Send Multiple Emails From Google Sheets Using GAS

Share this

November 28, 2018

Send Multiple Emails From Google Sheets Using GAS


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.

New Empty Spreadsheet

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.

Rename Sheet

Step 4: Add the headers to the sheet so as to look like the image below. I think that the headers are self-explanatory.

Adding the Headers In The Spreadsheet

Step 5: Switch to the Script Editor by selecting the Tools category from the menu and then clicking on the Script editor option.

Open The Script Editor

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.

Save The Project

Step 8: In the Edit Project Name form that will pop-up, enter a name and click the OK button.

Editing The Project Name

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.

Additional Menu

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.

Filling The Rows With Information

Step 11: Then, select the Custom Code category from the menu and click on the Send Emails option.

Send Emails Option

Step 12: In the Authorization Required form that pop-ups, click on the Continue button.

Authorization Required Form

Step 13: In the Sign-in form that pop-ups, click on your Google account.

Sign In Form

Step 14: Finally, in the next form that appears, click the Allow button.

Allow Access To Google Account

If all the previous steps performed successfully, you will probably see something like this:

Emails Were Sent Successfully

And here are the emails that were sent (in the inbox of the recipient):

Emails Received

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

Page last modified: March 2, 2020

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>