In today’s data-driven business world, communication is all about personalization. Whether you’re reaching out to clients, shareholders, or members of an organization, creating a personalized experience can make a significant impact. That’s where Microsoft Word’s powerful Mail Merge feature comes in. By linking a Word document with a data source such as an Excel sheet, Mail Merge allows you to automate the creation of personalized letters, emails, and even labels. This not only saves time but also ensures that each recipient receives information specifically tailored to them.
One of the key benefits of using Mail Merge to generate PDFs is the security and accessibility it provides. With PDFs, documents are saved in a read-only format, preserving the original layout, fonts, and details to ensure recipients view the content exactly as intended. When sent through email or uploaded to a secure web portal, PDFs allow clients, community members, or students to access essential information quickly and safely, without waiting for a physical letter to arrive. Many organizations use dedicated web portals or mobile apps, providing users with fast, secure, and convenient access to important documents in one place.
In this post, we’ll walk through a practical example of using Mail Merge to generate customized letters for shareholders, complete with personalized details such as names, addresses, and financial figures. By leveraging a list in Excel, we’ll demonstrate how to streamline this process, enabling you to handle high-volume correspondence with professional accuracy and ease. The most interesting part will be how do we generate PDF file for each individual letter. Thereby these PDF letters will be available
1- for attachment to emails or
2- to be downloaded from company’s web portal or mobile app.
Let’s dive in and transform the way you communicate with your audience.
First off we have our share holders’ data in an excel sheet. The sheet has the basic information of each share holder, required for mail merge. This worksheet is supposed to be maintained of its own, or updated from a cloud storage or through a script, depending upon the software infrastructure of the organization.
Now open MS Word and start a blank word document. Create a letter for the announcement of dividend for the registered share holder as shown in the image below.
Your letter content may be different but note that for now you can write any fake data instead of the real one. Later we’ll have MS Word replace this data with the respective column values form the Excel sheet. After having completed your letter follow these steps:
- Click Mailings tab
- Click Letters menu option from Start Mail Merge group
- Click Select Recipients from within Mailings tab and then click Use an Existing List…
Word will open up Select Data Source dialog to let you open the data source to merge. Here we already have our excel worksheet.
- Locate and select your worksheet shareholder_dividend_announcement.xlsx and click Open.
- From Select Table window that appears immediately after your data source selection, just leave the Name option selected, make sure that First row of data contains column headers checkbox is checked and then click Ok.
Now is the time to provide your letter with the real data from the selected Excel sheet.
- Move your cursor to where you want to place the share holder’s name then click Insert Merge Field.
- Click Shareholder_Name from the list.
- Repeat this step for every field or placeholder on the letter which you want to replace with the field or column name from the list.
- When you’re satisfied with your letter “template”, click Finish & Merge tab then Edit Individual Documents.
- Click All radio button option for Merge Records from within Merge to New Document dialog box. You’re done with generating actual letters.
Word may take a while to generate the letters, especially since our data source contains only 25 rows. If there were hundreds or thousands of recipients, expect the process to take longer. Once all letters are generated, review a few of them to ensure that the content is correctly merged and that the layout and styling remain intact, unaffected by the actual data. If you notice any issues, such as content shifting onto a second page or similar formatting disturbances across multiple letters, adjust the design of the original “template” document rather than the generated letters, and re-generate the letters.
Now for the exciting part—generating a PDF file for each letter. At this stage, you have two Word documents. One is the source or template letter, containing placeholders or fake data for the merged data. The second document holds the actual letters created using the Mail Merge feature, as described above.
Now while being in the second document follow these steps:
- Click Alt + F11 to launch VBA editor.
- Goto Insert -> Module
- In the right pane which is meant for the VBA code, place the following code.
Sub SaveLettersAsPDFs()
Dim i As Integer
Dim doc As Document
Dim path As String
Dim shareHolderName As String
Dim outputFile As String
Dim newDoc As Document
Dim numLetters As Integer
Dim letterRange As Range
Dim lineContent As String
Dim lineNumber As Integer
' Set the path where you want to save the PDF files
path = "F:\DATA\"
' Reference the active document (the mail-merged document)
Set doc = ActiveDocument
' Number of letters in the mail merge (number of recipients)
numLetters = 25 ''You may specify a different number depending upon your recipients.
lineNumber = 7 ''Here line number 7 contains the name of the recipient, you may have to identiy this line by running this code in debug mode.
''Iterate numLetters times to generate pdf files for each letter.
For i = 1 To numLetters
Set letterRange = doc.Range(Start:=doc.Sections(i).Range.Start, _
End:=doc.Sections(i).Range.End)
' Remove any trailing paragraph marks in the range
If letterRange.Characters.Last.Text = vbCr Or letterRange.Characters.Last.Text = vbLf Then
letterRange.MoveEnd wdCharacter, -1 ' Remove the last character (paragraph mark)
End If
shareHolderName = ""
If letterRange.Paragraphs.Count >= lineNumber Then
lineContent = letterRange.Paragraphs(lineNumber).Range.Text
If InStr(lineContent, "Shareholder Name:") > 0 Then
shareHolderName = Trim(Replace(lineContent, "Shareholder Name:", ""))
' Remove any trailing characters (like paragraph marks)
shareHolderName = Replace(shareHolderName, vbCr, "")
shareHolderName = Replace(shareHolderName, vbLf, "")
outputFile = path & "Letter_" & shareHolderName & ".pdf" ''outputPath could be like F:\DATA\Samantha White.pdf
Set newDoc = Documents.Add
letterRange.Copy ' Copy the entire letter range
newDoc.Content.Paste ' Paste the selected letter into the new document
' Remove any manual page breaks or section breaks
newDoc.Content.Find.Execute FindText:="^m", ReplaceWith:="", Replace:=wdReplaceAll ' Remove section breaks
newDoc.Content.Find.Execute FindText:="^b", ReplaceWith:="", Replace:=wdReplaceAll ' Remove manual page breaks
' Save the new document as a PDF
newDoc.ExportAsFixedFormat outputFile, wdExportFormatPDF
newDoc.Close False ' Close the temporary document without saving
End If
End If
Next i
End Sub
In order to save the document with macro through VBA code, remember to save the word document as .docm instead of .docx
Now You’re all set to generate letters individually in .pdf files. From within VBA editor Click Debug -> Step Into. You can also hit F8 key. Keep hitting F8 to execute the code line by line until you’re finished generating one letter. Check the letter and let the code execute till the end by hitting F5 key.
Using Windows Explorer open the target directory you’ve set for the generated pdf files. In our code we’ve set it “F:\DATA\”. Your generated letters will exist there as expected. Cheers!
