Script: Send a Personalized Email
With our mailing list waiting, we can now read the settings sheet and create a personalized email. The MailToList function accepts a validRows array.
/**
* Send email to list
* @param {Array} validRows - Row from the Maillist table
*/
function MailToList(validRows) {
if (validRows == undefined || validRows == null) {
SpreadsheetApp.getActive().toast("No valid email addresses found.");
return;
}
// build mail string for verification
var mailString = "";
validRows.forEach(element => mailString = mailString.concat(element[C_NAME], ": ", element[C_EMAIL], ", \n"));
var result = SpreadsheetApp.getUi().alert("You're about to send emails to: "+validRows.length +" persons: \n"+mailString, SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
if(result === SpreadsheetApp.getUi().Button.CANCEL) {
SpreadsheetApp.getActive().toast("Canceled. No emails were sent!");
}
else {
for(var i = 0; i < validRows.length; i++) {
// set placeholders in subject and body
let replaceArray =
[
{find: "[name]", replacement: validRows[i][C_NAME], empty: "acquaintance"},
{find: "[email]", replacement: validRows[i][C_EMAIL], empty: "unknown"},
];
let replaced = ComposeEmail(replaceArray);
MailApp.sendEmail({
to: validRows[i][C_EMAIL],
subject: replaced.subject,
htmlBody: replaced.body
});
}
}
}
What this script does is prompt the user with a confirm box before it iterates over all persons in the list and will compose an email for each and every one of them. This done with the ComposeEmail function below.
/**
* Fetch email subject and body from Settings sheet
* Set placeholders to actual data from table
* @param {Array} validRows - Row of the table, validated to mail to
* @returns {Object} {subject: subject content, body: body content}
*/
function ComposeEmail(replaceArray) {
let settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
let emailSubject = settingsSheet.getRange("B2").getValue().toString();
let emailBody = settingsSheet.getRange("B3").getValue().toString();
replacedEmailSubject = ReplaceStrings(emailSubject, replaceArray);
replacedEmailBody = ReplaceStrings(emailBody, replaceArray);
return {subject: replacedEmailSubject, body: replacedEmailBody};
}The ComposeEmail function will load the subject and body fields from the Settings sheet and will use a replaceArray to fill in each placeholder with the use of the ReplaceStrings function. The results are the defined email texts from the Settings sheet with [name] and [email] filled in.
/**
* replaces all 'find' with 'replacement' or 'empty' when replacement has no length
*/
function ReplaceStrings(targetText, replaceArr) {
for (var i = 0; i < replaceArr.length; i++) {
try {
if (replaceArr[i].replacement.length > 0) {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].replacement);
}
else {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].empty);
}
}
catch(error) {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].empty);
}
}
return targetText;
}This concludes the code. The code from the Create an Mailing List chapter combined with the code from this chapter can all be in one *.gs file. For the full working code, see the Full Code chapter.
