Customize Mass Emails with Gmail - Mail Merge Tutorial - Steffon Davis [PDF]

Steffon Davis Thoughts from a product manager in SF Blog About Curation »

Blog

Customize Mass Emails with Gmail – Mail Merge Tutorial By steffon August 5, 2013 244 comments Software Tricks

I day dream about a free, full-fledged Google CRM product native to Gmail. I’m closer now with the ability to send customized mass emails from Gmail. It’s not the “perfect mass email solution”, but it: Is Free Uses Gmail/Google Docs only Is Google Contacts friendly This tutorial is based off an excellent post by Amit Agarwal and utilizes a script written by Romain Vialard. Step 1: Create a draft of your message Open Gmail and compose a draft of your message.

Step 2: Replace the recipient’s name with a variable If you are sending the email to Tom, replace his name with the variable $%First Name%. The variable enables the script to pull rel="nofollow">Steffon Davis

into a cell and change the link and the “word” (which appears between the tags; in this case “Steffon Davis”) as needed (Note: you don’t need the rel=”nofollow” text but I can’t stop my CMS for automatically recognizing the HTML and including it right now). So, if you have three case studies, you could create one column for each case study and put in appropriate custom link for each email recipient in it’s own case-study column. Hope it goes well, and if I didn’t quite answer your question, just follow-up with more details. Reply Justin says: February 20, 2014 at 9:06 pm You’re the man! Thanks so much for the quick response, Steffon! Reply 16.

Mark says: March 5, 2014 at 10:52 am Hi Steffon, thanks for the very useful script. I have been using Streak, but it does not allow attachments, and I have to send one with the mailing I am doing, so this is great. I was hoping to be able to add a column called “company” then add $%company% to the email, but this does not work – I know very little about scripting, so I am sure it is not as easy as just doing that. Anyhow, if there was a quick way to add that functionality it would be great. Thank you Mark. Reply steffon says: March 6, 2014 at 1:46 am Hello Mark! Thank you for the question. You are correct that you can add a column called “company” and then add $%company% to the email and the script will fill in each company value automatically. It’s as easy as you described it. If you have problems, just reply to this thread with the details. Reply

17.

Mark says: March 6, 2014 at 5:08 am Hi Steffon, and thanks for the response about adding $%company%… I have another problem, I have JUST upgraded to the $5 per month Google Apps, and for some reason I am still getting “Service invoked too many times for one day: email.” messages and cannot send more than 100. I spoke to google support and the guy I spoke to seemed to THINK the script should work, he wasn’t sure and is getting back to me. Do you know for sure it works for 1500/day for paid customers? Thanks again mate, Mark. Reply steffon says: March 7, 2014 at 12:27 am Hi Mark – Having not personally upgraded to the $5/month version yet I can’t personally be sure, but I would try this first: Re-add and reauthorize the script within your new Google Apps account. In step 5 of my tutorial you’ll see the step to authorize Google apps to use the script. My guess is that you need to authorize the script again for your upgraded account. It’s possible that the script is still authorized for the “old” account and as such has the old limits in place. Definitely let me know how it goes, I’d be interested to hear what ends up working for you! Reply Mark says: March 7, 2014 at 3:44 am Hey there Steffon, I will report back… I did copy the spreadsheet from the one google account to the other, so would that have meant I was still using the permissions etc of the first gmail account? How do I remove the script completely so I can ask for permission afresh? tx a lot… this is very useful! Mark. Reply steffon says: March 7, 2014 at 12:38 pm Hey Mark – Try this: Log-out of all of your gmail accounts and only sign-in to the new $5/month, business account; we want to make sure that this is the only active account. Next, delete any script or spreadsheet you may have already copied into this account; we want to start from scratch. Now that you are only logged into your new email and it’s a clean slate, copy the spreadsheet/script again from the source (step 3 in my tutorial: use the link provided). Open that spreadsheet and authorize it (step 5). My hope is that the script will now be authorized by your new account with the higher-limits. Let me know how it goes! Reply Mark says: March 7, 2014 at 6:37 pm Hey there Steffon, I tried that, deleted spreadsheets with scripts, logged out, restarted computer, copied the spreadsheet from this page again, and I still get the message saying I have tried too many times in a day! I have a suspicion of two things, 1/ it takes a while to get the full account functioning fully. 2/ there is no guarantee about the number of times google allows you to run a script from spreadsheet to gmail each day. I have twice had google people implying there was no definite number. For me it has been 99 or 100, which is very limiting… and annoying I changed my account and spent two days trying to get this going, only to find google are a lot less friendly and open than I had thought a few days ago. Sad but true.

steffon says: March 10, 2014 at 11:14 am Hello Mark – It looks like you are not alone: I found this Google support thread with lots of people complaining about upgrading to the business account and not getting the email send limit increased to 1,500/day (https://code.google.com/p/google-apps-script-issues/issues/detail?id=3620). The consensus on the thread agrees with your first suspicion, that it takes time for the limit to lift. And everyone on the thread is also furious about it taking “time” for a product they are paying for now. This looks like really bad product design on Google’s part, especially since they advertise a quota of 1,500 scripted email sends per day for business accounts: https://developers.google.com/apps-script/guides/services/quotas . The thread also looks like the customer service is pretty bad too It looks like the correct course of action here is to escalate the issue with support as much as possible and get this fixed.

18.

Dan Weaver says: March 12, 2014 at 11:14 am Hi Steffon et al, Thanks for the post and GREAT answers to individual’s questions! I tried this last year using Amit’s script and can’t remember exactly the issue I’m going to point out but roughly: the emails are sent and received using a dissimilar mechanism to sending gmail normally (I guess this is why you are required to write the ‘senders full name’ – an omen of issues as gmail knows your name!) The emails are received from a nonstandard address (like a proxy perhaps) and filtered out of a recipients inbox into spam or similar. I sent 20 or so emails this way and got 0 responses until I realised they were being filtered by competent email systems. Can you clarify the issue I’m struggling to remember? I found this looking for a way to upload to drafts and then modify and send manually… still looking! Hugs, DrDan Reply steffon says: March 14, 2014 at 12:08 am Hello Dan! Thank you for the kind words. I’m glad you’re enjoying the post/community as a resource. If I don’t understand your question entirely I apologize, but it looks like you’re trying to evaluate if you should give this mail merge approach another try after having your 20 emails from last year were marked as spam. Unfortunately, I have not heard of this issue before but that doesn’t mean it isn’t happening. Spam filters can be tricky and unpredictable but here are few things to consider. First, each email with this script is sent directly from your email account. I am lead to believe this because you can see each individual email in your “sent” folder. The recipients of your email can’t tell that they were sent “rapid-fire” so, from their email client’s point-of-view, it should look like the email is coming from you “normally”. Second, if you are using a custom domain name (like [email protected]) then there are steps you can take to reduce the odds your email will look like spam. These steps are important when using a custom domain because the “world” doesn’t know if @drdanweaver.com is trustworthy, whereas they might have more confidence with @gmail.com. To authenticate a custom domain, follow the steps here under Authentication & Identification: https://support.google.com/mail/answer/81126?hl=en . I hope my response is helpful. Troubleshooting spam filter behavior is hard (not being able to see other people’s inbox’s and such). If you have a second email address, you can try out the script again, send yourself an email with it, and see if it goes through. Best of luck Dan, and if you have a breakthrough, definitely let me know – I’m interested. Reply

19.

G says: March 20, 2014 at 12:34 pm Hello, I am wondering how I would embed a hyperlink in my google mail merge? Each link is different for each recipient. When I run the mail merge normally the link shows up as text. Thanks! Reply steffon says: March 20, 2014 at 6:32 pm Hello! Thank you for writing in. The short answer is that, instead of just typing the link into the form, you need to include the link HTML as well like this: Steffon Davis

For more explanation on this answer, please look at my reply to Justin on February 20, 2014 at 7:17 pm (just scroll-up). Best of luck! Reply 20.

Camilla says: March 24, 2014 at 12:31 pm Hi Steffon, I’ve created some spreadsheets with mail merge, but I would like to measure the results…. basically I would like to know if I can record the number of lines (number of emails sent) and the template used (the email subject) in a separate spreadsheet or just sent this variables to an specific email once the mail merge spreadsheet is used… Hope you can help Thanks so much! Reply steffon says: March 26, 2014 at 1:52 pm Hello Camilla! Thank you for your question. I may not be fully understanding your requirements, but it sounds like you’d like additional ways to track the emails sent. But let me try and reply and ask some follow-up questions; it sounds like you’d like to record the number of emails sent for each template used. This makes me think of the “Mail Merge Status” column already in place in the spreadsheet which prints “EMAIL_SENT” every time an email is sent. It seems like you could total the number of rows with “EMAIL_SENT” to confirm how many were sent for that template. This would need to be done for each template used. To make this process automatic the script itself would need to be modified to keep a running tally of the number of emails sent for each template. As of right now, you’d have to record the total manually for each template. Hope that helps, and if I’m missing your point, just let know! Reply

21.

Nic Boyde says: April 1, 2014 at 1:31 am copyBlob Here’s a wrinkle: Create your message and insert an inline image. Run mailMerge. Observe copyBlob message. Go back to draft of email and remove images(s). re-run mailMerge. Emails now send successfuly, but images are attachments, not inline. Reply steffon says: April 1, 2014 at 7:48 pm Confirmed: I followed your exact steps and got the same results. It’s an interesting wrinkle: when I removed the inline image after the copyBlog message, and sent the mail merge again, it included the image as an attachment; how weird is that. Reply

22.

Nic Boyde says: April 1, 2014 at 1:55 am Further wrinkle: Add inline image, try mailMerge. Watch copyBlob error message. Remove image from draft, and add it straight back. Retry mailMerge. Voila! It works. copyBlob isn’t Google Policy to prevent inline images in mass-mailings: it’s a bug. Reply steffon says: April 1, 2014 at 7:52 pm Confirmed again! I agree, this looks like a bug. I added an inline image, got the copyBlog error, removed it, then added it back, and the mailMerge included it succesfully as an inline image. It indeed, works. Great finding Nic, thank you for sharing! So here’s a question: do you think it’s google’s intent to block inline images for email scripts, and you found a clever workaround, OR, do you think the copyBlob is an honest error that their product managers would want to fix? If it’s a genuine error, I’d like for them to fix it, but if this is a smart workaround, I’d like for them to, you know, NOT fix it. Reply

23.

Dee says: April 11, 2014 at 2:22 am Hey Steffon, lovely work on the mail merge! I used your script to send mass mailers to around 700 of my clients today, but a small glitch happened. It so happened that 2 of my clients who were in cc/bcc (Not sure how!) and they have recd the same mail 700 timesssssssss!! Its scary! Also I had 2 emails in my draft and clearly remember sending one of it.. But all 700 people have recd the 2nd email too and that too has gone to these 2 bcc/cc ids 700 times Pls help as I have another 700 to mail tomo and am scared!!! Reply steffon says: April 15, 2014 at 1:10 am Hello Dee! Thank you for the nice words. I’m sorry to hear about that glitch; it sounds super annoying. Is it possible the two clients were in the cc/bcc of the template email itself? Or did you already rule that out? I hope the additional mail merges went off without a hitch. Reply

24.

jeremymarks says: April 11, 2014 at 4:22 am Thanks so much for your tutorial. I am a personal user (not a business) and am trying to send a greeting to 300 contacts. I’ve run your tutorial and only 50 emails were sent before I hit the error message about the email daily limit. 24 hours later, I tried to send the balance, but only a further 50 were sent again! So, instead of the 100 limit that seems to be imposed by Gmail (and I still don’t understand the logic of this limit), I am being limited to 50 per day! Do you have any thoughts why this might be. It’s a bit frustrating, particularly given that I was so excited that this mail merge worked so well. Very many thanks indeed. Reply steffon says: April 15, 2014 at 1:22 am Hello! Thank you for writing in. I’ve poked around to try and find some details on a limitation of 50 and did find that Google limits the number of email recipients per message to 50 (https://developers.google.com/apps-script/guides/services/quotas). This tutorial however, assigns only one user per email, so you shouldn’t be hitting this limit of 50. I’m not exactly sure why your limit would be at 50 when it should be at 100. But I thought I’d post it here anyway and see if anyone else has an idea. It may be worth posting this as an issue on the Google-apps-script-issues group: https://code.google.com/p/google-apps-script-issues/ Reply

25.

Paul says: April 17, 2014 at 10:13 am Hi Steffon, Thanks so much for this, it is really useful. I have a second email address setup on my account that is not my primary address and I need to send the emails from that address but cannot work out how to get this to work as it keeps just sending them from my primary account. I have tried going into my accounts and making the second address my default one but this didn’t work either. Any ideas how I could get this to work Reply steffon says: April 23, 2014 at 3:09 pm Hello Paul – Thank you for writing in and for the question. Can you provide more details about your secondary email address? For example, are you using Google Apps, or are you using aliases in Gmail? Aliases in gmail look like adding dots ([email protected]) or adding a “+” sign ([email protected]). Adding aliases in Google Apps requires logging into the admin dashboard and adding them manually. For example, your primary email address may be “[email protected]”, but you’ve created aliases like “[email protected]” and “[email protected]” that are sent to the “name@domain” inbox. That way you only manage one inbox. My guess is you’ve done this and you want to send the mailer from, for example, “[email protected]”, even though your primary email address is “[email protected]”. Can you specify which is the case? Reply

26.

Heather says: May 7, 2014 at 6:57 am So I don’t think I saw this mentioned in the comments. I keep receiving this error: “Error encountered: Cannot read property “length” from null.” I don’t think I’m over the daily sending limit, and am only trying to send 38 emails at once. Has anyone else come across this error? Thanks in advanced for any help! Reply steffon says: May 7, 2014 at 2:51 pm Hello Heather – Thanks for the question. My guess would be that the script is having trouble parsing the data in your spreadsheet. Can you give some details about the mailer you are trying to put together? Here is something to try on your own: Can you use the script to send out just one email address from the spreadsheet? Do you still get the error if you simplify the process down to it’s very essentials? Reply

27.

Dinesh Rathi says: May 26, 2014 at 7:32 am Can this solution be used as mass emailer. I need to send about one emailer to 125K recipients once a week. Is the quota limit talked about here is that the number of unique emails or the number of recipients. Reply steffon says: May 26, 2014 at 2:58 pm The Google quota limit documentation is here: https://developers.google.com/apps-script/guides/services/quotas . Consumer Gmail is limited to 100 per day. The business accounts are limited to 1,500 per day. Reply

28.

Abi Lopez says: June 3, 2014 at 5:32 pm Hi Steffon, Thank you so much for the clear instructions! I’m having some trouble, however. I got the mail merge to send, but when I test it by sending it to myself, it does not populate the variables, it just shows the variable name (ie %First Name%). How do I fix this? Thanks in advance. Reply steffon says: June 3, 2014 at 6:18 pm Hello Abi – Thanks for the question. Here are two things to check. The first, is that your column name and the variable name in your email are exactly the same. So, For example, if the column name in your spreadsheet is “Name” than the variable in your email needs to be $%Name%. The second thing to check is to make sure you’ve enclosed the variable name just like this: $%First Name% . Notice it starts with a dollar sign, and then has percent symbols around the text. This should go directly into the body of your email (see step 2). If this still doesn’t work, please take a screenshot of your spreadsheet and template email and reply with links to them and I’ll take a look at your setup. Reply

29.

Nick Desmedt says: June 11, 2014 at 2:11 am Hi Steffon I added a custom email to my gmail account, so it looks like emails were sent from my business email. I set it as default email in gmail, but when i try the mail merge script, it still sends out the emails from my gmail email. Can this be changed, or isn’t it possible to send from my custom email with this script? Greetz Nick Reply steffon says: June 11, 2014 at 10:56 pm Hello Nick! Thank you for the great question. Yes, it should be possible to use the script to send from a custom gmail address (an “alias”) but it will need tweaking. I found a reasonable looking set of instructions here that shows how to alter the script to take advantage of an email alias. I unfortunately, don’t have an alias setup with which to test these alterations (sorry about that, I usually try to test everything), but rather than stay silent, I thought I’d pass this resource along: http://inevitableimprovement.com/email/using-mail-merge-in-gmail-with-an-alias . If you find that it works for you, it would be great to hear back. [UPDATE: Nick replied on Twitter and said that the resource WORKED. So yeah, if you want to use aliases too, follow the link for the solution!] Reply

30.

Dave says: June 12, 2014 at 4:57 pm HI Steffon, I am just a little bit worried about the wording regarding your app authorization. It states This app would like to: View and manage your mail View and manage your spreadsheets in Google Drive Send email as you Please explain, as I only want to use this as a one time email and not have others having access to my Gmail. Sorry need to be protected Reply steffon says: June 12, 2014 at 5:21 pm Hello Dave! Thank you for the question. Here is what the authorization messages mean. “View and manage your mail” – This allows the script to access your “drafts” folder. This is where you write the mass-mailer template. “View and manage your spreadsheets in Google Drive” – This allows the script to read and update the spreadsheet that contains your email addresses and other mail merge attributes. “Send email as you” – This allows the script to send the email from your email account. In total, these three permissions allow the mail merge script to do the things you want it to do. Now, that said, it could ALSO be doing malicious things with your email address. Which is why I recommend checking out the script code for yourself (which you totally can). If you don’t understand the script you’ll be running, then you’ll just have to trust the other people who’ve used it, which, from my blog post, now count over +1877 downloads. I reviewed the code and it looked solid to me, which is why I posted it. In short, I would trust this script and I have trusted this script, and you are wise to not just run whatever script you find running wild on the internet. Best of luck! Reply

31.

Chana says: June 13, 2014 at 8:03 pm Hello, I couldn’t seem to make it work. When i click Mail Merge > Start Mail Merge, it will always say “You do not have permission to perform that action” despite granting permissions already for the script. Could you help me about this? Thank you very much! Reply steffon says: June 17, 2014 at 6:54 pm Hello Chana – Thank you for the question. When Google asks you to give the script permission, it grants permission to a specific google account. This might be for a personal gmail address, of a work address that uses Google Apps for hosting. It’s possible that, when you grant the script permission, it’s assigned to the wrong account: this would be for no fault of your own and sometimes happens when people are simultaneously logged into several google accounts (like work and personal). To make sure this is not happening, I recommend opening Chrome in incognito mode, or FireFox in private mode, login to the one email account of interest, and try again. This will guarantee that there are no conflicting cookies or login ID’s so when you grant permissions for your email of interest, it should definitely get recorded for that email address. Best of luck. Reply

32.

Angela says: June 18, 2014 at 2:44 am Hi Steffon, Thanks for the tips. I am using the free Gmail, so I have 100 free mail per day. However, I sent out 100 yesterday but I get only 1 more allowed today. How did it happen? How can I fix it? Best, Reply steffon says: June 18, 2014 at 3:06 am Hello Angela – Thank you for the question. Gmail’s daily limits are measured over a rolling 24-hour period (https://support.google.com/a/answer/166852). So it’s quite possible to send out 99 emails at 6pm on Monday, and try to send out 2 emails at 2pm on Tuesday and hit the limit (even though it’s a “new day”). Try thinking about how many emails you sent over over the past 24 hours and, if that number is over 100, than you’ve crossed the limit. For example, if you sent out 100 emails at 6pm, you’ll need to wait until 6pm the next day to send any emails at all (is my understanding). But, if you sent out 3 emails every hour, than each hour that passes gives you 3 more emails to send since it’s a rolling 24-hour period. Reply

33.

Diane Blankenship says: July 16, 2014 at 7:42 am I am also getting the error: “Error encountered: Cannot read property “length” from null.” I have tried changing/removing data on the spreadsheet but nothing seems to be correcting the error. Any suggestions? Thx Reply steffon says: July 17, 2014 at 10:04 am Hi Diane – Thank you for the question. This is a tricky error to diagnose without seeing what the data in your spreadsheet looks like. As a result, let’s try an experiment. Remove all data from your spreadsheet except for 1 email address (your own). Will the script send out this 1 email to yourself? If not, please send back a full description of the error and of the columns/data in your spreadsheet so I have a little more to go on. Reply

34.

Coach Aileen says: July 16, 2014 at 9:26 am This was an amazing tutorial. It was a cinch. I only needed to e-mail a handful of people, and it seems like Google should have an easier way to this internally; but, this was a good practice run. Knowing how to mail merge is going to be so helpful in the near future. Thank you, Steffon! Reply steffon says: July 17, 2014 at 10:05 am Thanks Coach Aileen! I appreciate the note. Happy mail merging. Reply

35.

Ali Lawrence says: July 30, 2014 at 1:01 pm Hi Steffon, Thanks so much for sharing this! I tried doing a mail merge today but unfortunately I got this error: http://prntscr.com/47v411 Do you happen to know what it means? Thanks Reply steffon says: August 4, 2014 at 12:39 pm Hi Ali – Thanks for the question. This is a tough error mostly because I haven’t been able to reproduce it. Some people hypothesize it appears when you try to include inline images (see here: http://ctrlq.org/help/302-gmail-merge-error). To try and sort out why the error occurs for you, strip the mail-merge down to the basics. Send one email to yourself with text only. If that works, then increasingly add more complicated things to the mail merge. If you get the error when you add, for example, images, then that would be a huge find! Reply

36.

Andy Cochrane says: August 1, 2014 at 2:12 pm Steffon – I’m getting a similar error to a few above “Error encountered: cannot read property “1” from null.” I have tried removing all my data except for my own email and still have no luck. Have you found solutions for the others with a similar problem? Would you mind diagnosing my problem further? Thanks! Reply steffon says: August 4, 2014 at 12:39 pm Hi Andy – Thanks for the questio/n. This is a tough error mostly because I haven’t been able to reproduce it. Some people hypothesize it appears when you try to include inline images (see here: http://ctrlq.org/help/302-gmail-merge-error). To try and sort out why the error occurs for you, strip the mail-merge down to the basics. Send one email to yourself with text only. If that works, then increasingly add more complicated things to the mail merge. If you get the error when you add, for example, images, then that would be a huge find! Reply

37.

Richie says: August 8, 2014 at 5:02 am Hi there, I was having the same problem with the “Error encountered: cannot read property “1² from null.” error. I had an inline image in the email and once I removed it the script ran perfectly. Is there a way to send the emails with the image? Regards Richie Reply steffon says: August 12, 2014 at 1:07 am Hi Richie – Thank you for the question. The first thing I’d try to do is NOT upload the image directly to the email body itself, but make it available elsewhere online (like imgur) and reference it as a link in the email. Usually this prompts people to accept that they display images in the email, but for the time being, that might be the best approach to getting around the error. If you want to try getting the inline images to work, check out the dialogue I had earlier with Nic Boyde (search for his name of the post and it will come up). He found an interesting work around to a different inline image bug, and the workaround may work for this bug too. It could be worth trying. If you find that it works, definitely let me know. Good luck! Reply Jeff says: September 10, 2014 at 4:34 am Hi, Thanks for the great script. I had the same “null” problem as above. It appears to be related to the inline image, as you suggested. I tried a number of different emails and the ones with inline images always produced the error while the ones without an inline image went through fine. Unfortunately the workaround Nic suggested did not work for this error. I hope a workaround is found, as I’d really like to include a header image with my emails. Meanwhile I’ll look into the imgur idea. Thanks! Reply

38.

Sarah McGowan-Freije says: August 18, 2014 at 10:54 am Hi Steffon, This is a great script, but I am having one issue. I am new to Gmail and Google docs, so this may be a simple question. When I create the draft email, it gets saved into my Gmail account, and when I go to select it as a template during the Mail Merge, I get a message that there are no emails available to choose from. How can I save the draft to Google docs so I can use it with the script? Thanks! Sarah Reply steffon says: August 18, 2014 at 12:32 pm Hello Sarah! Thanks for the question. Were you able to successfully grant the spreadsheet authorization? Try looking at step 5 again and if that doesn’t work just let me know. Reply

39. Pingback: QUERY - Google Script for Gmail

40.

John says: August 20, 2014 at 2:07 am Hi Steffon, Need your help. I am using Gmail for my business email account which is the one I provided in this message. We are using Google Apps which are paid. Everytime I send mailmerge, it is limited to 100 per day but I can see from your post here that it shouldnt be the case for business accounts using GApps. Please advise what needs to be done. Best Regards John Reply steffon says: August 20, 2014 at 12:01 pm Hi John! Thanks for your question. Here are the steps I would take. First, try logging into your paid, business email account with Google Chrome in Incognito Mode. It’s possible that, if you’re logged-into several accounts at once, that the mailing script registered with your free gmail account with the 100 email limit, not the paid account. We want to rule that possibility out. If you run the script making sure that you are only logged-in with you Google business email account, and you’re still getting limited to 100 per day, then I’d contact Google Apps Support because it should be 1,500 per day: https://developers.google.com/apps-script/guides/services/quotas Reply

41.

Aaron Latina says: August 26, 2014 at 11:40 pm Hey Steffon, I’ve been looking around for something like this for a while. As a Gmail and Drive user, this could be the answer I’ve been looking for. I went ahead to tried a test run and could not get it to work. I’ve seen other people with consumer Gmail accounts hit the 100 email limit. I did as well, except that my test document in Drive only had 3 lines and contacts on it. It seems that I hit the limit after only one email was sent. Any ideas? Thanks so much, Aaron Reply steffon says: August 27, 2014 at 2:47 am Hello Aaron – Thanks for the question. Had you already sent out a bunch of emails over the previous 24 hour period? Gmail’s daily limits are measured over a rolling 24-hour period (https://support.google.com/a/answer/166852). So it’s quite possible to send out 99 emails at 6pm on Monday, and try to send out 2 emails at 2pm on Tuesday and hit the limit (even though it’s a “new day”). Try thinking about how many emails you sent over over the past 24 hours and, if that number is over 100, than you’ve crossed the limit. So I’d give it another shot in 24 hours and see if you still get the error. Reply Aaron Latina says: August 27, 2014 at 6:11 pm No, I wish that was the case. That would definitely be the easy fix for sure. I was still able to send other emails through gmail throughout the night after the mail merge kept saying I had reached that limit. Very strange. I’ll give it 24 hours and try again. Thanks, Aaron Reply

42.

Florida says: September 19, 2014 at 7:07 am When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several e-mails with the same comment. Is there any way you can remove me from that service? Thanks a lot! Reply steffon says: September 19, 2014 at 12:26 pm Hi – Does this tutorial work for you to stop receiving updates from comments? http://en.support.wordpress.com/following-comments/ Reply

43.

Eirik Øra says: September 19, 2014 at 7:46 am Hi Steffon, I use Google Apps for my business and this was the script/tool that I really needed! Thank you for creating it. However, In step 5, I manage to authorize Mail Merge with the first click. However, when I choose this menu option for a second or third time, I only get this error message: “Mail merge HD: Google Apps Script – You do not have access to perform this action!” I’m not able to figure out where to authorize any more access. I am the Google Apps Admin for my company, but where is the barrier? Best regards, Eirik, Oslo, Norway Reply Eirik Øra says: September 19, 2014 at 8:04 am Hi, I found out that I was having problems with being logged in with multiple gmail accounts at the same time. I logged out and removed the first copy of the document and then tried the procedure again. Now I can actually start the mail merge script, but get an error message: “https://docs.google.com: Error encountered: Cannot read property “1” from null. ” Any ideas what is stopping me now? Reply steffon says: September 19, 2014 at 12:32 pm Hello Eirik – Have you tried removing images from the email? See if it works without images first. Reply Eirik Øra says: September 21, 2014 at 6:35 am Yes, that fixed it! How come? It works like a charm now, but only without inserted pictures. Thanks!! Reply

44.

Mark says: September 21, 2014 at 3:06 pm Hi Steffon, Can you help me with an error message? Error encountered: Cannot read property “1” from null. Any idea what I’m doing wrong? Reply steffon says: September 29, 2014 at 5:13 pm Hi Mark – Thanks for the question. Do you always get the error, or is it only when you try to add pictures? Reply

45.

Aparna says: October 5, 2014 at 1:52 pm Hi Steffon, Thanks for the Info. I wanted to know whether we can draft the messages instead of sending them ? Is there any method for saving messages as draft, so that I could write it ? Reply steffon says: October 7, 2014 at 9:37 am Hi Aparna – Thanks for writing in. Step 1 calls for creating a draft of your message in gmail. Without this draft, the mail merge spreadsheet has no email to send. So yes, you can draft as many messages as you want until you are ready to select one and send it. If this isn’t answering your question, feel free to write back. Reply

46.

Girish says: October 6, 2014 at 10:19 am Hi steffon, Is it possible that I can send same mail at a time to various people by keeping them in “To” list and Cc to my own managers via Gmail Account. I know Bcc option is there for this but the recipient will get this message as Bcc and they may feel that this particular mail have just came in unusual way as there was another person in “To & Cc” option. Reply steffon says: October 7, 2014 at 9:44 am Hello Girish – Thanks for the question and I have something for you to try: When you draft your email, you can put an email address into the CC field. If you put your manager’s email address into the CC field, your manager will receive a copy of every single email sent out by the mail merge: they will be CC’d on everything. I don’t know if that is desirable, but it works. Reply

47.

Elliot says: October 7, 2014 at 2:22 am Hey Steffon, I’m wondering how, if at all, I can remove the ‘via maestro.bounces.google.com’ from next to my e-mail? Thanks, looking forward to your response. Reply steffon says: October 7, 2014 at 9:49 am Hi Elliot – I’ve personally not seen maestro.bounces.google.com, but by googling it it looks like you’re not alone. Here’s a potential answer: http://ctrlq.org/help/354-removemaestro-bounces-google-com-from-email-sender Reply

48.

Taryn says: October 22, 2014 at 11:54 pm Thanks so much for this! It works like a charm for me. Is there any way to get it to work with an image in the body? I made jpeg graphics for halloween marketing and have it copied and pasted in the body of the draft, but keep getting the ‘Error encountered: Cannot read property “1” from null.’ error. Reply steffon says: November 4, 2014 at 6:52 pm Hi Taryn – Thanks for the comment: at this moment, the null error seems to be a deal breaker for inline images (if you’re getting it: other people don’t seem to get this error). What you can try is, instead of uploading the image directly to the body of your email, try uploading it somewhere else, and linking to it. Reply

49.

Bernardas says: November 2, 2014 at 4:17 am Hi, is it possible to set the time when email will be send? Reply steffon says: November 4, 2014 at 6:53 pm Hi Bernardas – No, setting the email on a timer is not currently built into the script. Reply

50.

Billy says: November 19, 2014 at 5:39 pm Hi Steffon, Great tips it works like a charm. Is there anyway to get some stats on how many users opened the email, etc (like the mailchimp stats)? Reply steffon says: November 19, 2014 at 9:45 pm Hi Billy – Thanks for the note and I’m glad it worked easily for you! There are many ways to get stats on how many people opened your email through 3rd party services, though I haven’t tried to integrate any of those services with this technique. If you have luck with it, definitely let me know. Here are some 3rd party services I found with the google search “gmail track opens”: https://www.streak.com/email-tracking-in-gmail http://www.hongkiat.com/blog/track-opened-gmail/ http://www.boomeranggmail.com/l/read-receipt-gmail.html http://bananatag.com/email-tracking/gmail/ http://www.contactmonkey.com/ https://mailtrack.io/en/ Reply

51.

Jason says: December 2, 2014 at 7:03 pm Steffon, Thank you this is very helpful. I did have one challenge, when I send the email it works great but when they are opened on a mobile device the text is extremely small. Any advice? Reply steffon says: December 9, 2014 at 12:23 am Hi Jason – Thanks for writing in: that’s odd that the text would look smaller on mobile. The formatting of the emails sent should be the formatting present in the draft email you composed. Which makes me suspect it may be a function of the email client being used. One thing to try would be to open the same email in a 2nd email client on your phone and see if it’s rendered small in that one as well. I’d also double check the formatting of your draft email that was sent out. Reply

52.

Joe A. says: December 18, 2014 at 8:18 pm You are a God. Reply steffon says: December 19, 2014 at 2:58 pm Hi Joe – love your enthusiasm for mass mailers! Reply Joe Albergo says: April 7, 2015 at 3:51 pm Steffon, it was great to speak to you on the phone and it’s been very great so far. Any news on getting the Subject Lines to be customizable with variables as well? -Joe Reply steffon says: April 7, 2015 at 7:01 pm Hi Joe – Nice to hear from you. The example in this post shows how to customize subject lines with variables (see “Good Afternoon $%First Name%”). Hope that helps! Reply

53.

Nathan says: January 5, 2015 at 9:25 am Hi! I followed all the steps in the tutorial (they were very clear, thank you), however my mail merge has only sent 14/84 emails. It appears to have stopped sending them now… is this usual? Should I wait a bit in case it picks up again, or re-do the mail merge to send an email to the rest of the recipients? Cheers, – Nathan Reply steffon says: January 5, 2015 at 1:26 pm Hi Nathan – Thanks for your question. My first guess would be that you’ve hit your sending limit for the day so it stopped at the 14th email address. If that is the case, then trying the script later could solve the problem. If that is not the case, then the problem may be with how the 15th email address is formatted. Reply

54.

Maria says: January 6, 2015 at 12:04 pm trying to find out if it is possible to include the same image as a heading in the letter when using gmail for mail merge. There does not seem to be a clear answer to this. Some thing like yes or no and if yes this is how to do it step by step?????? Reply steffon says: January 9, 2015 at 10:27 pm Hi Maria – Thank you for the question. I could use more clarification as to what you mean by, “include the same image as a heading in the letter”. Do you mean how to include a header image? If so, try just adding one into the draft and send a test message: see if it sends out for you. Reply

55.

KAthryn says: January 7, 2015 at 4:29 pm Hi Steffon! Awesome tool. Any chance you have found a work around for the inline images yet? Or do you know of any alternate mail merge tools that could work, even if they aren’t as easy as yours? Thanks. Reply steffon says: January 9, 2015 at 10:45 pm Hi Kathryn – Thanks for writing in with your question. If you are getting the error, ‘Cannot call method “copyBlog” of undefined’, the workaround is to remove the image and then add it back in again. For some reason, adding an image once creates the error, but removing the same image and adding it again doesn’t. If you’re getting a different error, let me know. Reply

56.

Mike C says: January 22, 2015 at 11:02 am This is fantastic! Thank you! Is there any way to include a custom CC with every custom email address? If not, can I send the same email to two different people by separating their emails with a comma in the email address field? Reply steffon says: January 23, 2015 at 8:39 pm Hi Mike! I’m glad to hear it’s working for you! With the script as is, there is no way to include a custom CC with every email address: that would be a nice enhancement for sure. I’ve tried including two email addresses per line, separated by a comma, but that doesn’t work either. a way: you can add two email addresses on each line, separated by a comma (I explain more below, but I wanted to update it here in the thread as well) However I don’t want to leave you high and dry: a colleague of mine recently recommended this email tool from Yesware:http://www.yesware.com/plans-and-pricing He says it supports including BCC fields and it’s free for a little bit (the first 100 emails of the month I think). I haven’t tried it yet but it’s on my list of to tries. If you have luck with it (or something else) feel free to post back. Reply steffon says: January 25, 2015 at 9:22 pm Correction! You can add a second person in the same address field. So, for example, in the spreadsheet if you put the email address, “[email protected], [email protected]” (without the quotes) then the script will send it to both email addresses. Kind of cool. Hope that helps! Reply

57.

Winspire Global Solutions says: January 24, 2015 at 6:18 am Brilliant Steffon!!! This is just a Awesome find, I can save lot of my time now! Thanks a Lot man! -Vivek Kubal, India Reply steffon says: January 26, 2015 at 6:45 pm Thanks for the note Vivek. I appreciate the positive feedback and I’m glad to help! Reply

58.

Mark says: February 15, 2015 at 8:04 am Followed your great instructions but I don’t get the variable picked up in the individual emails. Not sure what I have done wrong. I have copied and pasted the variable from your instructions. Re created the google sheet.. Many thanks Reply steffon says: February 25, 2015 at 6:11 pm Hi Mark – Thanks for the question. Double check to make sure the variable name in the draft of your email e.g. $%variableName$ matches the name of the column in your spreadsheet e.g. variableName . If this doesn’t work, try sending back some specifics of what you’ve called the column in your spreadsheet and the variable you’re putting into the email. Reply

59.

Dzseki says: February 25, 2015 at 5:53 am Hi Steffon, thanks for the script. Could you please tell me what your script does differently than Romain’s original one? https://sites.google.com/site/scriptsexamples/available-webapps/mail-merge Reply steffon says: February 25, 2015 at 6:13 pm Hi Dzeki – It doesn’t do anything different. As I attributed at the start of the post, I got the script from a tutorial created by Amit Agarwal and he got it originally from Romain Vialard. It may have gotten better over time, but the one posted here is from at least 8/5/2013. Reply

60.

Justin Laplante says: March 9, 2015 at 11:32 am Hi Steffon, Firstly, thanks for sharing this, it’s been extremely helpful! I did have a question I was hoping you could help with: I need to include an attachment to the emails that get sent out–is it possible to do that? Thank you very much, Justin Reply steffon says: March 9, 2015 at 6:21 pm Hi Justin – Yep, it is possible: just attach your file to the template email you composed: the attachment will be included with to every email sent. I just tried attaching a 700kb PDF file to the template email and it was included with each email in the mail merge. Good luck! Reply

61.

Clay says: March 11, 2015 at 2:22 pm I really need to store my contacts in Google sheets with one column for which group they are. I need to regularly pick a group and then send a specific email to only them. It would be really nice to store other information on this sheet so I don’t have to have multiple sheets for each group, or copy and paste a huge group all the time. Anyone know if this is available? sending to only a specific set of my contacts is really important and is a daily task. Reply steffon says: March 13, 2015 at 5:29 pm Hi Clay and thanks for the question. Here is something you can try: notice that the column “Mail Merge Status” contains the text “EMAIL_SENT” after an email is sent for that entry. In fact, if you run the mail merge again without deleting those entries, the emails will not be sent (because the sheet thinks that “EMAIL_SENT” already). So, imagine you have a column that groups your names by, for example, state. You could keep all the columns with the value “EMAIL_SENT” and then when you’re ready to send emails to just “Arizona”, you delete “EMAIL_SENT” just for the Arizona group. In that way, you can “easily” select subsets of emails for the mail-merge. If anyone has another idea, feel free to post! Reply

62.

Max Goldman says: March 14, 2015 at 2:10 am Hi Steffon, I am writing an email for my band’s press release and after figuring everything out I sent a test to myself and a few friends. On our laptops everything looks great but when viewing the email on our Iphones It displays as a bunch of code. Any help you could give would be greatly appreciated. I would be happy to send you a test. Thanks so much, Max Reply steffon says: March 14, 2015 at 7:41 pm Hey Max: try sending the same template once manually and then viewing it on your iphones: does it still display a bunch of code? If so, the problem is with the content in the email itself. Reply

63.

Joey says: March 27, 2015 at 3:44 pm Hey Steffon, I am currently crowdfunding and I would like to be able to send more than 100 emails per day using the mail merge feature. I am willing to pay google to be able to send more but I don’t care for a new domain or email. I don’t really want to create a business for using google apps. Is there a way to pay google to allow my personal email to send more than 100 emails using the mail merge script? This is an incredible resource. Thank you for answering so many questions over the past year. Reply steffon says: March 31, 2015 at 3:05 pm Hello Joey – That’s a great question and to my surprise the answer seems to be no: it does not seem possible to lift the sending limit without “upgrading” to Google Apps for Work, which seems to require the use of your own domain: “To use Google Apps, you’ll need a domain name such as yourcompany.com,” says Google here: https://www.google.com/work/apps/business/learn-more/setup.html . That seems like a gmail product flaw: after all, you can easily upgrade your storage, so why not easily upgrade the sending limits. Lame. Reply

64.

Segun says: April 1, 2015 at 1:35 pm Thanks for the post… it was very helpful. The messages were sent when I tried it but the names of the contacts were not personalized. I look through this discussion and followed the process of making sure the variable name and the names are correct but still not personalized. I checked some of my friends mail I tested it with, it just shows the greeting and the hello without their names. Would really appreciate your response on this. Thank you. Reply

65.

Olatunde Segun says: April 1, 2015 at 1:38 pm Thanks for the post… it was helpful. however, when I checked the mails of my friends I tested this with, it does not personalize it. I was careful to follow the instructions regarding the variable name and the names but it’s just showing the greeting and ‘hello’ without their names. Would really appreciate your response to this. Thank you. Reply steffon says: April 5, 2015 at 9:10 pm Hello – I’m sorry to hear that. Here is one suggestion: when you test the process, send only one email to yourself. That way you can troubleshoot a lot faster and experiment more vs. sending email to a friend. Another suggestion would be to first recreate the exact example in the blog post before moving on to your own variable names. If it’s still not working, try writing back with some more supporting information on what you have done on each step and we’ll see if it becomes apparent where the process breaks down for you. Good luck! Reply

66.

jeanne says: April 8, 2015 at 9:37 am I too got an error message when embedding an image. Mine was of the copyBlob variety. Tested it twice. But, then, when I attached the image, the recipient got two copies, same file attached twice. I tested it twice. The fairly good news is that, it *SEEMS* that if there is some text above the image, the merge works. And it *SEEMS* that it is better to paste the image into the mail window than to drag and drop the file. This is only fairly good news because even tho there are only two variables, I cannot test it exhaustively. I don’t *think* either works independently. But I thought you might like this lead. I will definitely check back here to see what you think. Meanwhile, I’ll be trying it until it fails me. Well, your work is extremely nifty and I thank you. Reply

67.

jeanne says: April 11, 2015 at 12:51 am I wrote a couple of days ago about dragging versus pasting an image. I was wrong. It’s completely unpredictable. How unfortunate. But it is worth trying it this way and that because it does seem that it will work if you keep trying. Reply

68.

Panduka says: April 13, 2015 at 12:34 am wow it worked, Thanks but i couldn’t put the inline images, same error came as you mentioned when put the inline image. without inline image it is totally ok.



‘Error encountered: Cannot read property “1” from null.’ Reply steffon says: April 13, 2015 at 7:49 pm Hi Panduka – Thanks for reporting the error. That one is tricky… Reply 69.

FrancesG says: April 16, 2015 at 12:13 pm Hi, 2 problems I’m having: 1) I’m trying to send a mail merge that refers to 2 columns in my Google Drive spreadsheet and with an attachment. The spreadsheet has a FirstName column and a Surname column. Although I have marked up the 2 column names as below, when the email is sent I keep getting ‘Dear AnnSurname’, instead of what I really want which is ‘Dear Ann Bloggs’ I have tried: Dear $%FirstName% $%Surname% and Dear $%FirstName%$%Surname% 2) How to send a Word document as an attachment with all of the emails sent but each time I try the emails come through without any attachments. I have tried just attaching the item to the compose email screen, and also uploading the document to me Google Drive as a Word Document and then in the compose email screen using the Google Drive icon to find and insert the document. Any advice would be appreciated. Thanks. Reply steffon says: April 22, 2015 at 3:17 pm Hi Frances – hmm, those are frustrating problems and I’m not exactly sure what would cause them. Here are two things to try: 1. Try removing the “FirstName” variable and see if you can get just the “Surname” variable to work on it’s own. If you can get them working independently of each other, it may be easier to get them working together. 2. Attached a document to your draft email should work: that document should be sent with each email sent. I’d try sending the emails to yourself as a test to see if it will start working for you. Sorry I can’t be more helpful! Reply Jeanne says: September 3, 2016 at 9:57 am I used this delightful little script a year ago and it was great. Now I’m having this same problem. I tried creating a new name field but no good. I’m baffled. One thing I’ll tell you is that when I try renaming the spreadsheet, the new name displays but the mail merge doesn’t get it. Still, that can’t be the problem because the script still does does send my one test message with “Dear ,” but no name in there. Well, it was great and I hope you can build on it. Thanks for your efforts. Reply

70.

steve says: May 12, 2015 at 12:51 am hi, thank you for being so cool can i send individualized attachments? thank you so much Reply steffon says: May 19, 2015 at 2:13 pm Hello Steve – I added this feature request to the list of possible ways this script could be improved. Thanks for the note. Reply

71.

rock says: May 13, 2015 at 9:09 am Hi, How to Get to Know When Email sent from mail merge, are Opened by Recipient. I have created the custom field in google spreadsheet like “Is Read”. I want to update this field when recipient opened the mail. Please advise. thanks Reply steffon says: May 19, 2015 at 2:13 pm Hi – I added this feature request to the list of possible ways this script could be improved. Thanks for the note. Reply

72.

Bethany says: May 29, 2015 at 10:59 am If I purchase the $5/month paid Google Apps will that allow me to send 1500 emails/day? Or do I have to purchase the $10/month option (which I don’t really need)? Also, is there any way around the mail merge limiting you to sending 50 emails/message at one time? My email lists are much bigger than 50 people. Thanks! Reply steffon says: June 1, 2015 at 6:35 pm Hi Bethany – Thanks for the question: The $5/month plan should allow you to send 2,000 emails per day (https://support.google.com/a/answer/166852?hl=en). The $10/month option looks like it adds unlimited storage. How is the mail merge limiting you to 50 emails/messages at a time? Reply

73.

Suri says: June 2, 2015 at 1:38 pm Hi, Thanks for the tool. This really comes in handy. I did face the issue with image, when I removed it and added back the problem is gone . Here is my question. Will I encounter any issue with adding a video link or an embedded video? Thanks, Suri Reply steffon says: June 2, 2015 at 6:52 pm Hello Suri – Thanks for the note! Adding a video link or an embedded video shouldn’t cause any problems. But it can’t hurt to send yourself a test email 1st to make sure it looks right. Good luck! Reply Suri says: June 11, 2015 at 5:18 pm Hi, It worked. Thanks! I do have another question though, When I tried the first time It showed I can send up to 1500 mails in a day. But when I was actually trying it out I got a message saying that ” I exceeded my limit of mail for today” @ 750 mails. Do I need remove the first 750 names from the list to continue sending mails for the rest of the users or I can leave it there and your script will automatically picks up from the 751st user email-id? Thanks, Suri Reply

74.

Alexander says: June 8, 2015 at 11:39 am Hi, the script seems to be a helpful tool, but I am encountering an issue that is staying in my way. Once I send my emails, the text appears in a single bulk paragraph, rather than in different paragraphs, as it is in the original Google docs file. Basically, it looks like this: Hello, This my email here. Best regards, Alex. What I want to achieve is: Hello, This is my email here, Best regards, Alex How can I separate those paragraphs? Thank you for your assistance and I apologize if there is an answer to this inquiry already. Reply steffon says: July 14, 2015 at 2:24 pm Hi Alex – I would keep as much text outside of the Google docs file as possible and keep it inside the email “compose” window inside Gmail. In that way, the formatting you’ve put into place will be preserved. Reply

75.

Bethany says: June 18, 2015 at 12:59 pm Hi, When it’s doing the merge, it sends me a message once it hits the 51st entry and tells me I can’t send more than 50 emails/day. Should I be allowed to send more per day for free? Thanks! Bethany Reply

76.

Nitesh says: June 20, 2015 at 8:31 am Hey Steffon, Thanks heaps for your post. Just wondering, if I could personalize the PDF attachment as well ? Thanks, Nitesh.N Reply steffon says: July 14, 2015 at 2:23 pm Hi Nitesh – While the current script does not support customized attachments, here is an alternative approach: upload your different PDFs to something like Google drive, share them and retrieve their public URL, and then include custom URLs linking to the different PDFs as its own variable in the script. In this way, you can include customized links to the different PDFs. Best of luck! Reply

77.

Max Greenhalgh says: June 30, 2015 at 7:02 pm Hi there, Do you know if there’s away to make the $%First Name% portion not italicized in the sent emails? It looks kinda blatant when only the name is italicized. Thanks! Reply steffon says: July 14, 2015 at 2:21 pm Hi Max – Thanks for the question. The variable should only look italicized if you explicitly set that formatting in the email template itself. Otherwise, I have not heard of this happening (yet anyway). I would try testing with a brand new email that has only the variable name and zero formatting to see if it keeps happening. Reply

78.

Nishesh says: July 7, 2015 at 6:42 am Steffon, Excellent tool and really simple to use. Thanks so much. Your idea of using imgur link for inline images worked like charm to overcome the null to 1 error problem. In fact at least in gmail inboxes the images render automatically without asking the reader to click to view images. It might be different in corporate inboxes. Thanks a lot. Reply steffon says: July 14, 2015 at 2:19 pm Awesome, I’m glad it worked for you! Reply

79.

Joao Alexandre says: July 7, 2015 at 2:02 pm Steffon, good job. It’s nice knowing this article is still receiving comments this long after it was written. It must be a sense of accomplishment for putting out this value out there for you? This is a great article for those wishing to send personalized emails through Gmail. It has the advantage of sending using from gmail with a [email protected] email account instead of a gmail account. That is, if you’re paying for google apps or you’re one of their legacy users (from what I’ve read online). It would be great to know, as was stated by you and some other user(s), the open rate of emails. However, with YesWare you can do this. YesWare is an app used by sales people to track if and when their prospects open their emails. Perhaps it can somehow be integrated with the script, or it may be redundant if the app (I haven’t tested it, be my guest?) does that regardless of the script. Call me Joah, it’s easier. Reply steffon says: July 14, 2015 at 2:18 pm Hey Joah – Thanks for the note! I’m not maintaining this script, but it would be awesome to see it integrated with something like YesWare. If something like that happens, definitely let me know. Reply

80.

Marcus says: July 15, 2015 at 3:30 pm Hey Steffon! I followed your tutorial and it all worked great! The one issue I did have however, the variable $%FIRSTNAME% did not work by inputting everyones individual name. Do you know what could be the problem? Reply steffon says: July 21, 2015 at 2:08 pm Hi Marcus – Thanks for the question. I’d first check to see if the variable name in the spreadsheet matches exactly with the variable name supplied to the email draft. For example, the spreadsheet comes with the variable name “First Name” as a default, meaning that “FIRSTNAME” won’t relate back to it. Good luck experimenting. Reply

81.

Quintin says: July 21, 2015 at 3:58 pm Steffon, I just wanted to say that I find it remarkable that you reply to every single comment! Even when the last 2 are a year apart! Great job with this blog, very useful info, and good luck with future ones Reply steffon says: July 22, 2015 at 5:44 pm Hi Quintin – Thanks for the note, I appreciate it! Glad you’re finding it useful. Reply

82.

Erica Atencion says: July 21, 2015 at 7:56 pm Hi! I used this mail merge once and it went fine. Now, I’m trying again.. After my 2nd time starting mail merge I can an offline access window… and then nothing. Erica Reply steffon says: July 22, 2015 at 5:42 pm Hi Erica – Thanks for writing in. Would you mind supplying more details? I don’t quite understand, “I can an offline access window… and then nothing”. Hear from you soon. Reply

83.

Sarah says: July 27, 2015 at 5:11 pm After I accept the first authorizations and hit “Start Mail Merge” again I get a request to authorize Offline Access. I accept this, the hit “Start Mail Merge” again, and get the same authorization request for Offline Access. This cycle continues every time I select “Start Mail Merge.” Thoughts on how to solves this? Reply steffon says: July 28, 2015 at 2:47 pm Hi Sarah – Thanks for the question. It sounds like, despite granting authorization, it’s not actually being recorded by gmail. This can sometimes happen if you’re signed-in with multiple Google account simultaneously. To rule that possibility out, try closing all browsers and make sure you are only signed-in as one google user in chrome. Then, try to authorize that one user. Reply

84.

WALE says: September 1, 2015 at 4:10 am Thank steffon, really helped Reply steffon says: September 1, 2015 at 3:28 pm Thanks for the note Wale! I’m glad the post helped Reply

85.

Michela says: September 7, 2015 at 7:00 pm Hello Steffon, thank you very much for this helpful tool! I too had the problem with inline images (Error encountered: Cannot read property “1” ) and I solved it including the image in the email signature, in gmail options. Maybe this suggestion can be useful for somebody else.

Reply steffon says: September 8, 2015 at 2:00 pm Hi Michela – Thanks for the note and the tip! I’ve updated the post with your suggestion (and credited you). If you have a website you’d like me to link your credit to just let me know. Reply 86.

Yair says: September 12, 2015 at 9:36 pm I’m done using MailMerge. How can I remove access to my account? Thanks! Great tool Reply steffon says: September 14, 2015 at 1:52 am Hi Yair – Great question and thanks for asking! I updated the post with instructions on how to deauthenticate the spreadsheet and revoke its access to your account. For details, please refer to this new section above. But essentially, you should be able to click this link while logged into Google (https://security.google.com/settings/security/permissions) and then click “REMOVE” to deauthenticate the spreadsheet. Hope that helps! Reply

87.

WALE says: September 14, 2015 at 4:26 am Hi steffon- im getting this error “cannot call method “mailbob”of undefined” please help! Reply steffon says: September 15, 2015 at 10:06 pm Hi WALE – Thanks for the question. I haven’t seen the error before. Can you confirm that that’s the exact error and describe where in the process it appears? Thanks! Reply WALE says: September 21, 2015 at 4:12 am Dear Steffon- That’s the exact error, it appears when i hit the send button. Thanks In Advance!! Reply

88.

Rajesh says: September 16, 2015 at 7:22 am Hi Steffon, Is there a possibility to include a delay of let’s say 1 minute between each email sent? Reply steffon says: October 7, 2015 at 2:51 pm Hi Rajesh: That’s a great idea. I just added it to the list of suggestions for “expanded functionality” in the post. Reply

89.

SF says: October 2, 2015 at 12:27 pm Hi, Steffon, This is a super tool! Thank you so much sharing it online and making it so clear. I’ve seen glimpses of this in the comments, but perhaps missed a full answer. I’m being blocked from sending more than 100 emails for the fundraiser our non-profit is hosting. Is there a way around this? Sorry to ask you to answer again, because my guess is you’ve already answered somewhere. Best, SF Reply steffon says: October 7, 2015 at 2:53 pm Thanks for the question! As it turns out, Google limits the number of script-sent emails to 100 when you’re using the free version of gmail. If you use the Biz/Edu/Gov version, the limit is 1,500! See posted limitations here: https://developers.google.com/apps-script/guides/services/quotas If you need to send huge mailers, I suggest a few services at the end of the post. Best of luck! Reply

90.

Craig says: November 16, 2015 at 5:04 pm Steffon, I know absolutely nothing about programming, so forgive me if this is a stupid question. Is it possible to add more items to the spreadsheet which would also appear as an individualized item in each email (besides the First Name)? I’m a teacher and it would be super cool if I could send out student grades from the spreadsheet. But of course, each individual email would need to reference that student’s specific grade. Make sense? Thanks, Craig Reply steffon says: November 16, 2015 at 5:09 pm Hi Craig – Thanks for the question! You can add a “grade” variable in two steps. First, create a column in the spreadsheet called “grade” (without the quotes). I’d insert that column after the “First Name” column. Then, type the variable $%grade% in the email draft wherever you’d like the grade data inserted for each student. Best of luck! Reply Craig says: November 17, 2015 at 4:08 am Wow! It’s that easy?!? Great! I’ll give it a try today! Thanks for your help. -Craig Reply

91.

Craig says: November 17, 2015 at 10:32 am It worked! Thanks so much! I guess I have to clear the “Mail Merge Status” each time I want to send out another email? -Craig Reply steffon says: November 17, 2015 at 2:02 pm Correct, clearing the “Mail Merge Status” between mailers let’s the script know that you’re ready to send another email to the same person. Thanks for letting me know it worked for you! Happy mail merging! Reply

92.

Arch says: November 23, 2015 at 6:58 pm Hi Steffon, Using this script, the names in the greeting are showing up italicized. How do I make the %First Name% part unitalicized? …Same problem as Max Greenhalgh. Thanks in advance! Reply steffon says: November 25, 2015 at 1:57 am Hi Arch – Thanks for the question. Unfortunately, I don’t have much to add to the Max Greenhalgh response. The variable should only look italicized if you explicitly set that formatting in the email template itself. I would try testing with a brand new email that has only the variable name and zero formatting to see if it keeps happening. The only other thing I can think of that might be causing it is if there is extra formatting in the spreadsheet itself. Try clearing that of all formatting too. I hope that helps! Reply

93.

lance says: December 4, 2015 at 11:19 am Hi Steffon. I love your tool. I stared using it two weeks ago – I have a mailing list of around 1000 people in my HOA which I use to let everyone know what’s going on because official communications are not too good in our local community. This is not spam – since I have been provided the email addresses and everyone can opt out. I have been doing 100 per day – so at least ten days for a cycle. I am prepared to pay (out of my own pocket) for a Google Apps account but it seems to force me to set up a domain name etc – and I don’t want to do this. I essentially want to just use your tool and my gmail address to send out more than 100 per day. Before this I was using bcc at a limit of 500 but Google has tightened up on bcc now. Am I missing something or do I need to go through a full Google “business” application. I see lots of stuff for “non profits” but since I am just an individual and not representing an “organization” this doesn’t fit either. Any advice would be appreciated Reply steffon says: December 23, 2015 at 10:09 pm Hi Lance – Thanks for the question. Unfortunately, if you’re hitting gmail send limit, the only ways (that I know of) to get around it is to either time your mailing at the end of your 24 hour period and send it in two parts (creating the opportunity to send 200) or to upgrade to Google Apps. And yeah, Google Apps requires getting a domain, which you can expect to pay ~$15/year for (honestly though, it’s not that hard. I run my custom domains through Google Apps). It may be worth trying the free tier of Mail Chimp for your newsletter needs as an alternative. Best of luck! Reply

94.

Meenal Sinha says: December 23, 2015 at 7:30 am Hi Steffon, Over the last year this script has proved extremely helpful for my business. I have recently started encountering a problem wherein the mails are going without the names. My draft has Dear $%First Name%, however when the mails go out it just says Dear at the top and hence the entire benefit of the personalised message goes away. Any ideas on how to fix the problem. Cheers M Reply steffon says: December 23, 2015 at 10:17 pm Hi M – Thanks for the question and I’m glad to hear the script has proven useful! I just ran the script and it worked correctly for me so it should still work correctly for you. Did anything change with your setup? If nothing seems to work for you, try revoking the script’s privileges, deleting it and the spreadsheet, and “installing” it fresh; hopefully that will get it working again. Best of luck! Reply Meenal Sinha says: February 7, 2016 at 5:40 am Hi Steffon, Many thanks for the suggestion but nothing seems to work. Created a new email, typed the variable instead of a first name, copied the spreadsheet from the tutorial, typed in the email ids for test purpose instead of copying an excel and tried multiple times. but no luck. My emails read ” Dear ,”. So for some reason the variable in First name is not getting picked up at all. Reply steffon says: February 11, 2016 at 11:09 am Hi Meenal – Hmmm, that’s frustrating. If you want, you can record a screencast (posting it to YouTube) of the steps you’re taking and I’ll take a look to see if I can spot the problem. Reply Meenal Sinha says: March 7, 2016 at 7:56 pm Sorry for late response. Was travelling for a bit. I guess I have inadvertently changes something in my gmail settings. Will try the screencast too if nothing else works. Many thanks

95.

Bec says: January 15, 2016 at 2:58 am Hi Steffon, I’m using this add on for the first time and I understand how to attach files to my emails (and individualised ones at that) but I have 190 emails to send so I was hoping there was a quick way for the app to figure out which attachment goes with which email (ie the file name includes the surname of the person being sent the email and are in the same order as the names in my spreadsheet) rather than me having to attach 190 pdfs separately. Any advice? Thanks, Bec Reply steffon says: January 18, 2016 at 1:16 pm Hi Bec – Thanks for the question. Unfortunately, this version of the script does not allow for customizing the attachments directly. But do not fear! What you can do is host your PDFs somewhere else (like Google Drive) and link to your PDFs inside the email. The script WILL allow you to customize links. In this way, you can make sure each recipient receives a customized PDF link. Best of luck! Reply

96.

Joey says: January 17, 2016 at 12:02 am Hey Steffon! Thank you for this great tool! I used it for the first time tonight and am in talks with two prospective new clients! As soon as I get my first sale I will send you some drink money to celebrate! My question is that I selected the email address related to this business I am promoting, but when the 91 emails went out, it defaulted to my personal and primary gmail account. Any thoughts on how to get it switched to the biz address? I know the email account works because I tested that first. Thanks! Reply Joey says: January 17, 2016 at 12:03 am Also just to clarify I did have the email I wanted it to be sent from selected in the draft email. Reply steffon says: January 18, 2016 at 1:11 pm Hi Joey – Congrats on drumming-up two prospects with the tool! Regarding utilizing the mass mailer with your biz address, here are two things to try: 1. Try executing the mass mailer script when you are only signed-in with your business account. An easy way to try this is by signing in via incognito mode. This will rule-out the possibility that the script is operating with your personal email address. 2. You may find that the script itself has not yet been authorized for your business account (which you’ll uncover by attempting to run the script after step 1). If that’s true, then you’ll need to authorize it for the biz account. Thanks for the question! Reply

97.

David says: February 18, 2016 at 4:45 am Great tool thanks, our IT department has authorised its use so its great to have the functionality of mail merge back. Is there a way to use alternative accounts? We have a number of shared mailboxes and ideally I would want to send from one of these rather than my mailbox so any replies I get go through to this account? Reply steffon says: April 8, 2016 at 10:38 am Hi David – Thanks for the note. If you can set the alternative account inside the draft message that you compose, that may be a way to accomplish this. Otherwise, I think you’d need to authorize the script for each account that you’d like to send it with and then login to that account to send it from that address. Best of luck! Reply

98.

BTR Naidu says: February 29, 2016 at 8:30 am You are really doing a great job. Specially when it comes to support. Impressed and keep it up. Reply steffon says: April 8, 2016 at 10:36 am Thanks for the note BTR! Reply

99.

Sjaco says: March 13, 2016 at 11:40 am Works perfectly, you are my hero! Thanks! Reply steffon says: March 13, 2016 at 7:35 pm Glad to hear it Sjaco! Reply

100.

Karl says: March 31, 2016 at 3:26 am Hi, I’m a Google Apps user and the spreadsheet was copied to Google Sheets. However when I go to Google Docs I don’t find the mail merge script. How do I launch it to send the mass email to my clients? Reply steffon says: April 8, 2016 at 10:34 am Hi Karl – Make sure you’re checking in google drive (vs. google docs) as you’ll need to locate the spreadsheet. You can launch the script from within the spreadsheet. Reply

101.

Diva Tommei says: April 8, 2016 at 8:34 am Hi there, I was wondering how I can make the space after the tag $%First Name& disappear, since I am putting a comma there. Currently the outcome is “Hi $%First Name%, I am writing this email…” which appears as “Hi Mark , I am writing this email…”. I don’t want the space after the name. I am wondering if it has to do with the space in the tag between First and Name? Thanks so much! Reply steffon says: April 8, 2016 at 10:33 am Hi Diva – Thanks for the question. I just re-ran the script and am not getting that space myself so hopefully there is just a space slipping in somewhere that you can remove. To aid in troubleshooting, first, I’d look at the cell values in the “First Name” column in your spreadsheet to make sure there are no spaces after the names themselves. For example, you’d want to see “Mark” not “Mark ” in the cells (the only way to check this is to click on a cell and see if there’s an extra, hidden space is there). Second, the space between “First and Name” in the variable wouldn’t cause this because it’s a variable name, so you can rule that out. My money is that the space is in the spreadsheet value. Best of luck! Reply Diva says: May 10, 2016 at 6:08 am Hi steffon, yes you are totally right thanks for replying. It was just a problem with a trim function that wasn’t working when I was generating the names. I have another question now, the counter for how many emails I can still send seems off. First, I noticed that if I bcc myself then that counts as -2 emails. Does Google actually count that as 2 emails? Second, the counter hasn’t reset between one day and the other so I am now down to 12 emails that I can send today but there was no resetting this morning. Is that right? Is there any way I can actually know how many emails I have left by asking gmail somehow? Reply steffon says: May 12, 2016 at 4:28 pm Hi Diva – The counter isn’t exact due to time zone complexity. It doesn’t know exactly when the next day starts and when to reset. As a result, use it more for guidance than an actual limitation. Best of luck! Reply

102.

Derek Drake says: April 27, 2016 at 11:52 am Hi Steffon, Great stuff. My initial testing worked great. I’m running into an issue with getting a verification if the email sent or not. The EMAIL_SENT message appears in the sheet, but out of 28 messages I sent, I only see 2 in my “Sent Messages” folder. Also, I have a filter established to forward any emails that are “from” me, including a specific batch of words I’m using in my subject line, to autoforward to SalesForce. And when I “BCC” myself it works. But again, of the 28 messages I sent, only 2 showed up in my inbox, and they were the same 2 that showed up in my “sent mail.” Thanks for you work and support of this! Reply Derek Drake says: April 27, 2016 at 11:56 am Correction… I found them in my Spam Folder! I’ve updated my filter to ensure they don’t go to spam. I’m leaving this up for anyone who may encounter this. Thanks again Steffon! Reply

103.

Laura says: May 16, 2016 at 10:21 am Hi Steffon, I was wondering if when you’re attaching a link to a Google document, is there a way for the document to show up as a whole in the email rathering than just providing a link to click on? Thanks, Laura Reply steffon says: May 17, 2016 at 2:46 am Hi Laura – As far as I know, the most you can do is link to a google document from within an email. Sounds like you’ve got it. Reply

104.

Mayank Kumar Bansal says: May 25, 2016 at 11:23 pm Hi Steffon, This is really a brilliant script and I am sure helping many people a lot! Great work! Just have some queries below: 1. Have experienced an issue before when you use images/ 4-5 urls in the mail body for mass mailing, Google blacklist that particular domain name or IP address. Please let me know if through this script, this issue of blacklisting by google apps or any other source will be there? 2. Will it be OK to send mass mailing (1500) per day from a company’s domain email id without getting blacklisted? Appreciate your help on this! TIA, Mayank Reply steffon says: May 27, 2016 at 5:10 pm Hi Mayank! Thanks for the kind words. I’ll try and answer your questions: 1. As far as I know, the content of you email shouldn’t cause Google to blacklist you. So adding URLs and images shouldn’t matter. As far as I know, what WILL blacklist you is if you send a lot of emails that bounce. That’s something spammers do. Also, if people mark you emails as spam. 2. Volume of mail shouldn’t effect blacklisting. Note: I’m not an expert in this area and it’s always changing. One thing you can do is always include yourself in a mailing (a different email address than what you’re sending from) and see if the email appears in the spam folder. If it does, then you know you’re in trouble and probably new a new sending email address. Best of luck! Reply

105.

Red Bernaldez says: June 15, 2016 at 3:09 am Hi, can you disable the auto self bcc? Or its a standard if you use the Mail Class or Gmail class? Reply steffon says: June 16, 2016 at 2:22 pm Hi Red – Thanks for the question. If, in your draft email, you have yourself BCC’d, then you should be BCC’d each time it sends. However, the script does not by default BCC yourself. Reply

106.

Rob Carter says: November 30, 2016 at 6:01 pm Hi Steffon, How do I add another variable to the spreadshee?. I am basketball official and I need to send ratings out to all of my officials. I created another column and called it $%2016_2017_Rating% and then gave a value to each official. When I did the merge the rating column is blank. Am I doing something wrong or is this template not built for this? The name and email worked great. Any help would be appreciated. Thanks Rob Reply steffon says: November 30, 2016 at 11:52 pm Hi Rob – Thanks for the question. Your approach seems sound. I would try simplifying the column name and see if that helps. For example, trying naming it just “rating” and see if that simplifies things. I add columns too and it works. It’s possible your column name is too “complex” and is goofing something up. Reply

Leave a Reply Your email address will not be published. Required fields are marked * Message

Name

*

Email

*

URL Submit

Recent Posts

What Made Google Great May Be Its Biggest Risk

Email Address Permutation Generator – Generate Email Addresses from a Name

Herd Investors Spotted: Visualization of Hedge Fund Herd Mentality

The Curalytics Clique

Monitor MySQL Processes on Amazon RDS Every Second

Recent Comments Andrea john: You have done everything possible Good job.… anuradha: This post is lovely!! Thank you for the detailed steps.… steffon: Hi Rob - Thanks for the question. Your approach seems sound.… Rob Carter: Hi Steffon, How do I add another variable to the spreadsh… steffon: Hi Simon. Yes, I am! And, starting today, it now comes with …

Categories Amazon Web Services (2) Curalytics (5) Curation (10) Excel (2) Finance (1) HFT (1) Leadership (3) Marketing (2) MySQL (1) Software Tricks (5) Startups (5) Strategy (5) VC (2) RT @mich8elwu: Do you really know the diff btwn a #Community & a #SocialNetwork? https://t.co/5KLGhGH3nB #socialMedia https://t.co/asy7TfHV… follow me Blog Curation About Copyright © 2017 Steffon Davis. All Rights Reserved.