Skip to content


How to use Microsoft Excel and Word to send multiple emails

word-logo

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:

excel-logoI 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:

mailmerge-1

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.)

mailmerge-2

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:

mailmerge-3

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

mailmerge-4

mailmerge-5

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:

mailmerge-6

  • 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.

Posted in LinkedIn, Personal branding, Reviews.

Tagged with , , .


11 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Dave Kaiser says

    Hmmm, this isn’t working for me. I hit OK, and I see a list of the first names go by, but Outlook doesn’t show any activity. Do I need to enable a link between outlook and Word somehow? I can send a single doc from word via e-mail, but the merge function doesn’t work. Thoughts?

    BTW, thanks for this! assuming I can get it working, this wil be a huge help for my business!

    • Walter Feigenson says

      David, have you looked in your sent mail folder to see if they all went out? That’s the only confirmation you’ll have. Outlook and Word work together by default – there’s nothing you have to do to enable them. If you can’t get Outlook/Word to coexist, try exporting the names and email addresses to Excel, and then do the merge with Word, using Excel for the data. -walt

  2. Altaf says

    Need to attach letter in same mail is there any option with help of macro.

    • Walter Feigenson says

      Great question – actually I’ve been meaning to update the post about this. I don’t know of any way to do attachments in Word’s email module. I think it’s a pretty serious limitation – hope they fix it in Office 2010.

  3. Sarah says

    Is there a way to sent the email to a CC as well?

    • Walter Feigenson says

      I don’t believe you can use cc: in mail merge. Each email goes to a single person (it’s not like a long To: line where everyone sees everyone else’s email address).

  4. Wendy Petersen says

    Dear Walter,

    I have followed all of the above, but when I view the sent email, the page is not in Print Preview layout, but in web page layout with images and text all over the place. When it comes through on a Blackberry, it’s even worse. Unless people know to go and change the layout to print layout, they may just delete the email. Is there anyway to set the default so that it sends as PRINT layout only? I have tried sending as HTML as well as an attachment and the HTML isn’t even showing my images. Please can you help! Many thanks

    • Walter Feigenson says

      Hi Wendy. As far as I can tell, the emails you send using Excel, Word, and Outlook are pretty limited. For what you’re describing, I think you’d be better using a “real” email service. I’ve used a few, most notably Constant Contact and Vertical Response. If you’re doing infrequent emails, I suggest VR, since you can buy email credits when you need them. One caveat: all these email providers require that your list is strictly opt-in. Perhaps I should do a post on this topic? -walt

Continuing the Discussion

  1. JibberJobber Blog » Blog Archive » Merge Excel and Word To Send Newsletters linked to this post on March 27, 2009

    [...] Walt Feigenson was kind enough to create a whole blog post out of it.  You can read his post, How to use Microsoft Excel and Word to send multiple emails, at the [...]

  2. Good Blog – How to download your LinkedIn contacts to Excel « Dougneeper’s Blog linked to this post on September 26, 2009

    [...] written about using Word and Excel to do mass mailings here. Just be careful not to do too many in any session, because your email provider might tag you as a [...]

  3. How to Use Stats to Guide What You Write | Brand-Yourself.com Blog linked to this post on February 11, 2010

    [...] How to use Microsoft Excel and Word to send multiple emails [...]