How to use Microsoft Excel and Word to send multiple emails

Did you ever consider sending out bulk emails in your job quest? I don’t mean spamming, but rather targeting a few, or maybe even a few dozen recruiters or companies you’d like to work for.

It’s pretty easy to do if you’re using Microsoft Office, but beware: most ISPs put a throttle on your outbound emails (to ensure you’re not a spammer), and that could trigger with as little as 200 emails.

I use the process I’m going to describe to send invitations and thank-you notes to people who attend my seminars and workshops. Once you have the names in an Excel file, it’s really pretty easy.

Note: this description is based on Office 2007. There are differences in earlier versions of Office, but you can do essentially the same thing I describe here.

Another note: you cannot attach files to mail merged documents. Wish you could.

First step, create your data file:

I like to put all my contacts in Excel or Access, although you can do this mail merge from you Outlook contacts as well. The latest version of Excel (2007) has a nifty “dedupe” function that lets you weed out duplicate entries in your contact list. I’ve been waiting for that since the late 80s when I did my first direct mail.

I like to separate the first and last names so it’s easy to create something like “Dear ‘firstname.'” If your name data is all in one column, though, you can either forgo this or it’s possible to use Excel text functions to separate the first and last names.

So my Excel spreadsheet looks something like this:

You can have as many columns as you want, and you can give them any names you choose. My example is very simple, and I use Column D to keep track of people to whom I’ve sent my presentation.

Second step: create the Word merge file

Next step is to put on your writer’s hat and create your document. Start with or create any document just as you always do. It’s easy to insert merge fields from your Excel data file anywhere in the mail merge email, but don’t go overboard now that you know this! Nothing turns people off as much as an email that says “Dear Walter Feigenson, Walter, we’re happy that you’ve decided that the Feigenson Family is a national treasure for all those people who aren’t Feigensons, Walter.”

So limit yourself to Dear <recipient name>. And don’t use the recipient’s name in the subject line, because lots of spam filters look for that.

Third step: tell Word where to find the data

Here’s how you do it: Click on the Mailings tab in Word. (Click on the image to enlarge, and then use your back button or go back to the original tab to come back here.)

The first thing you have to do is to connect the Word document to its Excel data. Click on the “Select Recipients” button, and choose the “Use Existing List” option. This will open the Windows explorer you’ve grown to hate, in a location on your hard disk that only Microsoft could love. Navigate to the location of your Excel file, and click on the file you’ve created with your mail list. You’ll see this lovely piece of Microsoft interface design:

While this dialog box is pretty ugly, its power is terrific. In case you didn’t know, you can have more than one worksheet in your Excel file. You might use that, for example, if you were separating mail lists by month, so you’d have one worksheet for each month. If you give the tabs names, they’ll show in this dialog (in place of Sheet 1$). The bottom of the dialog should be checked if you use field names in row 1 of your spreadsheet (as I did – row 1 has fields: first, last, email, and sent preso).

After you’ve selected the Sheet and checked or unchecked the check box, press OK, and you’ll be back in your literary masterpiece.

Fourth step: insert merge fields

(Believe me, this sounds more impressive than it is.) If you want to customize your note, you can put the person’s name in the email, so it becomes Dear John rather than Dear Recipient. It’s pretty easy:

• Place your cursor where you want the name to be, after the word Dear
• Click on Insert Merge Field (see screen capture above)
• Select the field from the following dialog box, which will appear in the Word document as shown to the right of the screen capture

If you press the Preview Results button in Word, it will replace <First> with the actual name of the first record.

Almost there…

If you’ve already selected the records you want to send emails to, you can now press the Finish & Merge button on the right side of the Word ribbon, where you will get the option to print or email the documents. If you’re emailing, you’ll get the next dialog box, where you have to set some basic parameters for your email:

• Make sure to select the field that has the email address in the To: field. If you followed my example in Excel, this will be Email.
• Enter a subject line. Think carefully about this, because the subject line is the most important thing in your entire email. Direct marketers spend their whole lifes perfecting this line, because it can have an enormous impact on the open rate of the email. You should avoid any spam-sounding words, like Free (and probably Download…).
• Select the mail format – for most of you this will be HTML, but you can also send in plain text.
• Pick the record numbers you want to send – usually this will be All. Word actually has some nice selection options, but it’s easier for most people to simply create the Excel file they want to send.

When you press OK, your emails will start to go out from Outlook. It goes pretty fast! Sent emails will show up in your sent mail folder, as always, so you can double check that they all went out.

That’s it! Except…

You can do the exact same thing with CSV files. What are these? “Comma Separated Values” files are a common file format that is used by sites like LinkedIn – if you export your contacts from LinkedIn (oh goody, another blog article…), they will be in CSV format. Excel reads these without conversion, so everything you see in this post is exactly the same for these files. You can also export Outlook contacts to a CSV file. Many other programs support this file format. BTW, if you look at the file (you can open CSV files in Notepad or Word), you’ll see these are plain text files, that aren’t really comma delimited, but comma and quote delimited. Just thought I’d throw that at you in case you haven’t fallen asleep reading this post.