How to use Google Mail Merge to send out swap emails

If you’ve hosted a crafty (or other!) swap online, there’s a good chance you’ve sent out about a jillion emails, each of which needs to be personalized with that person’s partner information. If you’ve participated in a swap online – you should just appreciate that this is a lot of work! In addition to setting up partners, checking on the status of packages, answering questions… Anyways, I’m here to tell you that there is a super fast short cut to writing, personalizing, and sending all those emails!

To start with, you’ll need a gmail e-mail address. If you don’t have one already, you can get one for free! Use it just for the swap, if you want. You can have it forward to your real email address if you want, also.

Using Google Mail Merge, if you have the information you need to send in an e-mail in a spreadsheet, you can have google personalize and send out all of your swap partner e-mails pretty instantly. Start by typing up a draft of the e-mail you want to send out to your partner. Instead of the personalized info, though, you’ll type a field name wherever you want personalized info. For example, where I want to have the name of the person I’m sending the e-mail to, I instead type $%Partner First Name%.

You’ll then want to save this as a new canned response by clicking on the little arrow in the bottom right of the above image:

Next, set up a spread sheet with all a column for each field in your e-mail. This will generally not require all the information collected by the swap (i.e. you don’t need to include whether or not their partner is willing to ship internationally), so I like to make a separate sheet with only the info I’ll be including in the e-mail. I’ll copy all the info to a separate sheet, then delete columns I don’t need in the email and rearrange columns as needed. Here’s an example of that sheet for the Doctor Who Swap I’m helping host right now (#makeadalekmakeafriend) (I redid the order since the swap is still ongoing – so these are not the actual partner assignments!):

Quick tip: once you have the partners in your sheet in order, just reference one line up or down in the email slot to get the e-mail address and name for the person you are sending the email to. So in this example, Kim is sending to Zoe, Zoe is sending to Kerrie, etc. I use the ‘order’ column as I’m setting up my partners, so I can sort on that column and see who is sending to who.

Now, create a mail merge spreadsheet by saving to your google drive. Insert as many columns as you need before the ‘mail merge status’ column, but make sure not to change, remove, or write over that column. Make sure that the title of each column *exactly* matches the field name used in your e-mail – i.e. since I typed $%Partner First Name% in my email, I need the title of that column to be ‘Partner First Name’, and so forth.

At this point, I would suggest typing in your e-mail address as the e-mail to send to and some test info in each column to make sure everything is working right. Once you send out the actual e-mails, they will all go out at once, and if you have a mistake in your setup, everyone will get it before you have a chance to catch it. First, in the google drive sheet, make sure the ‘mail merge status’ column entries are blank. If they are not, delete the contents in those cells. Then go to Mail Merge > Step 1: Initialize.

After this runs, go to Mail Merge > Step 2: Start Mail Merge. It will open up the following dialogue:

Here, you must select the name of the canned response you saved earlier and type in your name. You also get to choose whether or not you want to be BCC’d on the e-mails – if you check yes, it will send you a copy of each e-mail it sends out. Then click ‘Start Mail Merge’ and it will send out the e-mails. If everything works, it will send out all the e-mails and each entry will be updated to say ‘EMAIL_SENT’ in the Mail Merge Status column. If there is an error in e-mail address, you will be able to see that that e-mail and any following ones were not sent. Once you fix the error, start the mail merge again and it should not send additional e-mails to the entries that are already set as ‘EMAIL_SENT’.

Then go check the e-mails and make sure they ended up how you expected. Once you know that it works, copy all your participants’ information, and you’re ready to start the mail merge by following the same steps as in my test above. Just make sure that you have everything set up right, in my sheet, ‘Partner First Name’ is making something for the person who’s name is ‘First Name’ ‘Last Name’ in the same line. The email address should be for ‘Partner First Name’, and the rest of the info in that line should be for the person who’s name is ‘First Name’ ‘Last Name’. Again, this is where sending out a couple of test e-mails is useful! Remember that you also need to clear the ‘EMAIL_SENT’ cells if you tested it on fake emails and want to resend new ones.

Quick Summary:

• Edit  with the e-mail address and first name of person you want to send the e-mail to as ‘Email address’ and ‘Partner First Name’, and the info for their partner in the rest of the columns
• Make sure the column titles in the mail merge spreadsheet exactly match the fields in your canned response e-mail
• Make sure all cells in ‘mail merge status’ column are blank
• In the mail merge spread sheet, go to mail merge>2. Start Mail Merge. Select your canned response email, type in your name, and click ‘Start Mail Merge’.
• Check the  ‘mail merge status’ column to make sure all your e-mails have sent!

Attribution: I found all this info from this site: digital inspiration. The spreadsheet script was developed by digital inspiration, and I just changed some things around and included some swap-specific info. There’s a video tutorial there as well if you want to see how it works in real time.Hope it helps!