The full code follows this post.

This page will cover the following topics:

• Using Google Script

• Automate sending emails with Gmail

• Get information from a Google Sheet to populate an HTML file

• Attach documents from Google Drive to your email.

The Background...

At my company, we do marketing emails from time to time. 

There are 2 problems with the marketing emails we send. 

One: The emails are sent out in plain text. Boring!
This is a marketing email. It should stand out. It should catch the attention of the reader.
It should NOT be BORING!
Marketing Emails should also include branding. The company logo and colors should be prevalent in the email.
We want readers to know this email is coming from OUR company, not just some random text garbage.

Two: The emails are being sent in bulk using BCC.
This in and of itself is not a problem, but it looks lazy and sloppy.
When I receive an email that just has my name in a BCC field, I get the feeling that the email is not worth my time.

Currently, we do not have a system to create, manage, and send HTML emails. I think larger firms, with actual marketing departments, use enterprise software like HubSpot to manage this type of bulk email creation. But, we don't. So, is there an alternative? Is there a FREE way to do this that you already have included in a service you are already using?

The answer is YES. YES. A thousand times YES!

I'm not going to go into the HTML set up here in this post, but I will cover how the different functions in Google Script work and how they replace variables in the Template HTML and can also be used to personalize the email.

So, let's get started.......

Getting Set Up

To start, you are going to need a few things.
The good news is that you probably already have them and if you don't, they are free.

If you don't already have a Gmail account (Google Account), go make one.
It's ok. I will wait for you. Take your time. I'll be here when you get back...

This can be done by looking for Gmail or Google Account in any search engine. Even Duck Duck Go.

If you still need more help, you can click here.

""A few hours later""

GREAT! Good Job! You did it!

Included with your new Google Account a wide range of Google Apps! These apps are great because they are all interconnected.
That means that you can get information from one app and use it in another.

Want to get emails from a list of customers in a SpreadSheet? No problem. Automate it!
Want to send attachments with those emails? No problem. Automate that too!

Basically, by using the Google Suite of Apps, you can combine their power to your advantage, improving the quality of your workflow, and speeding things up.

Getting your Gmail Account Info

To send an email, you need an email address. This email address is used in the email we are sending as a return address.
The code that I have below uses the alias that my work Gmail account uses, but if your account does not have aliases, you will need to change the code.

// Constants for session. Use email alias for logged in user.

const me = Session.getActiveUser().getEmail();

// Constand for Alias. If your Gmail account does not use aliases, you do not need this line.

// getAliases will return a list of aliases used by the account. 

// if not needed, please comment out.

const myAlias = GmailApp.getAliases();

The next thing that we need is a Spread Sheet with all the information that we will be using in our automation.

In the Google Sheets App create a new document. 

At the top of the new document page, you will have a URL.
Inside the URL will be the ID for the Spread Sheet that we will be using in our automation.

In the code, you will need to replace the constant ss with your spread sheet ID.

REMEMBER! The ID needs to be a STRING. So, don't forget to put those awesome quotes around your ID.

// Constants for Google Sheet with Email information and HTML Filler info.

// need to change the ID for correct Google Sheet

const ss = 'ID OF GOOGLE SHEET YOU WILL USE'

You can name the sheet what ever you want. It won't effect the ID. The ID is constant and unique for your spread sheet.

Inside the sheet, you will need to two sheets. You can name them whatever you like, but I have chosen to use the following names:

• emails - which holds the customer email informaton

• content - which holds the content that will be replaced in the HTML doc.

const emailSheet = SpreadsheetApp.openById(ss).getSheetByName("emails");

const content = SpreadsheetApp.openById(ss).getSheetByName("content");

Remember, if you choose to use different names for your sheets, you need to update the strings above.

It is also possible to get the sheets by number, but if you have someone rearrange the sheet order, your script won't work.

Setting up your emails.

This list doesn't need to be complicated.
I have just made a list of emails, however, there is the option to personalize the emails with information below if you include variable in your HTML

Optional Variables are as follows:

•  #LAST - The person's last name

•  #FIRST - The person's first name

•  #COMPANY - The company the person works at

•  #ROLE - The position the person holds at the company

 Setting up your HTML Email Content

The basic set up is something like this: emailMessage.replace('#TITLE', arr2[1][1]);

We are taking the information from the emailMessage and replacing the variables with text from the spread sheet.
The spread sheet range function returns an indexed array starting at 0,0. So, you will need to change the array index to the approiate location in your spread sheet. 

NOTE: This is an indexed array. Everything starts at 0. So, cell A1 is 0,0 not 1,1. In the array, the first number is the row, the second number is the column. So, A1 is 0,0 and A2 is 1,0, C35 is 34,2

The variables I have in the script is as follows.

• #EMAILTITLE - Used for the Title of the Email 

• #TITLE - Title of the Event

• #SUBTITLE - Subtitle of the event

• #THEME - Theme of the event

• #TITLE - title shows twice in my HTML, so I needed to add it twice to the script

• #SUBTITLE - same as above

• #DESCRIPTION - Description of the event

• #PDFPASS - Email includes a password protected PDF, so this is the password

• #DATETIME - date and time of the event.

• #DEADLINE - cut off date for registration

The following is information for the person who will be presenting.

• #PRESENTER_COMPANY

• #PRESENTER_DEPARTMENT

• #PRESENTER_TITLE

• #PRESENTER_NAME

Location information for the Event:

• #LOCATION

• #LOCATION_ADDRESS

Registration Form:

• #FORM

PDF Attachment from Google Drive ID

• #PDF

• #PDFPASS

• #ATTENDEE - Limit of attendees

• #EXTRA - Any additional information that might need to get added.

// Constants for session. Use email alias for logged in user.

const me = Session.getActiveUser().getEmail();

// Constand for Alias. If your Gmail account does not use aliases, you do not need this line.

// getAliases will return a list of aliases used by the account. 

// if not needed, please comment out.

const myAlias = GmailApp.getAliases();

// Constants for Google Sheet with Email information and HTML Filler info.

// need to change the ID for correct Google Sheet

const ss = 'ID OF GOOGLE SHEET YOU WILL USE'

const emailSheet = SpreadsheetApp.openById(ss).getSheetByName("emails");

const content = SpreadsheetApp.openById(ss).getSheetByName("content");

// Function that sends emails to all emails in Google Sheet.

function bulkEmails() {

  var sheet = emailSheet;

  // Get content from Google Sheet content

  var contents = content.getRange(1,1, content.getLastRow(), content.getLastColumn());

  var email_body = contents.getValues();

  // Logger.log(email_body);

  var range = sheet.getRange(1,1, sheet.getLastRow(), sheet.getLastColumn());

  var value = range.getValues();

  // returns the first value from the Aliases list.

  var alias = myAlias[0];

  // Go through email information on Google Sheet emails

  for (i = 1; i < value.length; i++){

    sendHTMLTemp(value[i], email_body, i, alias);

  }

  // Logger.log("email body content: " + email_body[0][1]);

}

// Function that sends

function sendHTMLTemp(arr1, arr2, x, alias){

  var emailMessage = HtmlService.createHtmlOutputFromFile('email').getContent();

  // Parse through spreadsheet and replace items.

  emailMessage = emailMessage.replace('#TITLE', arr2[1][1]);

  emailMessage = emailMessage.replace('#SUBTITLE', arr2[2][1]);

  emailMessage = emailMessage.replace('#THEME', arr2[3][1]);

  emailMessage = emailMessage.replace('#TITLE', arr2[1][1]);

  emailMessage = emailMessage.replace('#SUBTITLE', arr2[2][1]);

  emailMessage = emailMessage.replace('#DESCRIPTION', arr2[5][1]);

  emailMessage = emailMessage.replace('#PDFPASS', arr2[17][1]);

  emailMessage = emailMessage.replace('#DATETIME', arr2[6][1]);

  emailMessage = emailMessage.replace('#DEADLINE', arr2[7][1]);

  emailMessage = emailMessage.replace('#PRESENTER_COMPANY', arr2[9][1]);

  emailMessage = emailMessage.replace('#PRESENTER_DEPARTMENT', arr2[10][1]);

  emailMessage = emailMessage.replace('#PRESENTER_TITLE', arr2[11][1]);

  emailMessage = emailMessage.replace('#PRESENTER_NAME', arr2[12][1]);

  emailMessage = emailMessage.replace('#LOCATION', arr2[13][1]);

  emailMessage = emailMessage.replace('#LOCATION_ADDRESS', arr2[14][1]);

  emailMessage = emailMessage.replace('#FORM', arr2[15][1]);

  emailMessage = emailMessage.replace('#PDF', arr2[16][1]);

  emailMessage = emailMessage.replace('#PDFPASS', arr2[17][1]);

  emailMessage = emailMessage.replace('#ATTENDEE', arr2[18][1]);

  emailMessage = emailMessage.replace('#EXTRA', arr2[19][1])

  // Use info from excel to "Personalize" the emails.

  emailMessage = emailMessage.replace('#LAST', arr1[0]);

  emailMessage = emailMessage.replace('#FIRST', arr1[1]+"様");

  emailMessage = emailMessage.replace('#COMPANY', arr1[4]);

  emailMessage = emailMessage.replace('#ROLE', arr1[5]);

  // Send Emails with the Gmail API

  // Check to see if there is content in the FILE section of the sheet

  // Change Range to correct location for your sheet

  if (content.getRange(17,2).getValue() !== ""){

    const file = DriveApp.getFileById(content.getRange(17,2).getValue());

    GmailApp.sendEmail(arr1[2], arr2[0][1], '', {

      'from': alias,

      'htmlBody': emailMessage,

      'replyTo': "marketing@sjdc.co.jp",

      'attachments' : [file.getAs(file.getMimeType())],

    });

  }else{

    GmailApp.sendEmail(arr1[2], arr2[0][1], '', {

      // Please comment out alias if you do not have an alias.

      'from': alias,

      'htmlBody': emailMessage,

      'replyTo': "marketing@sjdc.co.jp",

      });  

  }