How To Use Mail Merge In Microsoft Word 2007

By: Chris Murphy

Email deployments give me a headache. I hate doing them, and I hate watching other people doing them. But they are a necessary part of web-marketing and communications.

In a recent project that required a mass email deployment, I encountered one of the most overlooked issues when sending bulk emails within a large company from an external source: corporate black-list policy. Quite simply, this is an IT policy that protects a company’s email servers from being overwhelmed and exploited by malicious sources. It’s a good policy, and if properly implemented, it can quickly stonewall persistent spammers.

Given that I am not the only one to encounter this issue, I thought I would share my recent experience and a work-around.

DISCLAIMER: Please note that I do not in any way advocate this as a permanent solution for any mass e-mail deployments on a budget. At best this article should be taken as a cautionary tale.

The Task

I developed an micro site for a client for an HR and team-building campaign. The micro site required internal users to claim their pre-assigned accounts through a simple “claim” URI provided in a email that was to be sent out after an internal event. One click; confirm the email address; accept a new user-defined password, and that was it. Simple and quick.

The Issue

The account claim workflow was simple, and my team and I had tested the system numerous times. We tested the standard exploits; we tested cross-location claims; we tested unorthodox methods of faking and breaking into the site. What we hadn’t tested as thoroughly was the e-mail deployment.

I made the assumption that we wouldn’t have issues with the email deployment since my team had done previous deployments for this particular client in the past. Easy peasy, right?

Not really.

The email was scheduled to deploy close to the end of the day yesterday. It went out. It came back. Rejected. As it turns out, I had also forgotten that the previous deployments were for external email lists — this was the first time we were handling a mass deployment to an internal employee list.

The Drama

By 9:30 this morning, I had a couple urgent notes from the client and spent the following hours following up with their IT department and our email service provider, along with our bulk e-mail distributor. It was then I found out that the email servers and the domain needed to be “white-listed” before any mass emails could filter through.

(You know those moments when you take a second and realized what a rookie mistake you just made — that was me this afternoon — email deployment cardinal sin numero uno).

By 4:00PM I was pretty much ready to hang myself after waiting for hours to hear back from the “deliverabily” department and the support desk to whom I submitted this rather urgent ticket. Not a fun day.

Desperate, I considered last resorts:

1. Have the client distribute the email themselves.

2. Set up a new account with a new provider specifically for this project.

3. Delay the deployment.

For various and obvious reasons, neither of the options were appealing to the client. The email deployment was 24 hours behind schedule at this point, and employees were chomping at the bit to claim their promised accounts on this micro site. It was time to fall back to some unorthodox means.

Enter Microsoft Word and Mail Merge (I feel dirty just writing this already).

The Solution

Mail Merge is one of those Microsoft Office features (there may be similar features in other products — I wouldn’t know), that is either loved or hated. I personally dislike it for the fact that it just wasn’t easy to use, and the lack of an intuitive workflow; however, it did come through for me today, so I’m going to cut it some slack.

Before we get started, this is what you should know:

1. If your target company has an IT policy in place to deal with bulk emails and spammers, you need to contact them first in order to get your e-mail server “white-listed”. No solution, even the one I’m outlining here, will get through a well-implemented IT policy. Read that last statement again and remember it.
2. You will need to provide the IT administrators at the target company with at least two things: the static IP address (possibly range) of your email server, and your the domain you are sending from. e.g. sender@somedomain.com — you will likely be asked to verify this through some simple test emails.
3. You will want to setup a new mailbox or profile through your Exchange server (contact your system admin for help on this). This will prevent you — your email account — from receiving a potentially overwhelming volume of bounce-backs or auto responses, etc.
4. If you have MS Outlook installed, start it up as you will need it to connect with your email server to send emails.
5. Lastly, you are definitely going to need some patience.

If you’ve done your due diligence and saw to it that the IT department of your target company has “white-listed” both your email server and domain, you can safely send a volume of emails through.

As with any bulk email deployments, you will also need a target list. Your list can come in one of several formats, the most common of which is a *.CSV formatted list.

I had several data fields in my list such as “first name”, “last name”, “email”, and “claimid”, so take some time before firing up MS Word to clean up your list.

NOTE: for the purpose of this article, I am using MS Office 2007

Step 1: Prepare your email list

Assuming that your list was previously validated, here are a couple house-keeping tasks that you might want to deal with first:

1. Ensure that your target list has the appropriate field/column headers.
2. Remove unnecessary fields.
3. Remove duplicates (mail merge helps you with this, but take the time to parse your list previous to using it).

I’m sure there are a number of best-practices you should keep in mind when dealing with email lists, but that’s not what this article is about (and it’s clear that I am not an authority or I wouldn’t be writing this article).

You can create a *.CSV using any text editor, but the most popular method for businesses is to take an existing Excel spreadsheet and save it as a *.CSV formatted document. Doing this will essentially strip out any proprietary document format information, but preserve the data, its structure, and order.

In this example I have two records (recipients) to whom I will send the email message to. I have four main fields which are denoted as follows (you can name and format them as you wish, this is just an example): [firstname], [lastname], [email], [claimid].

You will need all of the fields despite the fact that you may only use one or two of them for personalizing the email message.

The last step here is to save your *.XLS (*.XLSX if you’re using Office 2007) as a CSV, but before you do that, you should be aware that the application will display a couple of warnings regarding saving the file in a different format. To avoid multiple warning, simple delete out an unused worksheets from your document.

To save your file as a *.CSV you must do the following:

1. Select File > Save As.
2. When you see the “Save As” dialog, click on the “Save As Type” drop down and select “CSV (MS-DOS) *.csv” from the list. If you’re on a different platform (mac), select the appropriate encoding type (“CSV (Macintosh)”).
3. Click Save.

The first warning should pop up, which looks like this:

Click “Yes” to finish saving your document. If you did not delete any unused worksheets as I mentioned earlier, you may be prompted with a second warning; get past them and save out your CSV or use another application if the experience is becoming frustrating.

Step 2: Prepare Your Message

If you’re planning on sending out a mass email, you’ll need to have a message to send, and fields to populate. At this point you can fire up MS Word. You should be presented with a new document. Compose your message (or copy and paste it from whatever document you had originally composed it in), and create personalization fields. For example:

At this point it is safe to note that the Microsoft Office development team should seriously consider over-hauling this component for usability (their usability metrics be damned).

Step 3: Import Your Email List

This is the part that I had the most trouble with as I was both nervous about supplying the wrong fields, and generally hitting the wrong button that might cause MS Word to send the email “accidentally”. Zero points for usability Microsoft.

Jump back to MS Word and select the “Mailings” tab in the ribbon (if you’re using previous versions of MS Word, you’ll have to Google the location of that feature). Once you have selected the Mailing’s tab, you can click on the “Start Mail Merge” button to kick things off.

1. When you click the “Start Mail Merge” button, you will be presented with a short menu of choices.
2. Select the option “Email Messages” from the list.

Next, click on the “Select Recipients” button. You will be presented with three options in the drop-down list. Select “User Existing List…” from the options.

You will be presented with a “Select Data Source” dialog. Browse to the location where you saved your *.CSV and select the file and click “Open”. If you still have your CSV file open in Excel or another application, you may receive a prompt to open a “Read-only” copy or create a “Local” copy. Select read-only as that is about the only action we’re performing with the file.

If you’re curious to see the data you imported, click on “Edit Recipient List” in the main menu — you will get a dialog that lists the records in your *.CSV. There are a few useful aspects to this feature such as selecting and deselecting recipients, de-duping, sorting, and validation.

Moving on…

Step 4: Customize The Message Format

This next step is important (as are the following). You will need to personalize the email message along with some fields which are not readily apparent later on. The first thing you should do is customize the appearance of the recipient’s name. To do this, click on the “Address Block” button. You will receive a dialog that will assist you in choosing an appropriate format for the name. I’ve chosen the disable “Postal Code Insert” as those fields do not exist in my email list; however, the last option is worth considering — “Format address according to the destination country/region”. A sample of the output is displayed on the right-hand of the dialog.

Click “OK” when your settings are customized.

The next button beside “Address Block” (working from left to right), is “Greeting Line”. This feature allows you to create a greeting line such as: “Dear John Smith,” — going so far as to allow you to apply additional formatting. It can be useful for form messages, but for the purpose of this exercise, we’ll use the sample message and greeting like we created earlier.

The next thing you’re going to want to do (in fact, you can probably do this before some of the previous steps to personalizing the message), is match the fields in your CSV to the fields the typical Mail Merge expects. You can do this by clicking on the “Match Fields” button:

You will be presented with a small dialog which, if you’ve formatted your CSV properly will have automatically detected the matching fields, otherwise you will need to manually troll through the list and match the fields as needed. Since we have only a few fields to deal with, it shouldn’t be a big chore. If you recall, we have a fourth field “claimid”. We’ll get to that one in a moment.

Click “OK” to continue.

Step 5: Personalizing The Email Message

At this point the task is almost complete. You now have all the necessary mappings for your email list along with a working copy of your message. It is now time to insert the personalization fields.

To insert personalization fields perform the following steps:

1. Select [firstname] from the greeting line (first line).
2. Click on the “Insert Merge Field” button. You will be presented with a list of fields matching your email list.
3. Select “firstname” from the list.

What you should now see is the greeting line as it is customized with the fields. e.g.

Dear [firstname] [lastname], is now Dear «firstname» [lastname],

Repeat these steps for the last name and any other field you might want to insert into your message.

NOTE: since MS Word 2007 automatically converts text that looks like a URI into an html link, you will need to ensure that the link remains as plain text, especially if you are adding a personalization field. Allowing the application to automatically format your links does not mean it is correct. e.g http://www.somedomain.com/«claimid» may be parsed as:

<a href="http://www.somedomain.com/">http://www.somedomain.com/«claimid»</a>

Once you are finished with this step, you may want to use the “Auto Check for Errors” feature located here:

There are three options — the first of which is likely the most useful (and grief-free).

Step 6: Deploying The Email

This IS the last step, after which I highly recommend you take a breather and get yourself a beer, if only to calm the nervousness of relying on this method of deployment — believe me, it’s understandable.

To send your email message, click on the “Finish & Merge” button:

There are three options with this feature, but the one you’re going to want to use with this excercise is “Send E-mail Messages”. Selecting the last option will present you with a dialog (yet again); complete the dialog options before sending.

1. Select the field you’ve mapped for the recipient’s email address.
2. Supply a relevant subject line.
3. Select a message format (text or HTML).
4. Select how many recipients are to receive the message.

Click “OK” when you are done.

MS Word will now send your Mail Merged message(s) — individually — using MS Outlook. If you have not started the application, it will automatically prompt you to do so. To be safe, monitor your outbox and your sent messages to confirm that the email was deployed. Depending on the volume of email recipients in your list, you should have just as many individual emails sitting in your sent/outbox.

That’s it. You’re done. Go get a beer (I need one now after writing this).

Conclusion

As I warned in my earlier statements, this method is not ideal — and in all likelihood should be avoided at all costs unless you are in a serious bind.

I cannot guarantee the results of this method as it will vary from deployment to deployment. If you skipped past the first few steps and neglected to contact your target company’s IT administrators to conform to IT policy, you’re in for an inbox loaded with bounce-backs and delivery failures at best — at worst, you’re going to get a call from someone with a lot of very uncomfortable questions.

Again, there ARE repercussions if you avoid your due diligence; however, if you are attempting this method, you’re best off to apply it to low volume deployments. If you want anything larger than say 100 recipients, you’re better off using a service provider who can supply a smoother process, and help you navigate the pitfalls of email deployments.

You’ve been duly warned.

• Posted: August 5, 2008
• Comments: (23)
• Permalink
• Follow This Post: RSS 2.0