Mail Merge and the Office, Sweet!

This post is part of an on-going series in April, 2018 about Mail Merge for changemakers!  Read the origin story here!

Dear <<First Name>>,

I promised you weekly posts on Mail Merge so I’m going to deliver!  I’m typing this quickly before I dash out the door to learn and present at the Organizing 2.0 conference in NYC.  (Signal boost – I’m presenting on Friday AND Saturday spreadsheets for changemakers – and they even gave me a COMPUTER LAB!)  Will I see you there?

Last night was the Moment of Truth where I had to stare down a Mail Merge and execute, execute, execute.  I have to admit, MM can be kind of intimidating (if you feel that way, YOU’RE NOT ALONE!!!) and I had to do some midnight texting with my friend and EQAT Communications Director extra-ordinaire, Greg, when I thought that the data wasn’t alright.  But it all worked out and within an hour, I had 700+ personalized fundraising letters, coming soon to a mailbox near you (after they are lovingly stuffed into envelopes by amazing volunteers on Sunday).  I was thrilled to support the Earth Quaker Action team with this behind-the-scenes work, and I’ll also be double thrilled to support them with a donation as the Power Local Green Jobs campaign continues to gain steam.  Read on, below, for a tutorial and step-by-step instructions about how to Mail Merge a fundraising letter using Microsoft Word and Microsoft Excel.  Next week, we’ll cover merges using Google Drive!

Step One: Clean yer data

I honestly think this is the trickiest part of an MM project, because it’s hard to be 100% sure that your names and addresses are formatted properly when you pull them from your source (usually a database, but not always!).  In this case, we were mailing letters to households so the names came out as “First Last’ or “First Last and First Last” or “First and First Last” or even “Name of House of Worship.”  Yep – you guessed it – they were all mixed up!  This made it tricky to merge letters that say “Dear First Name!”

Here’s what I did:

• Used a Table and Filters to find JUST the people coded as “one member” households (this is info that came from our database, I didn’t do any tricks yet!). I figured those people would be easier to merge and it would be easy to find their First Names since they were less likely to have a spouse or partner coded into the Name column.
• Then I used the “text to columns” or “delimiter” feature in Excel to take their names and switch each word into a separate column so “Samantha Shain” got separated into Samantha | Shain and “Samantha and Bubbelah Shain” turned into Samantha | and | Bubbelah | Shain (in 4 separate cells in 4 separate columns).  When I did this, the overwhelming majority of people had first names that I could just grab.  A small minority of people (like the Mary Lees of this world) had a middle name that should be included in their First Name, so I manually adjusted those.
• I did a similar thing in the “more than 1 person household” tab, and manually edited the data to pull out First Names – Excel is smart enough to recognize a pattern and auto-filled in my data without last names, getting it like 95% right!  So for this section, “First Name” was actually “Samantha and Bubbelah” (Bubs is my adorable pet lizard), not just Samantha.  We wouldn’t want Bubs to feel left out!
• Finally, I re-formatted the zip code column so that we wouldn’t lose “leading zeros.”

No database is perfect, so I think you’ll always have to do SOME cleaning in Excel.  Plus, it’s good to thoroughly check your data BEFORE the merge so that you can identify outliers, people who shouldn’t get a letter, zip codes that are inaccurate, spelling mistakes, and other things like that.

Step 2: Map your “Fields”

“Fields” in this case are a fancy name for columns, not the agricultural beds of my youth!(from farmer to database activist, who knew I would end up here??).  Greg was kind enough to send me a letter that was formatted with Address and Dear First Name already, but I needed to tell Microsoft Word “where the heck is First Name supposed to come from????”  To do that, I needed to insert “Merge Fields.”

First, I went into Microsoft Word and went to the Mailings tab (seems logical, since it’s a Mail Merge).  Then, I went to “Select Recipients” and found my Excel document that had my contact list.  I selected that document and Excel knew that it was going to make one letter for each row in the spreadsheet.  Smart, right? But I still needed to tell Excel which column was first name and which column was Address or Zip.

To do that, I used the “Insert Merge Field” button in the tool bar and put in one Column at a time.  This button will let me access all of the columns in my Contact Spreadsheet and select which one should be merged – and where.  The merge field will show up in your Word document as grey and has << >> on either side.  When I thought I was satisfied, I did a preview — and then I realized, I wasn’t satisfied at all!  I had to change some things around, including spaces, commas, and switching Full Name and First Name!

Step 3: The Merge (not to be confused with The Purge)

I kept practicing until I was happy with the Preview result, and then I hit the fated Merge button.  Never fear, even after you merge, you can always re-do if necessary.  Doesn’t cost ya anything except your own time and frustration tolerance!

The result is a document 1500 pages long (there were around 750 recipients and it’s a two-page letter going out) and each one is personalized with the recipient’s name and address.  You could take it one step further and merge Envelopes and Mailing labels, too!  The same process would also apply for Printing Nametags if that’s something you ever need to do.

Wizardry

I love how fairy tale culture has infiltrated tech culture from “Wizards” (interfaces that walk you through the steps in a process) to “Unicorns” (uncommon experts).  Excel offers a Mail Merge wizard, which I really like.  I did this merge manually because to be perfectly honest, it’s been a few years since I did a merge and I wanted to make sure I was going through everything very thoroughly.  You can try the wizard on your own and write a comment about how it went!