We breathe cloud computing at Mailparser and are frequent users of all kinds of online applications. One of our most commonly asked questions is how to send email from Google Sheets. If you still need to extract the data from your emails and send it to Google Sheets, our app is an excellent fit for your use case. The same is true for those who wish to extract data locked inside their emails and create an Excel download on their device.
Today, I needed to send out a few personalized emails to a list of recipients stored in a CSV file. The usual way of doing this would probably be to pay for a newsletter software like Mailchimp and import the spreadsheet containing the recipients there. Not this time, though! I stumbled upon several easy (and free) ways to send out multiple emails directly from within a Google Sheet! This article is a brief walkthrough of my findings.
Why send emails from Google Sheets in the first place?
Before we get into the nuts and bolts of Google sheets, let’s clarify why you might want to use Google Sheets to send emails in the first place.
To illustrate an example, let’s use our team of cats. Did you know that Mailparser uses cats behind our email parsing automation tool? But, to keep our cats productive and happy, we need to feed them in mice. At least 5-6 mice per day for a nutritious diet.
So, we pay our cats in mice – the amount depends on how complicated the task is and their overall workplace performance. Feeding cats is a tedious and challenging task because they’re constantly meowing and climbing all over us until we feed them. To ease the pain, we created email automation. We add our cats to the Google Sheet: listing the name, email address (so THAT’S why cats walk on keyboards), and the accumulated number of mice.
By the way, if you use an accounting app such as Xero to monitor payments to your cats, you can automate the data import using Zapier and Mailparser. Read more here.
Now, we can’t spend our days grappling with mice, or we can automate the process by setting up a rule: that each cat must catch at least 3 mice before they can eat them. If they aren’t hungry, they can wait longer until they find 10 or so, but why wouldn’t they want their payment right away?
Cats aren’t exactly savvy with spreadsheets, so we need to email them when the mouse count reaches 3 mice. We need to build a script to send emails from Google Sheets.
Instantly Send Your Emails from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
How to send Email from Google Sheets
You probably already know Google Sheets. It gives you all the basic spreadsheet operations and feels a lot easier to use than the traditional Microsoft Excel. However, if you dig deeper, you’ll soon realize that Google Sheets is much more than a nice-looking tool for making lists and adding numbers. You can install one of the many add-ons that let you do more advanced things like removing duplicates, splitting names, or merging cell values.
Our favorite Google Sheets emailing add-on is
Yet Another Mail Merge (YAMM)
Using YAMM is really easy – All you need to do is compose a draft email in Gmail, and create a couple of header columns in your Sheet with the corresponding data you wish to send. Typically your Google Sheet consists of an email address, a name, and should you want other fields drawn into the email dynamically (City, State, Company Name), add those to the Google Sheet as well then map them in your Gmail draft you compose. Rather than fleshing this out here, I will provide you the link to the (free) add-on itself, where there is all the information you need and a video to introduce it. Check out YAMM here!
All said, creating your Google Sheet full of email user information can be significantly enhanced through using Mailparser, and piping data from incoming emails into your Google Sheet.
Our native integration allows you to parse emails to Google Sheets and extract tons of data points on each email so you can personalize the emails you send in YAMM. This is a superb option for an inexpensive and ridiculously personalized email campaign.
Send Email from Google Sheets Script
Today, I wanted to try out the scripting feature of Google Sheets. The scripting tool lets you perform tasks and automate your work in Google Sheets. For example, one of the tasks you can perform is sending out an email, which is precisely what we need!
To get started, we need to know how to open the script editor of Google Sheets and think about what our function needs to accomplish. Unlocking the code editor is a no-brainer; click Tools in the top navigation and click on Script Editor. As you can see, next to Script Editor is also a Script Gallery which is the add-ons store mentioned above.
For the action we want to perform, we need to follow these steps:
- Read the email addresses from the first column
- Read the recipient name from the second column
- Assemble the body text, including the first name
- Send out a personalized version of the email to each recipient
To make things even easier for us, Google published an article about precisely this use case on its developer blog. We took their sample as the basis of our script.
Script to send Emails From Google Spreadsheet
As written above, we want to read the content of the first column and use it as the recipient address. Then, we want to take the value of the second column and use it as the first name in the text body of the emails. So, this is what our data looks like:
So, how does the function look like? Have a look at the code snippet below. If you read carefully the comments inside the source code, things should become understandable even if you don’t have much coding experience.
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Start at second row because the first row contains the data labels
var numRows = 3; // Put in here the number of rows you want to process
// Fetch the range of cells A2:B4
// Column A = Email Address, Column B = First Name
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0]; // First column of selected data
var message = “Hey ” + row[1] + “,\n how are you doing today?”; // Assemble the body text
var subject = “Sending emails from a Spreadsheet”;
MailApp.sendEmail(emailAddress, subject, message);
}
}
Testing and executing your Script
Once you move the code into the Google Script Editor and adjust it to your needs, you should have something like this in front of you:
As you can see, there is a little Play button in the top bar next to an icon that looks like an insect. This little unimposing play button is the one you need to press to get the script executed.
Before you do this, however, please consider testing all of this on a small data set. It is important (in my opinion) to proofread an email at least once inside an actual email client. What worked for me was replacing the values of the first column with my email addresses and sending a bunch of emails to myself first.
Please note: The first time you click the Play button, Google asks you for permission to send out emails. This is perfectly normal as your script tries to access a protected function (MailApp.sendEmail).
Send Email from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
If I can just use a script to automate emails, why should I use Mailparser?
With Mailparser, you can save time by fully automating your email parsing process. What Mailparser does is simple:
Forward your emails to our email parser specifically emails with data trapped in their body or attachments
We (mailparser.io) extract all specified data fields based on your custom parsing rule.
Then, we automatically send the data to your favorite applications. We offer many integrations with popular apps.
Our email parser grabs essential data from recurring emails based on the custom parsing rules you set. Once new emails are processed, we transfer your data to where it belongs–the applications that are already a part of your workflow. Send your data to Salesforce or other integrations. You can also customize your integrations based on webhooks or download parsed email data to Excel, CSV, JSON, and XML.
Watch our video on how easy it is to start our services:
What’s more?
We offer 1,500+ integrations with your favorite apps through Zapier.
We offer data parsing for file attachments. Process any text stored inside email attachments, then store it as usable data. (Not every parser provides this functionality!).
You can use webhooks to send important notifications to Slack automatically.
But, don’t just take our word. Read what our customers say:
Testimonials
I have to tell you mailparser.io is great. I found you while trying out another program to help us parse information out of our leads into ZohoCRM and let me tell you it works great. I receive leads from several lead sources and with mailparser.io it saves me at least 2 hours a day. I can log in and look at every email that it parsed and review it and if there was an error I can fix it and have it resend to my CRM. It was easy to set up and there is documentation that covers most issues. Your support staff is great. Thanks for the quick response time to my questions and for helping me out to make sure my transition went smooth. To sum up, it was fast, easy and affordable. I look forward to a continuing relationship with you.
John Ruff – discountforkliftbrokers.com/
We have been using Mailparser for about a year to extract email addresses that we receive from download forms on our website, gillsc.com. The emails are automatically sent over to Mailparser and categorised into the appropriate category through a unique email address supplied by Mailparser. The data is then extracted from Mailparser and then sent over via a webhook integration to Zapier, which then processes and sorts the data and delivers to Mailchimp. Mailchimp then sends out an automated email campaign.
Prior to using Mailparser we had to sort the emails manually and then add them to MailChimp; this would be very time-consuming. With the help of Mailparser, the process is now completely automated and takes no time at all.
Michelle – Gill Sensors & Controls
Our business focuses on helping doctors, hospitals, medical clinics and health providers in their collections and administrative tasks with patients and insurance companies. With mailparser.io we are able to parse emails, extract and take action using different webhooks that will allow our clients to receive their information filtered by what’s relevant or not. This allows our clients to focus on growing their business without the need of a robust administrative staff.
We are very happy using mailparser.io, it’s easy to use and very reliable.
Jerónimo – MEDIFIN
So how did that work out for me?
Splendidly! The code block above is what I used today for sending out a couple of emails to a list of recipients in Google Sheets. As mentioned above, the code is based on a tutorial written by Google for this use case. It took me only a couple of minutes to adjust the code shown above to my needs. On top of that, it saved me some bucks as I didn’t need to subscribe to a paid newsletter software for this simple one-time emailing job.
Please let me know if this worked out well for you in the comment section! Also, maybe you could do other valuable things with the Script Feature of Google Sheets?
Easily Send Emails from Google Sheets
Easily manage your emails with our automated email extraction tool.
Try it free. No credit card required.
