How to Send Customized Bulk Emails without Mail Merge Add-ons

Sending bulk emails in one go is significant if you have many customers or friends. If you have some business or you are promoting something, you may required to send the same type of email responses or promotions every day to many people . Typing each email one by one for every user would be time consuming.  As well as Typing many email IDs in recipients section while composing email is also not good practice. Whenever you will send email with many recipients in to section, every user will able to see all mentioned email address to whom you sent that email. The Best way is to send customized bulk emails in one go.Send Customized Bulk Emails

But if you don’t want to invest any money and your mailing list is not too long, Google App script is the best option for sending bulk email. You can also always send customized bulk emails easily without using Mail Merge Add-ons. There are already many mail merge Add-ons available. Some are free and some are paid. To use them you will have to grant permission to those Add-ons for your account. But what if you can write your own code and send bulk emails without using third party mail merge Add-ons.

Benefits of Sending Bulk Emails without using Add-ons :

  • First, you don’t need to share your data with some other app.
  • Second, you can customize your emails according to your requirements. Even you can add different attachments to each email within size limit set by Google.

You can always customize your emails for every person, even when you are sending them in bulk. Moreover, This way you can avoid spamming look. Running script in Google Spreadsheets is great for doing so. With just few lines of code you can import your contacts to spreadsheet and send them personalized bulk emails without using mail merge Add-ons.

There are limitations set by Google on sending emails per account on daily basis. Google allows sending emails to 100 recipients in 24 hour period for Gmail consumer.

How to Send Customized Bulk Emails through App Script

For sending bulk emails we will use spreadsheet and will write script to send emails. For that we require contacts list in spreadsheet and email draft in Gmail. After that, We will use contact list stored in spreadsheet and draft saved in Gmail to send emails.

Steps to Send Customized Bulk Emails

List Contacts in Spreadsheet :

Login to your Google account and open Google Drive.

Click on New button at top and choose Sheet> Blank Spreadsheet.Create New SpreadSheet

Now as you are required to create mailing list in Spreadsheet. You can do so by either typing contacts one by one in spreadsheet or import them using script. Here are both methods.

Listing Contacts in Spreadsheet manually :

First of all, we will create Header. So,Type FullName, EmailId In first row columns like showed in image.Import Google Contacts

Now type  Name and Email Addresses in other rows.

Importing Contacts to Spreadsheet through Google App Script :

You can automate the importing contacts from Google contacts Groups through script. Here we are going to import contacts to spreadsheet from Google contact group already created in Google.Open Script Editor

Read How to create Google contacts Group.

Go through Import contacts through Script.

Create Personalized Draft in Gmail :

Next step is, creating draft in Gmail. Later in this article, we will fetch the content of created draft, in script for sending email. As we are going to send customize Emails per user, we will use some variables in Gmail draft. Later, Variables can be replaced by user information while sending email.

In this article we will use “$%FullName%” to replace it by user’s full name in each email. Wherever you will use “$%FullName%” anywhere in email draft, it will be replaced by user’s Full name to whom email is being sent. Example of Draft is shown in image.Customized Email Draft

Open Gmail Account and click on compose and type customized message using variable as mentioned above and save it in draft.

Script for Sending Bulk Emails from Spreadsheet –

In created spreadsheet as mentioned above, Open Script editor by navigating to Tools> Script Editor. Type code for sending bulk email and run the script.

function sendEmails() 
    var sheet = SpreadsheetApp.getActiveSheet(); // Get Active sheet
    var startRow = 2; // First row of data to process
    var numRows = sheet.getMaxRows()-1; // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, 3)
   //******get gmail draft message***********/
    var draft = GmailApp.getDrafts()[0]; // The first draft message in the drafts folder
    var message = draft.getMessage();
    var messageBody= message.getBody();

    var data = dataRange.getValues();
    for (i in data) 
        var row = data[i];
        if(row[1]!="")                      // making sure Emailid not empty
           var emailAddress = row[1];       // Second column of spreadsheet
           var emailSent= row[2];
           var fullname=row[0];
//***********Replacing name to customize email ***********/
           var message=messageBody.replace("$%First Name%", fullname);
           var subject = "Sending emails from a Spreadsheet";
           if (emailSent != EMAIL_SENT) // making sure email not sent already
                                to: emailAddress,
                                htmlBody: message,
              var cell= dataRange.getCell(parseInt(i)+1, 3);
              var x=cell.getValue();
               // Make sure the cell is updated right away in case the script is interrupted

Run the above script.

Understanding the Code To Send Customized Bulk Emails:

  1. In First line of code, we assigned EMAIL_SENT variable to string “Email_Sent”. This is for keeping track on sent emails . Later in Code, it will update Third Column in each row to string “Email_Sent”,  after sending email to that recipient.
  2. GmailApp.getDrafts()[0] will fetch first draft that you created recently in Gmail.   draft.getMessage(); will fetch the message of draft.
  3.  In code, For Loop is being used for going though all rows and fetch their respective column’s value for sending email.
  4. For sending customized mail, replace functionmessageBody.replace("$%First Name%", fullname); is being used. If it finds variable “$%First Name%” in message body, that will be replace to user’s full-name which is stored in spreadsheet and fetched in variable ” fullname”. In this way email can be customized per user. To send customized bulk emails according to you, you can add more variables in email draft and replace them in your script
  5. MailApp.sendEmail function will send the email to current user in loop.
  6. cell.setValue(EMAIL_SENT); will update third column to string “Email_sent” to keep track of sent emails.

Sharing is caring!


Skip to comment form

    • Monica Buchanan on August 27, 2018 at 7:25 pm
    • Reply

    This is so great. How do I add to the code to email a customer and CC their administrative assistant?

    1. Here is the solution. I hope it will help you.

      In Contacts spreadsheet add third column for CC and enter comma separated email ids for each row.
      Then in code add this line below var fullname=row[0]; while you are extracting values from spreadsheet.

      var CC=row[3];

      Then while you are sending email, replace the code as below:

      to: emailAddress,
      htmlBody: message,
      cc: CC


    • JC on January 14, 2019 at 2:58 pm
    • Reply

    If I want to add a column for Salutation. How do I replace it before the fullname? Thank you.

    1. First of all add a column for Salutation in spreadsheet. Next while drafting email you can fetch salutation values from spreadsheet by putting $%Salutation% in email.

Leave a Reply