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 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.
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)
.From = "email@example.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