Send emails via app script with varying names in the HTML body

Question

I've gotten myself hopelessly lost in this one.

I had this working with just information from one spreadsheet to the script, but I wanted to format the email with my email's signature and that lead to making the whole email HTML instead. (I understand there's some kind of API for that but I couldn't get to work.)

Unfortunately, if I send out a mass email with this code through gmail, it threads everything together into one massive conversation and I need them to be individual emails.

I am hoping that I can get the Name variable into the HTML text so that every email is different and maybe it won't thread itself together.

This is the apps script

function getEmailHtml() {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var emailData=ss.getSheetByName('Emails');
var subject = emailData.getRange(2,1).getValue(); //subject line
var n=emailData.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = emailData.getRange(i,3).getValue(); // recipient's email
var name = emailData.getRange(i,2).getValue(); // recipient's name

var htmlBody = getEmailHtml();

  MailApp.sendEmail({
    cc: "[email protected]", //cc manager
    to: emailAddress,
    subject: emailsubject,
    htmlBody: htmlBody 
  });
}

}

The (mostly relevant) HTML

<div dir="ltr">
    <div style="color:#222222;font-family:Arial,Helvetica,sans-serif;font-size:small;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:#ffffff;text-decoration-style:initial;text-decoration-color:initial">Hi there  !</div>

I would like to have the person's name be placed after "Hi there" There's more to the email after that but this is the only spot that needs the name.

I've tried scouring the various posts about pushing variables to HTML but I've gotten frustrated enough with this mess that I admit defeat.

liquidkat liquidkat · Accepted Answer · 2022-01-29T04:33:06

Codes:

function send_emails() {
  //get the data from your spreadsheet
  const ss = SpreadsheetApp.openById("datasheetid").getSheetByName("datasheetname").getDataRange().getValues();
  //get the html content from file.
  const testmessagebody = HtmlService.createHtmlOutputFromFile('yourhtmlfile').getContent();
  //this will iterate all of your values. You can filter some data out, or pick the data you need.
    for (let i in ss) {
      const name = returnedrow[i][1];
      const email = returnedrow[i][2];
      //replace the placeholder on your spreadsheet.
      const email_body = testmessagebody.replace("{name}", name)
                                        .replace("{job_title}", email)
      // Sending Email
      GmailApp.sendEmail(
        email,
        "subject",
        email_body, {
        htmlBody: email_body,
        name: "name you need"
      });
    }
  }

Simple HTML Template

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
    <table >
        <tbody>
            <tr>
                <td>
                    Name:</td>
                <td>
                    {name}</td>
            </tr>
            <tr>
                <td>
                    Job Title:</td>
                <td>
                    {job_title}</td>
            </tr>
        </tbody>
    </table>
</body>
</html>

Send emails via app script with varying names in the HTML body

2 Answers