Codegena
send personalized html emails using google appscript

Send/Mail Merge HTML emails using Google AppScripts

HTML emails are cool! A well-designed HTML message can quickly convey ideas than a regular one. Today almost all businesses rely on email marketing services to improve their service. MailChimp is the big name that most of you might have heard of. What you may not know is that you could send HTML emails for free using your Google account. Sure you could do that with several plugins but this guide will walk you through on writing Google AppScripts to send, automate or mailmerge HTML emails.

If you haven’t heard about Google AppScripts before, stay right here because you will be amazed about what it can do. Even though we have only shared the Gmail Auto Forwarder script, I have been using AppScripts for a while and I must admit that I love using it. You can reach me through the contact form for customized Google AppScripts. Enough of that, let’s get started!

Writing an HTML email is different from writing a webpage

Before discussing how to send HTML emails it’s quite important to know the basic differences in writing a webpage vs an email template. Let me get this straight – it’s hard. Eventhough it’s 2019 most email providers don’t support the latest and greatest HTML tags although many are catching up. You can check the supported tags using this website – caniemail.

The main difference in writing an HTML email template is that you will be using tables a lot as CSS support is very minimal. You will also be required to follow many other practices such as using inline CSS styles and more. There are several other guidelines to follow when designing an HTML email but it’s beyond the scope of this article so I recommend you read these guidelines by MailChimp and SitePoint.

Online Email Designer Tools

With the rise of email marketing, there are several online email designer tools, the most popular being Mailchimp and Designmodo’s PostCard. Many of these services offer to export the email template and for this tutorial, we will be using beefree.io, an email designer tool that has a good collection of free email templates as well.

For this tutorial, I have designed a simple party invitation email with BeeFree and exported it as a zip file. Extract the zip and you will find the HTML file and images folder. It is required to upload these images elsewhere and replace the relative links in the HTML with the absolute ones. You can use any free image hosting services such as imagebb or Imgur.

At the end you should have an HTML file with no local dependencies. All relative links must be replaced with absolute ones.

Send an HTML email with Google AppScript

The following code is all you require to send an HTML email to your friend. To run this code, head over to script.google.com and create a new one. Paste the following code into the editor. Now create the email template by clicking File > New > HTML file. Make sure that the HTML file name matches the name we provide in the code.

  
  var htmlEmailBody = HtmlService.createTemplateFromFile('html-template-name');
  
  var subject = "Welcome to Google AppScripts";
  var toAddress = "shaneapen@gmail.com";
  var normalBody = "This is the normal plaintext version of the html email";
 
    GmailApp.sendEmail(toAddress, subject, normalBody, {
      name: "Shan Eapen Koshy",
      htmlBody : htmlEmailBody.evaluate().getContent()
    });
    
  }

You can also check out BeeFree’s free Gmail add-on to directly send HTML emails from your Gmail interface.

Using Google Sheets and AppScript to send personalised HTML Emails (Mail Merge)

In the above code, we saw how to send an HTML email to your friend using Google AppScript and that’s fun. Now think of a scenario where you could automate birthday wishes, send personalized email invitations, etc, that’s where mail-merge comes into play. Mail-merge is used to send personalized emails to a group of people. Now let’s see how you can send personalized HTML emails using Google Sheets and AppScript.

  1. Go to sheets.google.com and create a new blank sheet
  2. Populate the sheet with your data such that each type of data goes in each column. For example, first name in column A, last name in column B, email address in column C and so on.
  3. Now go to Tools > Script Editor and use the code below.
  4. Create a new HTML file by navigating to File > New > HTML file.
  5. Paste the HTML code we have created earlier and replace the variable fields such as the name with printing scriptlets. For example, replace the "Hello Sachin" in the HTML file with "Hello <?= name ?>"
  6. Next, we need to write a script to loop through each member in the sheet and replace the scriptlets with the actual text. Find out how it’s done in the code below.
  7. Select the appropriate function from the drop down and click the play button to run the script. When running the script for first time, you will be asked to review the permissions and you may also see the “App not verified” error. Bypass that and run the script to start sending your emails.
function bulkSendEmail() {
  
  //StartRow and EndRow indicates the boundary
  var startRow = 2, endRow = 4;
  var loopLimit = endRow - (startRow-1);
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var names = sheet.getRange("A" + startRow + ":A"+ endRow).getValues();
  var emails = sheet.getRange("B" + startRow + ":B"+ endRow).getValues();
  
  //creates html template from the file `email.html`
  var htmlEmailBody = HtmlService.createTemplateFromFile('email');

  var subject = "Shan's Party Invitation";
  
  //Will be displayed incase HTML couldn't be rendered. 
  var plaintext = "We request you to attend the party at XYZ convention center on 29th September 2019."; 

  for (var i = 0;i < loopLimit;++i){
    
    // replacing the scriptlet `<?= name >`  with the actual name.
    htmlEmailBody.name = names[i];
    
    GmailApp.sendEmail(emails[i], subject, plaintext, {
      htmlBody : htmlEmailBody.evaluate().getContent(),
    });
    
  }

}

AppScript Quota

While this is all free, Gmail does impose some quota limits. You can only send 100 emails/day with your regular account and a maximum of 1500 emails/day for GSuite account holders. View the entire quota limits here.

HTML Email Best Practices/Tips

  1. Use table layout instead of CSS
  2. Use inline CSS to style HTML elements
  3. Include plaintext version when sending HTML email to avoid being classified as spam
  4. Use Google Analytics to track your email conversion

If you have any queries leave a comment below.

Shan Eapen Koshy

Shan Eapen Koshy is a web developer, technical blogger and a YouTuber. He has created several web applications and has written many app scripts.