Introduction
Have you ever wondered how is it possible to send multiple emails to different recipients, without counting on external services, like MailChimp, AWeber, Mailerlite, and others? If the answer to this question is yes, then you would probably be surprised to learn that you can achieve this by simply using Google Sheets and some custom code written in Apps Script.
In this post, we will assume that you already have the email addresses to which you want to send emails. In another post, we will see how to retrieve your Gmail contacts in a Google Sheets spreadsheet. Warning: the process below was not written to make you a spammer! The fair usage of the script that follows is up to you!
The process
The entire process involves 14 steps. However, once you set up the spreadsheet, then it is as easy as clicking a button on the menu bar.
Step 1: Following the instructions described here (steps 1 to 4) create a new empty spreadsheet on your Google Drive.
Step 2: Open the new spreadsheet in Google Sheets.
Step 3: Rename the first sheet to Emails by double-clicking upon the Sheet1 name and entering the suggested name (Emails). Of course, you can enter another name, if you want. You just have to be careful to put the same name in the code as well.
Step 4: Add the headers to the sheet so as to look like the image below. I think that the headers are self-explanatory.
Step 5: Switch to the Script Editor by selecting the Tools category from the menu and then clicking on the Script editor option.
Step 6: In the Script Editor, paste the code that you will find below.
Step 7: Click the Save button or select the File category from the menu and then click on the Save option. Alternatively, you can use the CTRL – S shortcut.
Step 8: In the Edit Project Name form that will pop-up, enter a name and click the OK button.
Step 9: Switch back to the spreadsheet and refresh the page (note that when you refresh the spreadsheet page, the script editor’s page will automatically close). You will see a new option on the menu called Custom Code.
Step 10: Fill the rows below the headers with the appropriate information (email address, email subject, and email main message). The other four columns (D through G) are optional and can be used for customizing, even more, your emails.
Step 11: Then, select the Custom Code category from the menu and click on the Send Emails option.
Step 12: In the Authorization Required form that pop-ups, click on the Continue button.
Step 13: In the Sign-in form that pop-ups, click on your Google account.
Step 14: Finally, in the next form that appears, click the Allow button.
If all the previous steps performed successfully, you will probably see something like this:
And here are the emails that were sent (in the inbox of the recipient):
Bonus Tip: To create a line break within a cell that contains your main message, simply use CTRL + ENTER, instead of just ENTER.
GAS code for sending multiple emails
Here is the GAS code that loops from row 3 till the last row and automatically sends the emails to the appropriate recipients using the sendEmail method of the Gmail service. Read the code comments for more information.
// Start of the GAS code.
/*
----------------------------------------------------
Written By: Christos Samaras
Date: 28/11/2018
Last Updated: 08/12/2018
E-mail: [email protected]
Site: https://myengineeringworld.net
----------------------------------------------------
*/
function onOpen(e)
{
/*
------------------------------------------
Creates a custom menu at the spreadsheet.
------------------------------------------
*/
SpreadsheetApp.getUi()
.createMenu('Custom Code')
.addItem('Send Emails', 'sendMultipleEmails')
.addToUi();
}
function sendMultipleEmails()
{
/*
-------------------------------
Sends multiple emails at once.
-------------------------------
*/
// Ge the sheet containing the data by its name.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails");
// The name you want to be displayed in the recipient's inbox.
var displayedName = "Name Surname"
// Check that the sheet object is not null (i.e. the sheet name is correct).
if(sheet == null)
{
Browser.msgBox("Invalid sheet name!");
return;
}
// Set the first row that containing the data (after headings).
var startRow = 3;
// Get the last row containing data (in column A).
var lastRow = sheet.getRange("A1:A").getValues().filter(String).length;
// Check that the last row is greater than the start row.
if(lastRow < startRow)
{
Browser.msgBox("There are no emails to send!");
return;
}
// Loop through all the rows and send the emails.
for (var i = startRow; i <= lastRow; i++)
{
// Get the email address.
var address = sheet.getRange(i, 1).getValue();
// Get the email subject.
var subject = sheet.getRange(i, 2).getValue();
// Check if the welcome message (i.e. the optional parameter) is not empty.
if(sheet.getRange(i, 4).getValue() != "")
{
// Create the custom message with the names, along with the welcome and the goodbye message.
var message = sheet.getRange(i, 4).getValue() + " " + sheet.getRange(i, 5).getValue() + ",\n\n"; // Welcome greeting.
message += sheet.getRange(i, 3).getValue() + "\n\n"; // Main message.
message += sheet.getRange(i, 6).getValue() + "\n" + sheet.getRange(i, 7).getValue(); // Goodbye greeting.
}
else
{
// This is the standard message (no optional parameters are provided).
var message = sheet.getRange(i, 3).getValue();
}
// Send the email using the Gmail service (typical parameters).
// GmailApp.sendEmail(address, subject, message);
// To include your name, call the Gmail service like this:
GmailApp.sendEmail(address, subject, message, {"name": displayedName});
// Other options include bcc, cc, attachments, noReply, replyTo etc.
// See here for more information:
// https://developers.google.com/apps-script/reference/gmail/gmail-app
// An alternative method that has some issues, is this:
// MailApp.sendEmail(address, subject, message);
}
// Inform the user about the process.
Browser.msgBox(((lastRow - startRow + 1) == 0 ? "An email was" : (lastRow - startRow + 1) + " emails were") + " successfully sent!");
}
// End of the GAS code.
Read also
GAS Code To Get Email Information Into Google Sheets
Page last updated: 22/06/2019
