VBA Send Email to Multiple Recipients

The VBA send email to multiple recipients macro will take a list of email addresses and send the 1 email to each address.  This automated solution can allow you to quickly email multiple recipients the same message without having to manually create each email individually over and over again.  This VBA Excel Outlook macro will create all the emails automatically based on the excel email template.

To send emails automatically uncomment the .send line.

 .Display 

'.Send

.display will show you the email before sending it

.send will automatically send it

VBA Send Email to Multiple Recipients (47 downloads)


 

VBA Send email to multiple recipients

Sub bulk_email_mailer()
'
' bulk_email_mailer Macro
'Outlook

emlSubject = Range("E4").Value
emlBody = Range("E8").Value
emlAttach = Range("E25").Value

Dim i, ii As Long
For i = 1 To 16
  emailTo = Cells(i + 5, 2).Value
  toLine = emailTo
  If emailTo = "" Or IsNull(emailTo) Then
  Else
     For ii = 1 To 4
       emailTo = Cells(ii + 23, 2).Value
       If emailTo = "" Or IsNull(emailTo) Then
       Else
           ccAll = emailTo & ";" & ccAll
       End If
     Next ii
 Set OutApp = CreateObject("Outlook.Application")
 OutApp.Session.Logon
 Set OutMail = OutApp.CreateItem(0)
 
 With OutMail
 '.SentOnBehalfOfName = "Execution Services"
 .To = toLine
 .cc = ccAll
 .Subject = emlSubject
 .body = emlBody
 .Attachments.Add emlAttach
 '.Attachments.Add "X:\IMD\ES\Notes.pdf"
 .Display
 '.Send
 End With
 End If
Next i


End Sub

Interested in learning new skills?