Customized Mail Merge - Excel VBA

Excel Macro - Mail Merge

Ever wondered how to send multiple attachments to a set of 'To' list and 'CC' list with different Mail body text !!

Well here is the solution to send email attachments with customized To, CC  & Mail Body text

Open an Excel Sheet and enter the Attachment details in Col B [path and file name]; To list in Col C [if there are multiple email ids then separate them with Semi-Colon ';'] ; Mail Body contents in Col D

The line which says .send if activated, all mails would be sent automatically.

Function Email_Attachment()

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim WBMl As Workbook

Set WBMl = ActiveWorkbook
R = WBMl.Sheets(1).Range("B65536").End(xlUp).Row
S = WBMl.Sheets(1).Range("B1").End(xlDown).Row

For a = S To R
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
        .To = Cells(a, 3).Text
        .CC = Cells(a, 4).Text
        '.SentOnBehalfOfName = """xyz"" "
        .Subject = "Resources"
        .Body = Cells(a, 5).Text
        .Attachments.Add Cells(a, 2).Text

        ' un-comment the below option to send mails automatically
        '.send

        ' the below option is give so that the mails are draft mode.
        .display
    End With
    Set olMail = Nothing
    Set olApp = Nothing
Next a
End Function