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.