Sending HTML email to multiple recipients using Google Sheet

1,295 views

Skip to first unread message

Nicusor Ionescu

unread,

Jan 15, 2020, 6:51:29 AM1/15/20

to Google Apps Script Community

Hello guys,

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 'x...@domain.com' , 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,

Kind regards,

Nick

Capture.PNG

Jorge Forero

unread,

Jan 15, 2020, 3:13:58 PM1/15/20

to Google Apps Script Community

Hello Nicusor,

I did some changes to the original script:

function sendEmails() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Bi-dimesional Array with data

// It's more efficient to get all data stored on Array, outside the loop. Then process the array data

var dataBox = ss.getDataRange().getValues();

// Arrays index start 0 and 1 to discard the names row

for (var i=1;i<dataBox.length;i++){

// Get the actual record (row on array)

var record = dataBox[i];

// and get the needed fields

var email = record[0];

var name = record[1];

// I get the html "Template"

var htmlOutput = HtmlService.createHtmlOutputFromFile('email.html').getContent();

// Inside the template, I replace the name

htmlOutput = htmlOutput.replace('##NAME##', name);

var subjectLine = "Test";

GmailApp.sendEmail(email, subjectLine, '', { htmlBody: htmlOutput } );

};

// This is the email.html file:

<!DOCTYPE html>

<html>

<head>

</head>

<body>

Hi ##NAME##,

This is your email.

Greetings.

</body>

</html>

And this is the output that i got from my example spreadsheet:

[20-01-15 15:06:28:056 EST] >><!DOCTYPE html>

<html>

<head>

</head>

<body>

Hi Jorge,

This is your email.

Greetings.

</body>

</html>

[20-01-15 15:06:28:060 EST] >><!DOCTYPE html>

<html>

<head>

</head>

<body>

Hi Daniel,

This is your email.

Greetings.

</body>

</html>

Greetings

Jorge.

Reply all

Reply to author

Forward

0 new messages