In this article, we will show you how to automate emails in Google Sheets so that emails are automatically sent by the use of a button or an extension. If you simply want to send an email from Google Sheets please read this article instead.
Most automation on Google Sheets requires basic knowledge of its Script Editor, Apps Script. We’ll cover a simple guide for setting up Apps Script here but a more comprehensive guide for it can be found in the link above.
Automate Sending Emails with a button in Google Sheets
Imagine you have a list of multiple customers and you have to create multiple customized emails for each of them. Doing this for a long database will take you a while to finish. Using Google Sheets makes this easier and here’s a simple guide for you to follow to automatically send them.
1. Input recipient details in Google Sheets
We will put recipient details in Sheet 1, including the receiver name, mailing address, and other details you want to customize in your text body. Remember to separate each in a new column.
2. Create a Message Template in a new tab
In Sheet 2, put the message template with our variable content enclosed in angle brackets <>. When changing the set-up of the sheet, note that you will need to change the set-up of the syntax.
3. Open Apps Script by going to Extensions -> Apps Script in the file menu
4. Input the following code in the editor:
function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet1');
var sheet2=ss.getSheetByName('Sheet2');
var subject = sheet2.getRange(2,1).getValue();
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,2).getValue();
var name=sheet1.getRange(i,1).getValue();
var ServiceAcquired=sheet1.getRange(i,3).getValue();
var message = sheet2.getRange(2,2).getValue();
message=message.replace("<name>",name).replace("<service>",ServiceAcquired);
MailApp.sendEmail(emailAddress, subject, message);
}
}
The bolded words are variables that can be adjusted based on the inputs of your code. Say that instead of Service Acquired, you want to put Ordered Items or Quantity Bought, simply edit the syntax of your script. Your code should look like this.
5. Name your project as “sendEmail” and click Save project
6. Click Run code
7. After pressing run code, permissions must be set up to access the data.
To set this up, click Review Permissions. Since this is a personally customized script, a warning will appear. Go to Advanced Settings -> Click “Go to Send Email” and allow the permissions. This will run your program and email your recipients.
You will see that it will be sent with a customized text in the message body for each customer.
8. To automate your script, add a button for the function in Google Sheets.
You can insert an image, clipart, or drawing of a button to your sheet to act as a Google Sheets button to run the script.
9. Select your image and press the three dots on the top right corner of the image. Select “Assign Script” and insert the name of your script function, sendEmail.
You can use steps 5-6 to automate any new script you want. This can be used in setting up and automating more complex emails with varying uses. Once you’ve set up your code, you can assign it to a button. Clicking the button will trigger the action to run.
Automate Sending Emails on a Recurring Schedule
We’ll be using the Email Spreadsheets Add-on for Google Sheets to use your spreadsheet data and email them on specified schedules so that you will never have to worry about being late again. A premium upgrade will allow you to access certain features but there is a free trial and below is a guide on how to use it. You can also schedule with other plugins or mail merge applications.
1. Install the add-on from the Google add-on store.
2. Go to your spreadsheet and press “Extensions” -> “Email Spreadsheets” -> “Open App”
3. In the add-on select “Create A Workflow” and this will open your export and email editing options.
4. In Part 1, you can adjust which sheet/s in your workbook you will be using and whether to attach them as PDF, Excel, or CSV file formats.
5. In the next part, you can customize the PDF Settings such as paper sizes, margins, and other formatting options.
6. In the Email Editor, input the details of recipients, subject, and body message.
Use the dynamic cell values with the use of the notation {{Sheet1!A1}} for specific cell values and {{Sheet1!A1:C2}} for ranges within the message body. You can also use these dynamic cells for the recipient details and the subject. The content of these dynamic cells will change to the current cell input at the time indicated.
You can forward to multiple people but remember to separate your recipients in the TO, CC, or BCC fields with a comma.
7. Schedule your recurring message.
Select the date and time when the email should be sent and when the schedule should stop. You can also specify dates to exclude from the repetition.
8. Save your workflow.
Saving the workflow will schedule your email regularly. If you have to edit your workflow, just go to “Extensions” -> “Email Spreadsheets” -> “Open App” then select your workflow and choose from the actions available.
And those are the several ways to make sending emails easier for work or personal use. Having the power to do these with multiple emails in scheduled occurrences with Google Sheets will allow you to save a lot of time and effort.
Recommended articles
More resources
