Sending html mail in a loop

Question

I have this code which works perfectly :

var EMAIL_SENT = 'EMAIL SENT';
  
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getRange("F1:F").getDisplayValues().filter(String).length;
  // Fetch the range of cells 
  var dataRange = sheet.getRange(2, 1,numRows-1,21);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[8] + "," + row[10] + "," + row[14]; 
    var message = row[19]; 
    var emailSent = row[20]; 
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Un nouvel adhérent vous a été affecté';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 21).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

I would like to change the message variable by a html template. I've created my html file but if I change the message variable with the html file name like that it doesn't work :

var EMAIL_SENT = 'EMAIL SENT';
  
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getRange("F1:F").getDisplayValues().filter(String).length;
  // Fetch the range of cells 
  var dataRange = sheet.getRange(2, 1,numRows-1,21);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[14]; 
    var message = template_social.html; 
    var emailSent = row[20]; 
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Un nouvel adhérent vous a été affecté';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 21).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Do I need to rewrite the whole code ? Any idea ?

Thanks a lot

ale13 · Accepted Answer · 2020-10-19 14:54:21Z

1

You will have to create a new file with the html file you have and then make use of HtmlService to get its contents.

  var body = HtmlService.createHtmlOutputFromFile('template_social').getContent();
  var emailAddress = row[14];
  var subject = 'Un nouvel adhérent vous a été affecté';
  MailApp.sendEmail({
    to: emailAdress, 
    subject: 'subject', 
    htmlBody: body});

Reference

answered Oct 19, 2020 at 14:54

ale13

6,1423 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sending html mail in a loop

1 Answer 1

Reference

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Reference

Comments

Your Answer

Sign up or log in

Post as a guest

Related