Mail Merge from Excel

Microsoft Excel spreadsheet is a common data source for a mail merge. In this article, we look closely at how to perform a mail merge from Excel and Outlook.

What is mail merge

The mail merge is a method of personalizing mass mailing messages. Helped by the mail merge, each sent email from a mass mailing becomes an individual message and contains personal information for different recipients. Mail merge tools can automatically fill such information and recipient details in the indicated merge fields.

How to mail merge

When you learn how to send bulk emails using mail merge, you choose from different tools to individualize the emails in the mailing. However, all these instruments follow the same algorithm:

1. Creating a database file containing the personal information for every recipient.
2. Writing a text template containing placeholders for the personal information to send separately.
3. Merging the text document with data from the specified source.
4. Automatically sending personalized emails according to a mail list and utility settings.

Those who are interested in how to do a mail merge in Outlook have two main options:

1. Use Microsoft Office Word Mail Merge Wizard.
2. Use a special instrument like Mail Merge with Attachments for Outlook utility.

Both methods use a spreadsheet file as the data source to fill in the placeholders in the mailing text.

What is the mail merge data-source file

Mail merge data can be contained in a spreadsheet file. The column headings of such a spreadsheet correspond to the merge fields in the mail template. The most common file format for this document is MS Excel.

The benefits of choosing MS Excel format for the database file for mail merging:

• All information is stored in one source file: addresses and variances to fill in the placeholders in the mail template.
• It is a common program installed on most modern devices.
• Because Excel is a ubiquitous spreadsheet format, many PC users know how to work in it, at least at a basic level.
• It is easy to download a spreadsheet in Excel format from Google Docs, and convert other spreadsheets to this format, for example, OpenOffice, LibreOffice, etc.

Creating database document

It's quite simple to create a database file using MS Excel. First, you need to consider what kind of mutable individual data is needed in your mailing. The most frequently used are:

• address field,
• personal greeting,
• recipient data,
• labels and signatures,
• individual details in the text like special prices, quotes, rates, dimensions etc.

Advanced version of Mail Merge with Attachments allows using, in the mail merging, the data for Subject, CC and BCC fields, and especially attachment names or paths to them (see how to mail merge with attachments for details).

All the data is arranged with the cells of the table in one row for each recipient. Name the column headers so they can be easily inserted by meaning into the merge fields of the Outlook draft message or a Word document.

Pay attention to:

• the allocation of the data in the cells of the table. For instance, to address recipients by the first name, both the last and first name need to be in separate columns;
• the presence of the table for merging in the first sheet of your Excel file;
• the format of columns that contain numeric data, like currency and measures. Format the columns containing percentages and postal codes as text.

To mail merge the data from the spreadsheet document via Mail Merge with Attachments, save the Excel file in a comma-separated value (.csv) format.

Personalizing addresses

Put your mailing list in the appropriate column. Here, for example, we named it Email.

Now, the mail merge tool you use can create individual emails containing the unique address for every recipient from the mailing list. Just indicate the spreadsheet file as the database when selecting recipients and the column header name for the To field.

1. Selecting recipients when mail merging in Word.
   Specifying recipients in Word is a bit complicated. First, you need to select the file with an address list in Step 3 of the Step-by-step Mail Merge Wizard.
   
   Then, return to it when finishing the merge to choose the corresponding column.
   
   
2. Selecting recipients when using Mail Merge with Attachments.
   
   As you see, one can choose the data-source file and the column for the To field on the same General tab. Also, the utility allows you to personalize the address field and Subject, CC, and BCC fields - something impossible in MS Word.

Personalizing the message text

To send the individual emails for each recipient of your mailing, you can put in the database table any personal information to fill in the placeholders in the text. In our case, this is a personal greeting in the Recipient column and the names of the different files to attach to the emails.

Add as many columns to your table as you want. Personalize any part of the email body that may give individual details.

MS Excel's unique capabilities allow you to combine values from cells directly in a working spreadsheet using formulas, for example:

= "Hello, " & A2 & " Thank you for " & E2 & " work!""

You can use formulas to combine any data, such as a separate first name, last name, and email address in the full address field, the name of attachments, and other fields.

How to merge the mail documents

Insert the column heading names from the created database spreadsheet in the placeholders in your mailing template.

1. Adding merge fields in Word.
   
2. Adding merge fields in Outlook with the advanced version of "Mail Merge with Attachments" add-in.
   

If your data-source table contains any numbers, ensure you put the appropriate symbol (currencies, measures, etc.) before or after a numeric placeholder in the email body.

Once all placeholders are ready, you can mail merge the Outlook bulk emails to send the mailing.

Before processing, check that the data-source file is closed and saved on your local drive.

Upon starting the mail merge process, all mailing messages will be filled in one by one with the data from the worksheet. Ensure your messages are filled out correctly before you send them. In Word, you can do this through preview mode. Our utility allows you to save your completed messages to a specified folder. Then you can check your mail, including the address, subject, and text.

1. Preview the messages in Word (text only).
   
2. Preview the ready-to-send emails created by Mail Merge with Attachments (check all the message fields).
   

Now that all the messages are successfully filled with the necessary information, they can be sent to their destination as separate emails with different facts.

If you used our utility and saved your messages to a separate Outlook folder before checking, you can send them from that folder using the Redirect Messages utility. Another option is to disable scheduled Send/Receive in Outlook and view messages in the Outbox folder before sending them manually.

---

About Author

David Godfrin is a software developer and blogger for Outlook and Exchange. He has a strong background in IT and works in the field of software development and tech support since 2011.