Sending HTML email to multiple recipients using Google Sheet and AppScript

0
votes

I created a script that helps me send bulk HTML emails to multiple recipients. In my Google Sheet file Col 1 contains the email address and Col 2 has the Name of the recipient. The one more thing i am trying to achieve and got stuck at is that i want to populate each email with the name of the recipient. For example , i am sending email to [email protected] , i would like the email to start with "Dear xxx" and so on for all recipients.

How can this be done?

My code is as follows: 

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var lr = ss.getLastRow(); 
  var lc = ss.getLastColumn();

  for (var i = 2;i<=lr;i++){

      var currentEmail = ss.getRange(i, 1).getValue();
      var subjectLine = "Test";
      var htmlOutput = HtmlService.createHtmlOutputFromFile('email'); 
      var email = htmlOutput.getContent();


      MailApp.sendEmail( currentEmail, "test", email, { htmlBody: email } ) 


    }      
}

Thank you

2
javascriptemailgoogle-apps-scriptgoogle-sheets

2 Answers

0
votes

You can use indexOf to find the @ character and split the array into a substring to only get the characters positioned before @. Then, you can just add it to your htmlBody parameter. For example:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var lr = ss.getLastRow(); 
  var lc = ss.getLastColumn();

  for (var i = 2;i<=lr;i++){

      var currentEmail = ss.getRange(i, 1).getValue();
      var subjectLine = "Test";
      var htmlOutput = HtmlService.createHtmlOutputFromFile('email'); 
      var email = htmlOutput.getContent();

      //My changes start here
      var index = currentEmail.indexOf('@');
      var name = currentEmail.substr(0, index); //chars from position 0 to index

      var head = '<html><body>Dear ' + name + ': <br />';

      MailApp.sendEmail( currentEmail, "test", email, { htmlBody: head + email } ) //You can add + '</body></html>' at the end if it's necessary.


    }      
}
0
votes

What about adding a placeholder in the email body and replacing it with the name you want to use?

For example, the HTML email body template could be something like:

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
       <h1>Dear {0},</h1>
       <p>Let's try Apps Script!</p>
 </body>
</html>

Then changing your original script a bit:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var lr = ss.getLastRow(); 

  for (var i=2; i<=lr; i++){

    var userEmail = ss.getRange('A'+i).getValue();
    var userName = ss.getRange('B'+i).getValue();
    var subject = 'Test Personalized Mass Email';
    var htmlOutput = HtmlService.createHtmlOutputFromFile('SampleEmail'); 
    var email = htmlOutput.getContent();
    email = email.replace('{0}', userName);

    MailApp.sendEmail(userEmail, subject, email, { htmlBody: email });

    }  
}