This is a How to that will show how to send Personalized PDFs using Mail Merge

 

Prerequisites

• Developer Tab must be enabled
• Macros must be enabled for your current Outlook Session
  • These can be done by following this article:
  • https://help.utahtech.edu/kb/article/183-outlook-macros/
• A History of using Word's Mail Merge function

 

 

Click on the "Developer" Tab and click on "Visual Basic"

 

 

The Visual Basic Window will open up

On the left pane, you will want to click on the next to "Project1" and "Microsoft Outlook Objects"

 

 

Now double click on to open the editor window

 

 

Inside of this text editor window you will copy and paste this code:

 

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

 

If InStr(LCase(Item.Subject), "[merge]") Then

 

With Item
      .Subject = Replace(.Subject, "[merge]", "")
      ' to add an attachment, enter the path & name here
      .Attachments.Add ("C:\Path\To\File\" & .To & ".pdf")
End With
  End If
End Sub
 

 

In short, what this will do is if the subject of your outbound email contains the keyword "[merge]" it will send the email with the specified file at the given file path

 

The keyword "[merge]" can be changed to anything you would like; I would recommend keeping it the same but that is completely up to you.

 

To explain the & .To & part of this code:

This will grab Full Name of the person who you are sending the email to and will replace the & .To & with the Full Name.

 

So in the case of a Mail Merge, you would need to name your files the exact name of the people who you are sending to, so that the macro can grab the correct file and send it to the correct person.

 

You can also edit the File Names to be more explanatory for the receiver by adding the same phrase after the person's name

An example would be:

 

"C:\Path\To\File\" & .To & " Promotion.pdf"

 

Where the File Name would be "FULL NAME Promotion"

 

NOTE: There is a space before the word "Promotion" in the code, that is important as it will grab the correct File Name above

             If the space is left out, it would look for a File Name of "FULL NAMEPromotion" which looks wrong

 

This can also work with other file types; you would just need to change the ".pdf" to the different file type like ".docx" or ".xlsx"

 

 

 

An example Mail Merge would be:

 

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

 

If InStr(LCase(Item.Subject), "[merge]") Then

 

With Item
      .Subject = Replace(.Subject, "[merge]", "")
      ' to add an attachment, enter the path & name here
      .Attachments.Add ("C:\Users\d12345678\Documents\Macro PDFs\" & .To & ".pdf")
End With
  End If
End Sub

 

In this case, it will be looking for a file under my Digital-ID's Documents folder that has a new folder named Macro PDFs

In this Folder, I have PDFs with names of all of the people I am sending personalized document to.

 

 

Start a Mail Merge using an Excel Sheet with list of emails for the people who need the individual PDFs

Once you have finished composing your message, Click on "Finish and Merge" and "Send Email Messages..."

 

 

Set up your merge so that the "To:" option is going to the correct column

Ensure your "Subject line:" box contains "[merge]" in the message

Click "OK" if you are ready to send

 

 

The email will look like this if it was successfully sent

 

The file with the person's name will be attached, and the "[merge]" keyword will be removed from the Subject Line.

 

This can also be tested before using Mail Merge by sending individual emails with the "[merge]" keyword in the Subject line