Create and utilize mail merge in Microsoft Word for free
What is mail merge?
Mail merge is a built in Microsoft Office feature that allows you to populate data from a list into a document that would otherwise be repetitive to create, copy and edit each document. With mail merge, you create the template document once, and the data from the list is populated in the document to create multiple, unique documents based off the template.
For this guide, I will use the example of a contractor I helped utilize the mail merge feature and redesigned their contract. The contractor wanted to send out their annual yardwork maintenance agreement to customers in a more efficient manner. This is a small business that isn’t large enough to justify the cost of paid software, but has enough customers that creating each contract one by one in Word is time consuming.
The contractor previously created the Word document, then clicked the file and copy/pasted or pressed Ctrl + D to duplicate. Then, opening the duplicate, editing, and saving. This was time consuming and took 3-4 hours for 30 contracts. Now, by entering the data as a list in Excel, performing the mail merge in Word, this can all be done in 10 minutes. Furthermore, the potential for human error or inconsistencies of individual documents is reduced.
Mail merge terminology
Let’s start with Excel, then Word. Excel is where we need to make our list of data on a sheet. The sheet consists of faint grey gridlines. Each box in the gridlines is called a cell. You can state a cell’s location like calling out a location in the board game, Battleship. G, 4! Anyone? You identify the cell by stating it’s row first (runs vertically across the left edge of the sheet), followed by the column (runs horizontally across the top edge of the sheet). Make your list so that each row contains the data of a specific customer, and columns represent attributes about that customer (name, address, email, price, product attribute, etc). I recommend breaking the address up into attributes like (addressline1, city, state, zip) so you can insert the fields in the Word document and see exactly where the data will appear. Inserting one field with nearly a sentence of address data is tough to predict how much space it will occupy and may spill over to the next line, shifting your entire document formatting down which will look inconsistent.
Once we move into Word, it is important to know that we can insert Merge Fields into the document, which will appear as placeholds in the document like <<fieldname>>. Each column in your Excel sheet can be an attribute you can choose to insert anywhere into the Word document.
Creating the Excel list
Now that we have an understanding of terminology, let’s begin. It is assumed you have created a Word document to serve as your template and created an Excel sheet with a list of data, such a customer information.
In Excel, click and drag from the first top left cell (1,1) and drag to the bottom right where the last column and row of your data is. Let go of the left mouse button and press Ctrl + T keys to create a Table. A pop up will appear, check the box “My table has headers” so Excel knows your row 1 is the attributes/column names. This will prevent your first mail merge page from inserting your column names.
If you add or remove rows, be sure to click and drag the bottom right corner of the table to size it where your data ends. If the table ends on a row above or below where your last row of data is, you will miss information or will have an error when Word makes the last page of the merge and the process will need to be corrected and restarted. My aim is writing an article so detailed you can successfully do this the first try!
These are table sizing examples the wrong way: one has the table including two blank rows and the other image shows the table cut off too soon and will exclude the bottom two rows of data.
An advantage of making a list in Excel is you can use the Sum function to quickly total up prices for an estimated profile. But, do not do this until after the mail merge. If you use the Sum function before making the mail merge it will put the total in a new last row. When running through the mail merge, Word won’t know what to do with a row that is empty except one cell and will error. If you add more rows and merge again, be sure to delete the Sum total first. I explain how to run the merge a second time if you add a few more rows (say you picked up a few more customers after you did your initial merge).
Inserting the mail merge fields in Word
First, we need to add the merge fields in the Word document to tell the program where the data from excel should insert. Go to Mailings, Insert Merge fields.
If it is greyed out because it is the first time doing a merge with this Word document, just like our example screenshot; we need to first point Word to the Excel sheet. Click Select Recipients > Use Existing List. The existing list will be the Excel sheet you saved.
Be sure to check the box “First row of data contains column headers” so that you will not insert the column names into the merged first page.
Click to put your cursor where you want the merge field to go. Click Insert Merge Fields, and you’ll see each available field is a column name. Click whichever field you want. It will insert into the document.
Performing the mail merge
Under the Mailings tab, right side, click Finish and Merge, then Edit Individual Documents. This will give you a chance to scroll through the completed merged pages to review and make any final tweaks.
Click the All button to create documents from all of the rows in the Excel sheet. After you create documents, say you gain a few more customers and you add more rows to your Excel sheet. You can specify a range of the last couple new rows, say you added a few more rows to Excel after doing the initial merge, say row 2 to row 13 (remember row 1 is the column header name row). If you added two more, you would select From row 14 To row 15.
Click Ok.
Splitting the merge into individual PDFs
Word will open to a new document containing a running document of all of the individual pages of the merge. Ensure the merge worked, correctly by carefully scrolling down to review. Press Ctrl + S and save it to a location, or do File, Save As. Give it a name that makes sense to help you stay organized. Something like purpose_currentyear_merged right away. Then, you can make your edits if needed, such as adding specific text to certain pages. Ctrl + S to save changes.
To separate the pages into their own documents to send to your customer, the easiest way is: press Ctrl + P or click File > Print. For Pages range, enter the first page and last page of your first document. If each page in the document is a customer contract, enter 1 and 1 in the start and end fields. If your document is 3 pages long, start with 1 and end with 3. Print, then do the print function again and enter start of 3 and end with 6. Select your printer destination as ‘Print to PDF’. You will need to print the same number of times you have customers, but this process is far more time saving than not doing the mail merge at all. A PDF is the best document format to send to customers instead of Word documents because it can be opened on any device, displays well without the risk of things being moved around, is hard to edit, and will print perfectly.
Save each PDF in the same folder, don’t just save them to desktop or downloads and make a mess you need to cleanup later. And that’s it, you have finished a mail merge.
Please reach out if you need assistance. We would love to gain a client who wants Smart, Secure, and Affordable IT for their small business.