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
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
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.