E-mail from Exel to Outlook with "TO" and "SU

[Y Bagg]

Hello all and thank you for your help.

I have a very simple spreadsheet with Columns A and B. Column A contains different company names and Column B contains e-mail adresses. All I need to do is send an e-mail to every address in Column B with value from Column A as a SUBJECT line.

I use Outlook (not Express)

I searched on this board and people have done some very similar tasks. Unforunatelly, I do not know enough about VBA to significantly modify someone's code to suit my task, so can someone please post a code that would work for me?

Thank you kindly!

 

[MichaelRo]

You need to set a reference to Microsoft Outlook in the Library references before you run this code:

Public objol As New Outlook.Application
Public objmail As MailItem
Public StrSubject As String
Public StrEmailAdd As String
Public i As Long

Sub Loop1()

'Loop through all Cells in column B
For i = 1 To Range("B65536").End(xlUp).Row
    StrSubject = Cells(i, 1)
    StrEmailAdd = Cells(i, 2)
    Call Mailer
Next
End Sub


Sub Mailer()
'Mails without security alert
'Need reference to Outlook in the Project References
 
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
    With objmail
        .to = StrEmailAdd       'email address
      '  .cc = "whoever"        'cc email address
        .Subject = StrSubject   'subject
        .Body = ""
        .NoAging = True
        .ReadReceiptRequested = False
        '.Attachments.Add AttachementPathname 'Attachments
        .Display
    End With
    Set objmail = Nothing
    Set objol = Nothing
    Application.Wait (Now + TimeValue("0:00:04"))
    Application.SendKeys "%s"
    Application.Wait (Now + TimeValue("0:00:04"))
    SendKeys "%{s}", True 'send the email without prompts
End Sub

 

[Y Bagg]

Thank you Michael for a reply!

Could you please elaborate on how I set up a lib. reference and also how do I "run" the code? Do I create a module in the spreadsheet and run it?

Thank you very much for your help !

 

[MichaelRo]

To create a library reference:

In the VB Window - Go to Tools / References

Scroll down till you get to Microsoft Outlook (10.0*) Object Library.

* Depending on the version of Office you have this number may be 8.0 or 9.0

Tick the box next to it - The code should now run.

The code needs to be placed into a standard module.

I hadn't defined the sheet name in the code - Either have the required sheet selected before you run the code or alter the following to the sheet name you require:

Sub Loop1()

Sheets("YourSheetNameHere").Select

'Loop through all Cells in column B
For i = 1 To Range("B65536").End(xlUp).Row
    StrSubject = Cells(i, 1)
    StrEmailAdd = Cells(i, 2)
    Call Mailer
Next
End Sub