automated email sending - variable in Htmlbody

3.2k views Asked by At

I'am very new in gas. The scenario is this : Having a spreadsheet with the list of recipient and their names, I want to automate and customize the body of the email writing on the top "Dear {name}". How can I pass the variable into the Htmlbody option?

Here is part of the code... Thanks in advance.

var ss = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxx").getSheets()[0];
  var rng = ss.getRange(2, 1, ss.getLastRow()-1, 8);
  var rngvls = rng.getValues();
  //
  for (var i = 0 ; i<rngvls.length; i++){
    var name= ss.getRange(i + 2, 5, 1, 1).getValue();
    GmailApp.sendEmail(dest, ogg, body,{name:nam, attachments: [fail.getAs(MimeType.PDF)], htmlBody : <html><h1>here is the header</h1> and here the name
</html>});
4

There are 4 answers

4
piscator On

The values are already in rngvls. Using the method getValue() in a loop isn't very good for performance.

You can add the name to a string containing the html using concatenation.

Try this:

for (var i = 0 ; i<rngvls.length; i++){

    var name = rnglvls[i+1][4];
    var html = "<html><h1>here is the header</h1>"+name+"html";

    GmailApp.sendEmail(dest, ogg, body,{name:nam, attachments: [fail.getAs(MimeType.PDF)], htmlBody : html});
}
0
TsunamiSteve On

You can pass HTML formatting into the script by setting an HTML variable and then using that variable in the optional MailApp arguments. You can also use other variables within the body using this approach.

It should be noted that the GmailApp class is more likely to require a re-authorization because it can access a user's inbox, so if all you're doing is sending mail the MailApp class is the recommended approach.

var1 = ...
var2 = ...
var3 = ...
var html = '<body>' +
  '<p>Message.</p>' +
  '<p><b>Variable 1: </b>' + var1 + '</p>' +
  '<p><b>Variable 2: </b>' + var2 + '</p>' +
  '<p><b>Variable 3: </b>' + var3 + '</p>' +
  '</body>';
MailApp.sendEmail(recipient, subject, {htmlBody:html});
3
Serge insas On

Here is a script that you can use if you don't need to send too many emails messages (because it's rather slow), it uses a Google doc as a template with a few placeholders and creates a temporary copy of it that is converted to HTML and sent as a message body.

All the features available in Google docs are correctly translated in HTML, including images, fonts etc...

Some formatting are slightly modified (image alignment for example) but nothing to really worry about.

If you are planning to send many messages you might need to create an automated process to send the messages in smaller bunches on a timer trigger but that would be out of subject here.

Code :

function customizeHTMLMessage() {
  var templateId = '1aZAgVbvZYD1JcoLqv8x-A5knHLrVNOfEyGh_O-06dgg';// the template doc with placeholders
  var docId = DriveApp.getFileById(templateId).makeCopy('temp').getId();
  var doc = DocumentApp.openById(docId);// the temp copy
  var body = doc.getBody();
  var items = ['#day#','#starter#','#main course#','#dessert#'];// the placeholders in this example
  var values = ['Monday','Smoked fish','Beef steak','ice cream'];
  for(var n = 0;n<items.length;n++){
    Logger.log(items[n]+' to replace with '+values[n]);// check in the log
    body.replaceText(items[n],values[n]);// update the temp doc
  }
  doc.saveAndClose();// save changes before conversion
  var url = "https://docs.google.com/feeds/download/documents/export/Export?id="+docId+"&exportFormat=html";
  var param = {
  method      : "get",
  headers     : {"Authorization": "Bearer " +     ScriptApp.getOAuthToken()}
  };
  var htmlBody =     UrlFetchApp.fetch(url,param).getContentText();
  var trashed = DriveApp.getFileById(docId).setTrashed(true);// delete temp copy
  MailApp.sendEmail(Session.getActiveUser().getEmail(),'test','html body',{htmlBody : htmlBody});// send to myself to test
}

note : this template doc is shared (view only) you can make a copy to test it.


Edit : screen capture in 2 different mail readers.

enter image description here

2
Riël On

Use the Drive service and get the export link.

var url = "https://docs.google.com/feeds/download/documents/export/Export?id="+docid+"&exportFormat=html";
  var param = {
  method      : "get",
  headers     : {"Authorization": "Bearer " +     ScriptApp.getOAuthToken()}
  };
  var htmlBody =     UrlFetchApp.fetch(url,param).getContentText();
 }