The purpose of Microsoft Word’s Mail Merge feature is to reproduce a document, envelope, email, or label which contains a combination of static (i.e., same every time) and dynamic content.
In the case of a form letter or mass email, the static content would be all the information that is the same for each recipient, such as announcements, agendas, news stories, etc. The dynamic content would be data that is unique to the reader, like their name, address, medical information, or purchase history.
By placing the static information in Word, then storing the dynamic content in an Excel table, we can strategically place connectors throughout the Word document that will extract from the Excel table, one row at a time, information to complete the Word document.
Each iteration through the Excel table will produce a separate and unique version of the Word document.
The results of the Mail Merge can be sent to a printer, generated as PDF files, or automatically sent out as email messages through your default email application.
Let’s look at a practical example.
Setting Up the Mail Merge Documents
We begin with a Word document that was written as if it were to only go to a single recipient.
Think of it as a template by which all other like documents are to be based.
Several pieces of information in the Word document need to be changed for each recipient.
The elements that need to change from recipient to recipient are stored in a table in an Excel file.
The table contains all the dynamic information used by the Word document, but it can also contain other information not accessed by Mail Merge. The Excel table can contain unneeded information; it won’t upset Mail Mere in the slightest.
The Excel table can also contain calculations. NOTE: The formatting of dates and calculation results do not carry forward to the Word document. I have a couple of options for you later that will address that issue and make the formatting of the data perfect.
Featured Course
Excel Essentials for the Real World
Starting the Mail Merge Process
In the template Word document, start the Mail Merge feature by clicking the Mailings tab/ribbon and locate the button group labeled “Start Mail Merge”.
Selecting the “Start Mail Merge” button displays a list of Mail Merge output options:
- Letters
- E-mail Messages
- Envelopes
- Labels
- Directory
You can even take advantage of the Mail Merge Wizard that will walk you through the most common steps when setting up and producing Mail Merge content.
For this example, we will work with our open template file by selecting “Normal Word Document”.
Selecting the Excel File with Dynamic Data
The next step is to point to the source of the dynamic data. By clicking the “Select Recipients” button we can pull information from the Contacts feature of Outlook, create a new list of dynamic data directly in Word, or what is the most common option, selecting content from an Excel table.
Browse out to the location of your Excel file and select the file. Click Open when ready to read the contents of the Excel file.
A dialog box will appear requesting two pieces of information about the data:
- Which sheet in the Excel file contains the table of information?
- Does the table of information have an existing header row?
If you have a column header in the table, those heading labels will appear as field name selections in an upcoming step. It is highly recommended that if you do not have an existing column header in the data, take a moment to create one. The alternative will be to work with generically named field names that are difficult to understand.
Once you have clicked OK, you can begin setting up the links between the Word document and the Excel table of data.
Inserting Excel Fields into the Word Document
Once we have selected the table of data, many of the options in the “Write & Insert Fields” button group have become available for use.
Next, we will select an element of the Word document that is currently static and replace it with a field from the Excel table. This provides a pointer to the column in the table that holds the information currently being represented in the Word document.
Erase the current static version of the data, then click Mailings (tab) -> Write & Insert Fields (group) -> Insert Merge Field.
This displays a list of all the column headings from the Excel table. For this instance, we will select the field entry for “Invoice Number”.
We now see a dynamic placeholder that will connect this part of the Word document to the single column in the Excel table.
We will repeat this process for the remaining fields we wish to be dynamic.
Preview the Output Results
We can “step” through the list of entries from the Excel table, swapping field data in the Word document to see a rendition of each iteration of the results.
This is accomplished by enabling the data preview (Mailings -> Preview Results).
To step through the various table entries from the Excel file, click the “Next Record” and “Previous Record” buttons.
Featured Course
Black Belt Excel Package
Number and Date Formatting Issues
One of the weaknesses of the Mail Merge feature is that it does not carry forward from the Excel table any of the date or number formatting.
We have two ways to address this issue:
- Add formatting codes to the Word field.
- Create a formatted version of the data in the Excel table.
Adding Field Codes in Word
Underneath each of the Mail Merge field links are a series of codes that tell Word what to display in that location.
To display the codes for a specific field, right-click the field and select “Toggle Field Codes”.
We can add a small bit of code to tell Word to apply a number style, like a Currency Style from Excel, to the linked data.
Just before the closing curly brace, add the following text:
\# $,0.00The updated merge field code appears as follows.
{ MERGEFIELD Amount_Line_1 \# $,0.00 }
To display the original data (hiding the field codes), right-click the field code and select “Toggle Field Codes”.
Repeat this process for each of the fields you wish to have a Currency Style applied.
You will likely need to toggle the “Preview Results” feature twice to see the results of your efforts.
NOTE: To display ALL the field codes in a single step, select the entire document (CTRL-A) and then press the ALT-F9 key combination.
NOTE: The \# tells Mail Merge that you are applying formatting codes to numbers. If you want to apply formatting codes to dates, you can use the \@ code as shown in the following example.
{ MERGEFIELD Date \@ “dddd, MMMM d, yyyy” }yields
Tuesday, April 20, 2021Creating Formatting Versions of Data in Excel
If diving into the Mail Merge formatting codes is a bit intimidating, an alternate approach is to create a “helper column” in the data source with formulas that point to the cells containing typed values or calculation results and applies formatting via a function called TEXT.
For example, if column A has a list of “Sales”, we can create a “helper column” in column B that takes the values in column A and applies formatting via the TEXT function.
=TEXT(A1, “$#,##0.00”)
If we use the “helper column” in the Word document instead of the original “Sales” column, the formatting will be carried forward.
You can use the same example from the previous section for dealing with the formatting of dates.
Sorting and Filtering the Source Data
If you have connected to a data source that is in an undesirable order or contains records you do not wish to use in the final merge operation, you can easily arrive at the perfect list by using the Sort and Filter controls in Mail Merge.
To access these features, click the Mailings (tab) -> Start mail Merge (group) -> Edit Recipient List button.
Sort Controls
The Sort controls allow you to select up to three levels of sorting based on the data source fields as well as the ascending/descending direction of each sort.
Filter Controls
The Filter controls allow you to reduce the data set by applying tests to various fields. If the data passes the various tests, the record is included in the final merge operation.
You can also simply brute force remove items from the merge operation by unchecking the record from the summary list.
Performing the Merge Operation
To blend the records of data in the Excel file with the Word document, we begin by selecting Mailings (tab) -> Finish (group) -> Finish & Merge.
We have three options:
- Edit the individual documents; useful to perform individualization to certain instances that are not achievable through standard Mail Merge means (ex: add a personal message to a special client.)
- Print each document. This allows you to save the processed Excel entries as a single, process file that can be printed at a later date to any printer needed or even saved as a PDF file. NOTE: Do not use the traditional PRINT system in Word. You will only receive a single document of the first record in the Excel data set.
- Send each instance of the document to an email recipient vis your default email application.
In our example, we will send each instance of the document to the recipient’s email address.
Selecting “Send Email Messages…” opens the following dialog box.
Notice how the Mail Merge feature has determined that a field Excel file contains email address information. This information will be used by the TO: field in the email distribution process.
We can also supply the following information and content customizations:
- Provide a subject line that will be applied to each outgoing email message.
- Define the format of the email message. This can be Text (for older systems), HTML (the most common selection), and Attachment. This attaches the Word document to the email message, requiring the viewer to open the document in a word processor application.
- Process and send all the Excel table entries, only the currently viewed instance, or a numeric range of instances.
Below is a segment of the processed and sent message to a single recipient.
If we were to have selected the “Attachment” option, the email message would appear as follows.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
