Send bulk emails with different attachments - Excel

Updated: Jun 16, 2020

I was assigned a task where I had to find a way to send bulk emails to customers. That part was easy. So, I was taking it easy.

BUT comes the catch..

There were some conditions...not all attachments should be sent to everyone. Based on the nature of a candidate, the attachments should be assigned dynamically.

Example: Person 1 should get only attachment 1, 2 & 3 and Person 2 should get attachments 1 & 2.

At first I thought it was a crazy requirement for Excel. But then I was like, wait a minute...we are talking about EXCEL! There has to be a way

So, with my thinking cap on, I was able to come up with a solution. Check out the video.


Sub sendemail()
On Error Resume Next
Dim o As Outlook.Application
Set o = New Outlook.Application
Dim omail As Outlook.MailItem
Dim i As Long
For i = 2 To Range("a100").End(xlUp).Row
Set omail = o.CreateItem(olMailItem)
With omail
    .From = "" (Change .From to .SentOnBehalfOfName if you want to send the email from a shared mailbox)
    .HTMLBody = "Dear " & Cells(i, 2).Value & "<br><br>We are looking forward to meeting you!<br><br> We've attached some documents for your review. Please print these and bring with you on your first day.<br><br><br>Kind Regards,<br><b>SDHN</b><br>Tech Team <br>P.O. Box 123 | Dubai | United Arab Emirates<br>"
    .To = Cells(i, 5).Value
    .Subject = "Membership Documents"
    .Attachments.Add Cells(i, 6).Value
    .Attachments.Add Cells(i, 7).Value
End With
End Sub

#VBA #Excel #Code #Macro #send #email #different #attachments #multiple

2,680 views2 comments

Recent Posts

See All