Send bulk emails with different attachments - Excel

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

Macros:

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 = "sdhn1@gmail.com" (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>tech-sdhn.com"

    .To = Cells(i, 5).Value

    .Subject = "Membership Documents"

    .Attachments.Add Cells(i, 6).Value

    .Attachments.Add Cells(i, 7).Value

    .Send

End With

Next

End Sub

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