Gmail is a great tool for managing your emails. You can add labels, stars or even create multiple inboxes to sort your mail. One thing that is useful, but not supported by default is the mail merge feature. For those who have not heard of mail merge, it means you can send personalized email (with a general template with custom name and info) to everyone in your email list. The good thing is, with a little help from Google Docs (now known as Google Drive) and Google Script, you can now easily create and send a mail merge in Gmail.
1. Create a template email in Gmail. You can either save it as a draft or as a canned response. In the template email, replace the terms that you want to personalize with “$%terms%”. For example, if you want to personalize the name of the email, replace the name with “$%Name%” or “$%first_name%“. Below is a screenshot of the template email that I use for notifying winners of our giveaway event.
2. Next, open Google Drive and create a new spreadsheet. In the first row of the spreadsheet, insert the terms that you use in the Gmail template. At the last cell, insert another field call “Email Address”.
3. Populate the spreadsheet with the detail of your sendee.
4. The next thing we are going to do is to add a Google script that will merge the content from the spreadsheet to Gmail and send the email out. On the Spreadsheet, go to “Tools -> Script Gallery”. In the pop-up window, search for “mail merge”. There are several mail merge script that you can use, but the one we are using for this tutorial is “Yet Another Mail Merge”.
Scroll down to the end of the list till you locate it. Click Install. Google will then prompt you to grant access to the script. Once that is done, it will bring you back to the Spreadsheet. You should now see a new “Mail Merge” option in the toolbar.
Click “Mail Merge -> Standard Merge”. A window will pop up to prompt you to select the template. Click on the template that you have created and saved earlier.
Click “Send Mails”.
That’s it.
This is the end result:
After the mail merge, you should see a new column “Merge Status” in your spreadsheet. This should inform you if the mail merge is successful for each recipient.
Save and Recycle
Now that you have completed your mail merge, you can save the spreadsheet so you can reuse it in the future. A point to note is that for future usage, you will need to remove the whole “Merge Status” column before you perform a new mail merge.
The mail merge will also work with Google Apps or any other email address that you have associated in Gmail.
As mentioned earlier, there are plenty of mail merge scripts available in the Script Gallery. Some come with more complicated features not found in others. You can try out each and every one to see which one is the best for you. If you are proficient in javascript, you can also create your own Mail Merge script in the Google Script Editor and customize it to suit your needs.
Receive the latest update in your inbox.
Great tips! Very useful to send newsletter to my readers. Thank you so much!
Really usefull article :) I can see how this might come in handy for some people who want to give details for something to a lot of people without writing the email again and again…
Thank you so much Damien :) for this Great Tutorial.
I installed Yet Another Mail Merge and am working out of Google Apps for work. When I start MAIL MERGE it does not give me a drop down to choose a template (?). I have an email template set up already to send out to contractors. Ideas?
I have tried on both personal Gmail account and Google apps and they both
worked. Perhaps you can create a new spreadsheet, re-add the mail merge
script and test it out again. The script is tied to individual document, so
creating a new document will start the mail merge process afresh.
Is there a limit to the number of field it will merge? The first two on my spreadsheet show up find in the email – the other few just won’t work! Please let me know what you think is happening! Thank you!
How many fields do you have? I have tried it with 4 fields and they all
work fine. I haven’t test it with more than 5 fields yet.
I have inserted pictures in the email but somehow when I start a new email with a new inserted picture, everything is ok except that the 2nd email still retains the 1st email’s inserted picture. I can’t seem to correct this. What should I do?
Can this script be used to send from another account I have imported to Gmail using POP3? Even when I have the draft email set up to send from the other account the test mail merges I’ve tried all come from my Gmail.
For this particular script, it will only work with the default Gmail address. I did not try other mail merge scripts, they might work.
Similary to Lindsay’s question… but a bit different:
Do you know any tips on how to change the “From” address in the mail merge? We use multiple aliases in our business and need the mail merge to work with one of our aliases instead of the main email account. I have tried creating the Draft email using the alias, changing the default email address in settings to that alias, etc. but I cannot find a way to have it come from anything other than my main email account.
Any ideas?
Thanks!
I sent out my mailing and realized that the customized fields appear in a different font than the rest of the body text.
My recipients got the body in arial but the cusomized fields in Cambria (Body). To prevent this from happening in the future, I’ll make sure to convert my entire message to Cambria in Word first and then copy/paste it into Gmail so the entire message is in one font.
Please let me know if there’s something I’m missing about the different font issue.
thanks
I usually compose my email in pure text format. In this way, I won’t have the font issue.
Hi Damien,
Thanks for the wonderful work. But I can’t seem to find the script for Just Another Mail Merge. Do you know what could be the problem? Joel
The name is “Yet Another Mail Merge”. Search in the Script gallery section. You should be able to find it. I have tried to search again and it is still there.
Hi Damien,
I have created a gmail draft with <> ect as merge fields that are also the column headers in a Google spreadsheet.
I have the email addresses in column 3. When I run the script I am asked to select the column for the recipients (I choose – email) this changes the column header to Email Address. I then choose the draft email with the merge fields. I get the following error… “Error encountered: TypeError: cannot read property “length” from null.”
Is there a way to correct this error as the emails are not merged nor sent.
I am assuming that I am doing something incorrectly. Novice driving again!
Cheers
Mark
Hi, I have the same problem with “Yet Another Mail Merge” script. Can anybody help with this issue: “Error encountered: TypeError: cannot read property “length” from null.”
This is a great script. Very helpful, except each “from” field for the recipients is followed by “via maestro.bounces.google.com”.
This does not look professional for users. How can I get rid of this? I can’t find it anywhere in he script.
I tried another mailmerge script earlier today which failed because inserting the message body into the spreadsheet overwrote everything. I tried uninstalling but could not see how to. I then installed your script (Yet another…) but I don’t see the same screen that you show in your screenshots, it is like I am on a different version of Gmail. There is no option to save a “canned response” and the message does not recognize a data source. Two failures in one day is going some, especially as these are the first two scripts that I have ever attempted
Hi I am using Mail merge, but since today morning all the mails I am sending are getting failed. Can you suggest why it is happening.
Aarti, since I received no response to my query I gave up and now use a tool outside of Gmail to control my mailmerges which is a much better solution and easier to use.
Perfect! Such an easy to follow tutorial! Thanks so much for uploading this
Hi, great script, except for one of the variable fields (I named it ‘Greeting’ in the spreadsheet) I get the following in the merge email:
Dear ;font-family:Arial,sans-serif;color:rgb(51,51,51)”>%,
Any idea what’s happening? Thanks.
Figured it out from one of the above posts. Once I stripped the draft email of formatting it worked perfectly. Thanks!
I just did this with 300 + emails out to our bands fans! Worked perfectly! NICE ONE!
how to insert table in mail merge?