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 data from a spreadsheet of hundreds of names and emails and customize each message.
Step 3: Copy this mail merge spreadsheet into Google Docs
Click the link above and then click “yes, make a copy”. This will copy the spreadsheet template and associated script into google docs. If you want to peruse the code before copying, check it out here.
Step 4: Copy your mailing list into the spreadsheet
There are lots of ways you could copy your mailing list into the spreadsheet. This is one of them. Open the spreadsheet you just copied. Notice that there is a column for First Name and a column for Email Address. We will go into gmail’s contacts to export the first names and emails from our mailing list (if you know of a script that will import them directly, please message me in the comments).
In Gmail, click on the Gmail link with a little red arrow in the upper left corner and select Contacts.
In Contacts, select the users you would like to mail. I spent time creating lists of users so I can select my target list easily (like family).
Export the list to Outlook CSV format.
Open the list in Excel (or google spreadsheets) and delete all columns except the first name and email address.
Copy the two columns into the mail merge spreadsheet in Google Docs
Step 5: Start the Mass Mailing
– In Google Docs, go to the Mail Merge menu and select Start Mail Merge. Grant it authorization. Observe that it still has not sent the mailing (kind of confusing). All this did was grant the authorization. You have to click the button again for it to work.
– Click on Start Mail Merge again to open the mailing options.
Step 6: Select your email template
From the drop down menu, select the draft you composed in Gmail.
Step 7: Write in your name
The form asks you to “Please write the sender’s full name”. This will appear as the name next to your email address in the email header.
Step 8: Start the mailing!
Click Start Mail Merge and the spreadsheet will print a confirmation after each email sends. Note: Gmail caps the number of emails you can send in a day to 500 (UPDATE: as it turns out, Google limits the number of emails you can send with scripts. It’s limited to 100 per day for the free gmail, the formerly free version of Google apps for domains, and it’s 1,500 per day for the paid version of google apps). Business accounts can send 2000 1500 if you have Google Apps.
Did These Instructions Work For You? Buy Me a Drink!
If these instructions worked for you (or one of the questions below helped you trouble shoot) feel free to express your gratitude by buying me a drink (powered by PayPal)! I’ll even link back to your business (unless you’d like to stay anonymous):
Total Drinks So Far (updated 9.12.16)
$1.00 from Sergio: Thanks for the coke!
$5.00 from Paul: Thanks for the draft beer!
Potential Problem (and Solution) By Adding Images: The ‘Error encountered: Cannot call method “copyBlog” of undefined.’ Error.
When you add an image to your email, do you get the error ‘Error encountered: Cannot call method “copyBlog” of undefined.’? To solve this, remove the image from your draft and then add it back in. The process of removing it and then adding the exact same image back makes the error go away.
Error, be gone!
Potential Gotcha With Images: Error encountered: Cannot read property “1” from null
Some people are reporting the error ‘Error encountered: Cannot read property “1” from null.’ in the comments. It seems to be related to placing images inside the body of your email. If you get this error, try removing any images and then sending again and see if the error goes away. Not everyone gets this error, and I’m not sure at this point what causes it. For example, I’ve sent several mailers with inline images and never received the error myself, which makes diagnosing it further almost impossible. If you have insight on this, feel free to share in the comments.
Update as of 9.8.2015
Michela reported in the comments that including the image in the email signature can prevent this error. It’s another option to try if you’re getting this error. Thanks for sharing the tip Michela!
Unauthenticate The Spreadsheet And Revoke It’s Access
This guide requires providing a spreadsheet access to your Gmail Account. If you want to unautheticate and revoke this spreadsheet’s access, follow these steps:
- Click this link and go directly to the account page that let’s you manage apps connected to your account. Once there, click on the “MailMergeHD – Digital Inspiration” app and click “REMOVE”.
- If the link provided didn’t take you to the “Apps connected to your account screen” then follow these steps:
- Login to Google
- Click on your “head” icon in the top right. Click “My Account”.
- Click “Sign-in & Security”
- Click “Connected apps & sites”
- Click “Manage Apps”
- Click “MailMergeHD – Digital Inspiration” app and click “REMOVE”.
Expanding functionality
For anyone interested in expanding the functionality of this script, two often requested new features include:
1. Recording in the spreadsheet when an email is opened.
2. Send individualized attachments.
3. Setting a delay between each email sent.
Sending Huge Campaigns
If you’re sending out huge mailing campaigns, think about trying a managed service like ConstantContact or MailChimp. If you sign-up for one of these services using these links here, I get a little affiliate marketing bonus, which is much appreciated (you know, if you end up signing-up for them anyway).
Provide Feedback
Feel free to ask questions in the comments if you’re having trouble or provide a thanks if this worked for you. If you liked it, please follow me on twitter or throw some coins into my PayPal powered tip jar!
Thanks so much – what would personally take this over the top for me is if you could write the script that would include a BCC to a specific email address other than my own. So when I tick the BCC box, I could enter a Salesforce email address, or any other email address needing a copy. Huge thanks for the tutorial!
Hi Eric – Thank you for the note! I am very glad you found my tutorial helpful. Regarding your question, there may be a straightforward solution. The script in the tutorial sends a unique email to each email address on the list. So, if you have 20 people on the list, it sends 20 separate emails. As a result, it is safe to manually add, for example, a Salesforce email address to the end of your spreadsheet of emails because the script will send a unique email to that address too; because the script sends a separate email to each address, no one sees anyone else’s address.
While the script includes the option to BCC yourself, the fact that it calls it “BCC” is largely unnecessary: it’s just going to send you a separate email anyway, so who cares if you’re BCC.
If this doesn’t answer your question, just let me know!
Hi Steffon,
Thank you so much for this – it is of amazing help. I had a quick question that I really hoped you could assist with.
I would ideally like to send the mail from my Gmail using our company alias. E.g. Ed@company.co.uk – which we currently run through my Gmail account. Is there anyway to do this?
Thank you in advance.
Hello Edward! Thank you for the question. Interestingly, I just received the exact same question and replied to it one comment earlier. Please check-out my post to Nick right above. Essentially, I recommend checking out this resource for the fix: http://inevitableimprovement.com/email/using-mail-merge-in-gmail-with-an-alias
Many thanks! Superhelpful! I was not a coder myself until today. Finally I can cancel all my CRM subscriptions.
Thanks for the note Steve! I’m glad to hear my walk-through helped.
Dear Steffon,
I like this mail merge script but I would like to send these email on a specific date not today. Kindly help me to schedule these email.
Regards
Yogini Kulkarni
Hello Yogini – Currently, the script does not support sending emails on a timer. However, that would be an interesting modification to make!
Thanks – but in my case, I would use a BCC as Salesforce connects the email address of the recipient to its contact name in the CRM. If I just send an email to the salesforce address (as it sounds like it would do with your suggestion), it wouldn’t attach to any record at all – just sit there in “unresolved.”
So it can’t send an email to two addresses at the same time, eh? Maybe I can auto-forward my BCCs…hmmm… thanks again for your help!
You are correct: the script needs modification to accomplish this. Although, if you want a quick and dirty solution, you could create a filter in Gmail to auto-forward your sent emails to the Salesforce address. With that filter in place, each email sent will also get forwarded to Salesforce. You could add the filter just for purposes of the mailing (and then delete it afterwards), or, put in a special keyword or symbol in the filter so it only engages when you want an auto-forward to Salesforce. Best of luck! And if you or someone else builds on the script, let me know.
Quick Question?
If I have a mass mailing of 1200 and my limit is 700. Will I be able to go back to my gmail merge spreadsheet and send the last 500 if I just do Step 3 tomorrow?
Your steps were very helpful!
Hello Jenny: Thank you for the note. I’m glad the steps are helpful! Google’s sending limit page says that the, “Limits per day are applied over a rolling 24-hour period (rather than a set time of day)” (https://support.google.com/a/answer/166852?hl=en). So to answer your question, yes, you should be able to send the last 500 tomorrow, just keeping in mind that tomorrow could be, at worst, 24 hours from when you send the first 700. But I would double-check that your limit is actually 700. On the same support page, Google says the limit for education and business users is 2000. Best of luck!
I do not want it to send the email but put it into draft. That way I can check each one for quality and a few customized tweaks before sending it out. Can I make the messages saved into the draft folder?
Hello Mahesh, and thanks for the question. To make the script send all the emails to the draft folder instead of delivering them immediately, you would need to alter the script itself. Outside of altering the script, you may want to try adding an additional variable to your mail merge that allows you to include a customized message, for example, a unique 1st introduction paragraph to each email. And while this would let you tweak each email, it would not meet your desire to proof-read each one before sending it out. However, each email should be largely identical since they are based on the same template. Good luck with your mailing!
Thanks! I am not that familiar with JavaScript but with my programming background I think I can figure this out.
To send it to draft, I think the line that needs to be replaced is GmailApp.sendEmail
I looked at https://developers.google.com/apps-script/reference/gmail/gmail-app but could not find anything to save a message into draft.
Line to replace:
GmailApp.sendEmail(line[mail_idx], subjectCopy, bodyCopy, {
htmlBody: bodyCopy,
name: name,
attachments: attachments
})
What is
Sorry, did not complete my message.
Would you know the method to call on GmailApp for saving to draft?
Thanks for providing the link to the gmail-app script documentation. I looked it up and down but unfortunately, I could also not find a method related to creating drafts. When using gmail in the web browser, interestingly, a draft is created whenever an email is in “compose” mode, but has not yet been sent. Because the gmail-app script does not seem to have methods related to composing emails (the “body” of an email is just another variable) it looks like the draft method is also not present. However, it may be worth investigating what happens when you use sendEmail and leave the “recipient” variable blank. This may force gmail to create a draft since it can’t send. I realize this isn’t exactly what you’re trying to do, but it may be a launching-off point for experimenting with hacks. Best of luck!
ear Steffon,
First off, thank you for such a great script that allows mail merge in Gmail. What a great tool!
I am using this script with an email tracking tool called Bananatag (https://bananatag.com/), and somehow when I use your script to send email, Bananatag does not track those emails sent via your script. Can you let me know how I can get the 2 to work together?
Thank you so much Steffon!
Jimmy
Hello Jimmy! I’m very glad the tutorial works well for you; thanks for the note. Regarding your question, I researched Bananatag and developed some approaches to arrive at a solution. Bananatag, like most email tracking services, likely inserts a 1x1px transparent image into each email. When this image loads, Bananatag can tell the email has been opened. For this to work correctly on their system, each email needs a unique tag. As a result, for Bananatag to work correctly with the mass emailer script, each email will need to be assigned its own unique tag. To do this, you would likely need programmatic access to their system. Fortunately, they seem to have a robust API: http://www.bananatag.com/api/. It may be possible to use this API to begin programmatically including tags to each email. It may be worth posting this request in the Bananatag knowledge base: http://blog.bananatag.com/knowledgebase/ . Unless there is a way to insert your own tags manually, and then assign your own unique variables to this tag via the spreadsheet and script, then the API will likely need to be used. It may be worth reaching out to their team to see if this is functionality they want to develop. It would be in their best interest to enable customers to easily add thousands of tags.
Are you able to draft merge emails with a link to a google doc survey document without issue?
Hello Trish! Thank you for the question. Yes, you can draft an email with links to anything, including a google doc survey. When you draft your email, you compose it in Gmail itself. So anything you can do in gmail to compose an email will be sent in the mass mailer, including links in the draft.
Hi Steffon! I actually use this product and it’s been very helpful, but I’m wondering why I get locked out of mail merge after 100 emails. I have a Google Enterprise account so it shouldn’t be an issue. Any suggestions? TIA! Sarina
Hello Sarina! Thank you for the question. Can you describe what you’re seeing when you are “locked out” of mail merge after the 100 emails? You are correct that Google Enterprise should not limit you to 100 emails (it has a 2000 daily email limit documented here: https://support.google.com/a/answer/166852?hl=en). To address you question, I need to better understand what you mean by getting “locked out” of mail merge. If it’s hard to describe, feel free to take a screencast with, for example, http://www.screencast-o-matic.com/, and reply back with a link to a youtube video that shows the “locking out” happening.
Whenever this happens I receive a message that says: “Service invoked too many times for one day.” I thought I had up to 500 but the mail merge stops working after 100. I’ve seen others with the problem online, but haven’t been able to find a solution. Thanks for the quick reply!
Hello Sarina – Thank you for posting the error message. I researched it and found the likely source of the problem. As documented here (https://developers.google.com/apps-script/guides/services/quotas) Google has a unique set of limits for all script driven activity. As it turns out, the quota for “email recipients” is one of these script driven limitations. However, the limits are different depending on which account type you have. If you are using the free gmail.com, the email recipient limit is 100/day. If you are using the free Google Apps For Your Domain, the limit is also 100/day (This is the version of Google Apps that was free to businesses for a long time, and continues to be free today even though you can’t sign-up for it anymore). The limit for Google Apps, the paid version, is 1,500/day.
My guess is that you are using either the free gmail.com version or the free Google Apps for Your Domain version and are hitting the 100/day script sending limit. Unfortunately, I’m not sure if there is a solution to this problem without paying for Google Apps. Thank you for bringing up this limitation as it’s a serious caveat. If anyone comes up with a work-around, feel free to post. it’s worth noting that Google says the quotas are subject to change: maybe it will get lifted in time.
Hi Steffon,
Thank you so much for the tool, but it no longer seems to work for me. When I try to run the mail merge, I get error message “cannot call method “copyBlob” of undefined.” Please help!
Hello Eric – Thank for writing in! I’m glad the tool has been useful, that is, until you started getting the “cannot call method “copyBlob” of undefined” error. Now, the first thing I did was check the script to see if it works for me: and it does. So, the fundamentals of the script are still OK. My guess is that you are including an image inside the email itself and Google has, yet again, changed their policies for putting inline images in emails. I did a little research on the error and it seems like other people have the same hypothesis: http://help.ctrlq.org/54-mail-merge-error-copyblob-of-undefined . So, the first thing to try would be to send the email without the image (kind of lame). The second think to try would be to not upload the image directly to the email body itself, but make it available elsewhere online (like imagr) 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.
Thanks! It was the image…
Steffon–Any hints on how to format numbers and dates so that they maintain that formatting in the merged email? For instance, when I have a merge field that is currency amount, say $1,000.25, the text that appears in the merged email is 1000.25. And when I have a merge filed that is a simple date, say 12/22/2013, the text that appears in the merged email is Sun Dec 22 2013 00:00:00 GMT-0600 (CST). Any suggestions would be very much appreciated.
Hello Mark! Thank you for your question; I had not noticed this formatting issue before. I can suggest two work-arounds. The first is easy, but is also a compromise. If you put quotes around your value in the cell, for example, “$1,000.25”, the formatting will be retained in the emails and it will look exactly like “$1,000.25”. The downside is that the quotes will be there too! The second way is harder, but gives you complete control over the formatting. You can add any character you want into your cells with CHAR() values. So, for example, if you want to put $1,000 into a cell, you can use =CHAR(36)&1&CHAR(44)&0&0&0 . The first value CHAR(36) displays the “$” sign. The “&” symbol links the next value which is “1”. The CHAR(44) displays the “,” symbol and so on until you have explicitly defined each character in your cell to “spell” out $1,000. This will guarantee that the formatting translates exactly how you want but is obviously more tedious. For a complete list of ASCII character codes that Google supports look here: http://anandexcels.wordpress.com/2012/04/25/ascii-character-set-in-google-docs/ . If you want to learn more about making strings with these codes, I’ve written about the topic here: http://steffondavis.com/manipulate-strings-with-excel-add-characters/ . Best of luck!
Actually, if you just set the formatting in the field in google docs to “Plain Text” and type $1,000.25 it will pull the correct formatting.
Great info Michael! Thank you for sharing. I’ve tested this and confirmed that setting the cell format to “Plain Text” works.
After days of searching the internet for this solution i am elated to have found it and to know it’s a really easy fix.. but what i am impressed with more is how you respond to each question posted.. that is awesome. Keep up the good work dude. Wish you much success.
Hi Fabian – Thank you for the kind words! I’m glad this solution worked for you. Best of luck with your mailings.
When I do the “Plain Text” option it defaults to 1/23/15 where I would like it to read “January 23, 2015″ is there a way to set it to that without all the ” 00:00:00 GMT-0600 (CST)” at the end of it?
Hi Suze – Thanks for the question. Would you mind asking it again with more specifics? I’m not sure exactly where or why you’re entering date information. Thanks!
I am sending out emails regarding hotel confirmations and I want to have the date of arrival listed in the body of the email, but it is reading “Wed Jan 28 2015 00:00:00 GMT+0530 (IST)” when I would like it to simply state “Wed Jan 28”. And when I set it to Plain Text it doesn’t write out the day, it simply says “1/28/15” but I would prefer it say “Wed, Jan 28”. Hopefully that makes sense!
Hi Suze – Try formatting the spreadsheet cell containing the date as “plain text”. Whatever you enter as plain text will be brought into the email as-is. It seems like the cell may be formatted as a “date” and, as a result, introduces artifacts into the email. Let me know how that goes.
i am facing below error, please help
Error encountered: Service invoked too many times for one day: email.
Hello Shreyas! It looks like you are hitting the 100-email-per-24-hour-period limit: see here https://code.google.com/p/google-apps-script-issues/issues/detail?id=3625 and previous answers on regarding hitting the limit. If you don’t think this is true, please provide more details on how you get the error. Thanks!
Hi Steffon,
Thanks for your prompt response.
Yes, that’s very true. I am trying to send more than 100-email-per-24-hour-period.
But in my business i have got to send around 500 personalized emails every day to my clients, vendors, customers etc….
So could you please suggest me some way out of this or any other similar script using which i can do this?
in my gmail account if i try to send emails one by one, then it allows me to send 500 emails per day.
but sending emails one by one manually is really very very time and energy consuming stuff and i want to get rid of that.
so i would appreciate if you can help me in this.
Thanks in advance.
Shreyas
Hello Shreyas – I responded to a similar question for Sarina earlier, but I’ll repost the key information here: Google creates unique limits for email sent by a script: (https://developers.google.com/apps-script/guides/services/quotas). It’s lower than the number of emails you can send individually. If you have the paid Google Apps the limit is 1,500/day. If you don’t have the paid version, it’s 100/day. If you’re hitting the 100 email limit, it’s likely you’re not sending email on the paid version of google apps and the best solution would be to see if there is an account you can access that has the 1,500/day limit.
Thanks for the info.
Steffon – thanks for the great info. I’m trying to add dynamic words that are hyperlinked – is there a way to do this? For example, I’d like to include three example clients that are links to a case study about each client (eg: “you could use this like Nike, Adidas, and Reebok did”, where each company name would link to a URL showing a case study). These example clients vary based on the email we’re sending, so ideally I’d like to have a column in the mail merge gdoc that lists the three companies and URLs. I hope that makes sense!
Hello Justin! Thank you for the question. To turn a word into a hyperlink (as opposed to seeing the entire hyperlink in the email like http://nike.com) you need to insert the link HTML code directly into your spreadsheet. But don’t worry, it’s easy enough. Just copy this format
You’re the man! Thanks so much for the quick response, Steffon!
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.
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.
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.
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!
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.
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!
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.
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.
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 non-standard 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
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 dan@drdanweaver.com) 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.
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!
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:
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!
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!
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.
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.
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.
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.
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!!!
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.
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.
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/
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
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 (my.name@gmail.com) or adding a “+” sign (myname+word@gmail.com). Adding aliases in Google Apps requires logging into the admin dashboard and adding them manually. For example, your primary email address may be “name@domain.com”, but you’ve created aliases like “info@domain.com” and “contact@domain.com” 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, “info@domain.com”, even though your primary email address is “name@domain.com”. Can you specify which is the case?
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!
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?
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.
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.
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.
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.
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
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!]
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
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!
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!
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.
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,
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.
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
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.
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!
Thanks Coach Aileen! I appreciate the note. Happy mail merging.
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
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!
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!
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!
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
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!
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!
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
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.
Pingback: QUERY - Google Script for Gmail
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
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
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
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.
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
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!
Hi – Does this tutorial work for you to stop receiving updates from comments? http://en.support.wordpress.com/following-comments/
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
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?
Hello Eirik – Have you tried removing images from the email? See if it works without images first.
Yes, that fixed it! How come?
It works like a charm now, but only without inserted pictures. 🙂
Thanks!!
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?
Hi Mark – Thanks for the question. Do you always get the error, or is it only when you try to add pictures?
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 ?
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.
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.
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.
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.
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-remove-maestro-bounces-google-com-from-email-sender
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.
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.
Hi, is it possible to set the time when email will be send?
Hi Bernardas – No, setting the email on a timer is not currently built into the script.
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)?
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/
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?
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.
You are a God.
Hi Joe – love your enthusiasm for mass mailers!
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
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!
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
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.
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??????
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.
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.
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.
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?
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.Correction! You can add a second person in the same address field. So, for example, in the spreadsheet if you put the email address, “bob@gmail.com, sue@gmail.com” (without the quotes) then the script will send it to both email addresses. Kind of cool. Hope that helps!
Brilliant Steffon!!!
This is just a Awesome find, I can save lot of my time now! Thanks a Lot man!
-Vivek Kubal, India
Thanks for the note Vivek. I appreciate the positive feedback and I’m glad to help!
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
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.
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-web-apps/mail-merge
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.
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
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!
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.
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!
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
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.
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.
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.
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.
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.
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!
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.
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.
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.’
Hi Panduka – Thanks for reporting the error. That one is tricky…
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.
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!
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.
hi, thank you for being so cool 🙂
can i send individualized attachments?
thank you so much
Hello Steve – I added this feature request to the list of possible ways this script could be improved. Thanks for the note.
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
Hi – I added this feature request to the list of possible ways this script could be improved. Thanks for the note.
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!
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?
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
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!
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
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.
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.
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
Hey Steffon,
Thanks heaps for your post. Just wondering, if I could personalize the PDF attachment as well ?
Thanks,
Nitesh.N
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!
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!
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.
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.
Awesome, I’m glad it worked for you!
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 you@yourdomain.com 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.
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.
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?
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.
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 🙂
Hi Quintin – Thanks for the note, I appreciate it! Glad you’re finding it useful.
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
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.
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?
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.
Thank steffon, really helped
Thanks for the note Wale! I’m glad the post helped 🙂
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.
🙂
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.
I’m done using MailMerge.
How can I remove access to my account? Thanks! Great tool
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!
Hi steffon- im getting this error “cannot call method “mailbob”of undefined”
please help!
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!
Dear Steffon- That’s the exact error, it appears when i hit the send button. Thanks In Advance!!
Hi Steffon,
Is there a possibility to include a delay of let’s say 1 minute between each email sent?
Hi Rajesh: That’s a great idea. I just added it to the list of suggestions for “expanded functionality” in the post.
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
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!
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
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!
Wow! It’s that easy?!? Great! I’ll give it a try today!
Thanks for your help.
-Craig
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
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!
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!
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!
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
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!
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
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!
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. 🙁
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.
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
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
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!
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!
Also just to clarify I did have the email I wanted it to be sent from selected in the draft email. 🙂
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!
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?
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!
You are really doing a great job. Specially when it comes to support. Impressed and keep it up.
Thanks for the note BTR!
Works perfectly, you are my hero!
Thanks!
Glad to hear it Sjaco!
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?
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.
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!
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!
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?
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!
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!
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!
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
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.
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
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!
Hi, can you disable the auto self bcc? Or its a standard if you use the Mail Class or Gmail class?
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.
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
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.