VBA Code to send email to multiple recipients

AndyBuck · Jun 8th 2018

I have written some code to email an excel spreadsheet to a single recipient. However I need to be able to email the attachment to multiple recipients listed in a different file to that which I am sending. This is the code that I have written

Code

Sub Send_Group_A_Multi()








Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Dim Strdate As String
Dim EmailTo As String


Set outlookApp = New Outlook.Application
Set outlookMail = outlookApp.CreateItem(olMailItem)
Strdate = Format(Now() + 1, "dd-mm-yy")
EmailTo = Join(Application.Transpose(Worksheets("Email").Range("A1:A4").Value), ";")


With outlookMail
    .To = EmailTo
    .Subject = "Group A Drivers " & Strdate & " "
    .BodyFormat = olFormatHTML
    .HTMLBody = "Hello,<p>Please find attached the Group A Drivers for <strong>" & Strdate & "</strong> "
    .Attachments.Add ActiveWorkbook.FullName
    .Send
End With




Set outlookMail = Nothing
Set outlookApp = Nothing


End Sub

Display More

The code falls over at the EmailTo = line, probably because I'm not referring to the file with the email list. I don't know the code to do this.

davesexcel · Jun 8th 2018

The code works for me, I originally had a Subscript out of range" because I forgot to name the sheet when I tested your code.

Like you say, the requires the correct location of the list. Assuming the workbook is opened,

Code

EmailTo = Join(Application.Transpose(Workbooks("TestRemoveLater.xlsm").Worksheets("Email").Range("A1:A4").Value), ";")

AndyBuck · Jun 11th 2018

Thanks Dave, that works for me too.

VBA Code to send email to multiple recipients

Participate now!

Share