Creating a mail merge from Microsoft Word with Microsoft Excel as database

Mail Merges using Microsoft Office (Excel, Word and Outlook), is a very useful tool in managing mass communication with personalised content.   It can be used in many divisions in an organisation but mostly the marketing and finance departments find this useful.  Marketing as an example commonly needs to send out invitations to an event, but you want to personalise the invitation with various personal fields such as the recipient’s first name and their company name…thus having ‘Dear Peter’ instead of ‘Dear customer’.  In the finance division, you might need to remind a customer about an outstanding balance on an account…follow these easy steps carefully and you will see how Microsoft Office can improve your customer communication.  These steps show you how to create an email merge.  You can also do merging for printed letters, envelopes and labels.

Step 1:

Create the database of recipients in Excel.  It is very important to note that the data in Excel needs to be in the EXACT format as you would like it to be displayed in the email:  Example: If you are sending to a person called Henry, and his name is in capital letters on your Excel database sheet (HENRY), when you merge the documents, it will be displayed as HENRY in your final email or letter.  When merging, you need to get the email to look as if you typed it yourself, so getting the data into the correct format is of paramount importance.

Make sure your headings are in the first row, each heading in its own column, and the individual recipients data each in its own row underneath each column heading.

Table on info to be used in Microsoft  mail merg

Step 2:

Once the Excel datasheet is complete, save and close the file (You will not be able to reference the Excel sheet from Microsoft Word if it is open elsewhere).  Pay attention to which tab in Excel contains your recipient data is displayed as you will be asked for this when you do the merge.

Before renaming the sheets

Before renaming the sheets

After the sheet has been renamed

After the sheet has been renamed





By default it should be Sheet 1, so for clarity and ease of use, delete any unused tabs and rename the Tab to display the name of your project example: Invitations.

Step 3:

Once the Excel sheet is closed, create the Master document in Microsoft Word.  Click on Mailings; Click on Start Mail Merge and select Email Messages.  The document is now ready in the format required for Mail merging.

step3 - Start mail merg, Microsoft Outlook

Step 4:

Once the document is finalised and the view that you see on the screen in Microsoft Word is what you would like your email to look like you need to select the database to where the mails will be sent to.  Click on Select Recipients

step4 - Select Recipients, Microsoft Outlook

When using Excel as a database, select ‘Use Existing List’ .  Browse to where your excel file is located on your computer and select it.  Once you select it, you will need to select the correct tab.  If you followed the procedures in Step 2, there should only be one tab to select.  It should look like this:

step4b - Select Table, Microsoft Outlook

Click OK.

Step 5:

You will notice the Insert Merge Field button is now active.  It now recognises all the column headings in your Excel Recipients database and these are now available for selection in the ‘Insert Merge Field’ dropdown list.  In the word document simply insert your selected fileds.  Example: Dear «FirstName»

step5 - Insert Merg Field, Microsoft Outlook

Step 5 - Example - Microsoft Outlook

Example of: Dear «FirstName»

Step 6:

Once all the merge fields are inserted at the correct positions in your document, you can preview your results.  Click on Preview Results, and tab through the mails using the blue arrow buttons.

step6 - Preview Results, Microsoft Outlook

Step 7:

If you are happy with the preview, select Finish and Merge and select ‘Send Email Messages’.

step7 - Finish and Merg, Microsoft Outlook

Step 8:

If it was not automatically selected, select the Column Heading that contains your email addresses in the To: field.

step8 - Merg to email, Microsoft Outlook

Step 9:

Add the subject line (this is the subject that appears in the recipient’s subject line on his email). Tip: Do not type your subject line in manually.  Copy and paste this from another Word Document or source in order to ensure that there are no spelling mistakes.  By default the ‘All’ button will be selected which means all data-fields will be contacted.  If you want to only send to a selection of the dataset you will have to enter from which to which record you want to select.

Step 10:

Hit the OK button, and let Word do its thing.  You can now go to your Outlook Sent items folder and you will see the personalised sent emails starting to appear.

Hints, Tips and Tricks

  • Create a sample database with your own name and email address.  Use this sample as your first  test run, just to see if everything is working ok
  • Once a Word document is ‘merged’ with an excel sheet, the word document will always reference back to that sheet when opened the next time.  If you want to change the source, you will need  to repeat Step 4 and reselect a different sheet.
  • Subject line: As mentioned, rather copy and paste you subject line, so that you can do a spell check on the subject.  Nothing creates such a bad impression as spelling errors in the subject line.
  • Keep it simple.  Do not over-elaborate the message and try to include too many merge fields, you may confuse yourself, resulting in mails going to Frank that was meant for Jane.