Tutorial: How to Automatically Send Emails From Google Sheets
With Gmail’s mail merge feature, you can send mass emails by uploading a CSV file - such as one created in Google Sheets - containing your recipients’ names and email addresses.
But the feature stops short of letting you customize your emails with other content. For example, it can’t insert personalized greetings for each recipient. As a result, it isn’t the most useful solution for mass-sending marketing messages or holiday greetings, for example.
But when you add Make into the equation, this obstacle disappears.
Adding Make will level up your emails
Make can automatically populate emails with data from a Google Sheet, then mass-send these emails via the Gmail app, with a single click of a button. Subject to our email provider’s daily sending limits, there’s no cap on the number of emails Make can help us send.
It can also mark these emails as “Sent” in a Google Sheet, so we know we’ve already emailed these recipients.
Make’s free plan is perfect for trying out email automation. So, sign up for an account here and follow along as we cover how to send emails from Google Sheets, step by step.
1. Create a Google Sheet and add your email data
First, create a new Google Sheet and set up column headings for the email data you want to populate your emails with.
At the very least, there should be a data column for your recipients’ email addresses. But if you want to go further, you can also have columns for:
Your recipients’ first names.
A few simple sentences to personalize the email for each recipient.
After that, add a last column with the heading “Email status”. We’ll use this column to keep track of whether each recipient has successfully been sent an email.
Once you’ve added your column headings, fill out the first row with test data, such as your first name and your email address.
We’ll use this data to test the scenario, and make sure it works, before using it to email our recipients - so we can catch anything in case it goes wrong.
So, your Google Sheet should look something like this:
2. Create a Make scenario and add the “Search Rows in Google Sheets” module
Now, log into Make and create a new scenario.
Add the Google Sheets > Search Rows module to the scenario and connect your Google account to it.
We’ll then set up the module like this so it can search for the data in our Google Sheet:
Enter a Spreadsheet ID and Sheet Name: Leave this setting as Select from My Drive.
Spreadsheet: Select the Google Sheet you created earlier.
Sheet Name: Select the specific sheet in your Google Sheet that contains your email data.
Table contains headers: Leave this setting as Yes.
Column range: Set this to A-Z to search for all data in columns A to Z of your Google Sheet. It doesn’t matter if you aren’t going to fill out all these columns with data. But if your Google Sheet’s columns extend past Z to include columns AA, AB, and so on, you’ll need to adjust the Column range setting accordingly.
Leave the rest of the module’s settings blank. The settings should look like this:
Click OK to finish configuring the module.
3. Add the “Send an Email in Gmail” module
Next, add the Gmail > Send an Email module to the scenario and connect your Google account to it.
We’ll use this module to send emails containing our Google Sheet data to our recipients. But feel free to replace Gmail with a different email app like Outlook and tweak the module setup process to match.
If you’re using Gmail, here’s how to set up the module:
To: Click Add a recipient to add an Email address 1 field setting. Map the “Email address” data item from the Google Sheets module here.
Subject: Fill out your email’s subject, mapping any data items you want to personalize the subject with. For example, your subject could be “Best wishes, [“First name” data item]!”
Content: Fill out your email’s contents. You can use HTML tags to format your email. Likewise, map any data items you want to personalize your email content with here.
Optional: Enable the Show advanced settings switch to customize your email’s “From” address and CC and BCC recipients.
See this image for reference:
Click OK to finish configuring the module.
4. Add a filter between the “Search Rows in Google Sheets” and “Send an Email in Gmail” modules
Now, we’ll prevent Make from sending emails to recipients we’ve already emailed previously.
We’ll do so by clicking the dotted line between the Google Sheets > Search Rows and Gmail > Send an Email modules, which will add a filter between them.
Label the filter with a descriptive name like “Email not sent to recipient yet.”
Map the Google Sheet module’s “Email status” data item to the condition’s first operator and change the Text operators: Equal to setting to Text operators: Not equal to.
Finally, type “Sent” into the condition’s second operator.
Like so:
This way, we are allowing the scenario to proceed with sending an email only if the email status for the Google Sheet row doesn’t say “Sent.”
Click OK to finish configuring the module.
5. Add the “Update Row in Google Sheets” module
After Make has emailed our recipients, we want it to update our Google Sheet’s “Email status” column with the word “Sent.”
So, we’ll add the Google Sheets > Update a Row module next. Connect this module to your Google account like you did in Step 2 earlier.
Leave the Choose a Method dropdown setting as Select by path, then use the Choose a Drive, Spreadsheet ID, and Sheet Name settings to select your Google Sheet - and the specific sheet in it that contains your email data.
Map the “Row number” data item from the first Google Sheets module to the Row number setting. This helps Make update the Google Sheet row whose data it has just used to send an email.
Leave the Table contains headings dropdown setting as Yes, then type “Sent” into the Values > Email status field.
View this image for reference:
Click OK to finish configuring the module.
6. Add a filter between the “Send an Email in Gmail” and “Update a Row in Google Sheets” modules
Make should update the Google Sheet row’s “Email status” column with “Sent” only if it has successfully sent the email. So, we’ll add a filter between the Gmail > Send an Email and Google Sheets > Update a Row modules to do just that.
Click the dotted line between the two modules to launch the Set up a filter window.
Give the filter a descriptive label like “If email sent successfully.” Map the “Message ID” data item from the Gmail module to the condition’s first operator and change the Text operators: Equal to setting to Basic operators: Exists.
Check this image for reference:
With this filter setting, Make will mark a Google Sheet row’s email status as “Sent” only if the Gmail module had created a message ID. Which it will have, if it had successfully sent the email.
Click OK to finish configuring the module - and the entire scenario, too!
The scenario should look like this:
7. Test and run the scenario
Click the Run once button at the bottom left of the scenario editor to run your scenario.
If everything is working correctly, you should receive an email to the test email address you’d entered into the Google Sheet:
The “Email status” for that Google Sheet row should have also been updated to say “Sent.”
You can now replace your Google Sheet’s test data with real data, and click Run once again to mass-email your recipients.
The scenario will use the “Email status” column to check whether it has already sent an email to a certain recipient, so you don’t have to delete recipients from your Google Sheet once you’ve emailed them.
However, it may still be a good idea to do so to keep your Google Sheet clean.
Bonus: Auto-send email from Excel
It’s also possible to use this same scenario to send email from Excel automatically!
Instead of adding our email data to a Google Sheet, we’ll add it to an Excel file first. After that, we’ll import this file into Google Sheets to get our data into the latter app.
The rest of the steps to automate email from Excel are the same as those from Step 2 of this tutorial onward.
But whether you decide to automatically send emails from Google Sheets or Excel, you’ll need a Make account. So, sign up for one here, and experience how the platform can help make mass-emailing effortless.
