How to use multiple cells for email body, Google Sheets

Question

I'm using the following code to send emails from a Google Sheets spreadsheet.

function email(){
  var rng = SpreadsheetApp.getActiveSheet().getActiveRange()
  var email = rng.getValues(); GmailApp.sendEmail(email[0][0], email[1][0], email[2][0]);
}

The 3 selected cells contain email address: email[0][0], subject: email[1][0], and message body: email[2][0].

The code works well but I would like the message body to be built up from multiple cells. So that for example cells [2][0], [3][0], [4][0] will all be contained within the message body.

Answer 1

Since email address: email[0][0], subject: email[1][0], and message body: email[2][0] are all in column 1. I might guess that that you holding content for each addressee in different columns, so it might make sense for you to use email[3][0] through email[n][0] for additional information for this address and then you could loop through columns to send multiple emails.

Set lets say that email[3][0] = Salutation and email [4][0] might contain message body and email [5][0] your find goodbye's.

So then the email would command would looks something like this:

GmailApp.sendEmail(email[3][0] + email[3][0] + email[3][0], email[1][0], email[2][0]); }

and if you run in a loop sequencing your way through all of the columns you would replace the zero with you index parameter. This could be much more complex depending upon how refined your requirements are. But further development of this idea would require information that you haven't shared.

Answer 2

Given the variables you've already defined, you can just use the + operator to add these array objects to each other. Since we're dealing with strings, it'll concatenate the values.

The correct syntax for GmailApp.sendEmail() is as follows:

GmailApp.sendEmail(recipient, subject, body);

So your new line of code to concatenate the values would be:

GmailApp.sendEmail(email[0][0], email[1][0], email[2][0] + email[3][0] + email[4][0]);

Or instead, you could define your body variable separately and just call it inside your sendEmail() like below:

function email(){
  var rng = SpreadsheetApp.getActiveSheet().getActiveRange();
  var email = rng.getValues();
  var body = email[2][0] + email[3][0] + email[4][0];
  GmailApp.sendEmail(email[0][0], email[1][0], body);
}

This just makes it a little easier to keep track of what the body of the email would look like, as your line of code for sendEmail() would start to wrap or extend off of your browser window if you were trying to concatenate a multitude of array objects into one email body.

---

References:

1. Javascript + Operator
2. GmailApp.sendEmail()