21

I have a script that is using the following script:

MailApp.sendEmail(row.shiftManager, "Holiday Approval Request", "", {htmlBody: message});
  row.state = STATE_PENDING;

Howeverm I would like to also send the same mail to row.shiftSupervisor, this is probably something really simple that I've overlooked, but I figured someone here would know straight away what it was.

Cheers for your help :)

Edit - I tried to use:

MailApp.sendEmail(row.shiftManager, row.shiftSupervisor, "Holiday Approval Request", "", {htmlBody: message});
      row.state = STATE_PENDING;

But no joy.

Edit 2 - I got it working with:

  MailApp.sendEmail(row.shiftManager, "Holiday Approval Request", "", {htmlBody: message});
  MailApp.sendEmail(row.shiftSupervisor, "Holiday Approval Request", "", {htmlBody: message});
  row.state = STATE_PENDING;

Not the most graceful looking piece of code, but it does the job...

double-beep
  • 5,031
  • 17
  • 33
  • 41
SL8t7
  • 617
  • 2
  • 9
  • 27
  • what do you mean by multiple variables? – rpm Feb 05 '15 at 22:31
  • Totally a mistake, I was mutlitasking (badly) and put something else in the title. – SL8t7 Feb 05 '15 at 22:35
  • You will have to loop over multiple email addresses to send one email to them. – rpm Feb 05 '15 at 22:45
  • Yeah, I got it to work with: `MailApp.sendEmail(row.shiftManager, "Holiday Approval Request", "", {htmlBody: message}); MailApp.sendEmail(row.shiftSupervisor, "Holiday Approval Request", "", {htmlBody: message}); row.state = STATE_PENDING;` It's a bit clunky looking as far as code goes, but its doing the job. – SL8t7 Feb 05 '15 at 22:56

2 Answers2

31

The email addresses can be concatenated (joined together) using the plus sign with a comma in between each email address. In JavaScript the plus sign can be used for addition OR joining strings. The plus sign is both an addition operator and a string operator in JavaScript. Strings are text, and if you use the plus sign to concatenate text strings then it's a string formula.

One solution would be:

var recipient = row.shiftManager + "," + row.shiftSupervisor;
MailApp.sendEmail(recipient, "Holiday Approval Request", "", {htmlBody: message});

In the above example, there are 4 parameters. But MailApp.sendEmail() has multiple possible parameter structures. The following example shows all of the settings put into an object, where the "to" key in the object is for the recipient.

MailApp.sendEmail({
  to: recipient,
  cc: recipientsCC,
  subject: Subject,
  htmlBody: html
});

A complete example:

function sendToMultiple() {
  var message = "This is a test of HTML <br><br> Line two";
  
  var recipientsTO = "example@gmail.com" + "," + "example@yahoo.com";
  var recipientsCC = "example@gmail.com";
  var Subject = "Vacation Approval Request";
  var html = message;
  
  MailApp.sendEmail({
    to: recipientsTO,
    cc: recipientsCC,
    subject: Subject,
    htmlBody: html
  });

}

Documentation with an example is at the following link:

Google Documentation - MailApp.sendEmail

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Hey, thanks for this! It looks much better than my crudely put together solution. However, I keep getting a message "Missing : after property ID. (line 96, file "Approval")" – SL8t7 Feb 06 '15 at 23:21
  • Hey again, thanks for your help with this. I had it working, but it appeared to be interfering with another part of the script. However, I figured out I could do `MailApp.sendEmail(row.shiftManager + "," + row.shiftSupervisor, "Holiday Approval Request", "", {htmlBody: message});` thanks to the way you had it laid out. A bit more elegant than repeating the function over again! This is why scripting / coding while working a night shift is bad. Haha, you miss silly little things! Cheers again~ – SL8t7 Feb 07 '15 at 02:03
  • Glad you got it to work. I'll update the answer with your solution also. Please consider marking it as the correct answer. – Alan Wells Feb 07 '15 at 02:08
  • Done, thanks for everything. At the risk of sounding slightly cheeky, I've also mailed you regarding another issue I've been having with a piece of code. Any help you could give would be greatly appreciated! :) – SL8t7 Feb 07 '15 at 02:49
3

Here is how I'm doing it:

//check quota and log
const emailsLeft = MailApp.getRemainingDailyQuota();
console.log( emailsLeft + " emails left in quota");

//get list of emails from spreadsheet itself
//filter out empty rows
const emails = getTab("Config").getRange("D2:D").getValues().map(function(el){ return el[0]; }).filter(function(el){ return el != '' });  

//send emails from NO_REPLY email, subject and HTML body
MailApp.sendEmail({
  to: emails.join(","),
  subject: subject,
  htmlBody: html,
  noReply: true
});

function getTab(name) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  return sheet.getSheetByName(name);
}

getTab() and other helper functions can be found here https://github.com/tim-kozak/google-spreadsheet-helpers

Tim Kozak
  • 4,026
  • 39
  • 44