Sending an email with an html body from another email address via google sheets

Question

I'm having a problem sending an html body message from a Google sheet from another email address that is an alias.

I can send it using the mailApp, but when I switch to the GmailApp I can't seem to get it to work.

The script I am using is below:

function sendNotification(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var emailAdd = "[email protected]";
  if(event.range.getA1Notation().indexOf("G") > -1 && sheet.getRange("G" + row).getDisplayValue() > 999 && emailAdd.length > 1)
  {
     var rowVals = getActiveRowValues(sheet);
    var aliases = GmailApp.getAliases();
Logger.log(aliases);
     GmailApp.sendEmail(
       "[email protected]",
       "Allocation Request - " + rowVals.quantity + " cases on " + rowVals.date,
       {htmlBody: "There has been a new allocation request from " + rowVals.name + " in the " + rowVals.team + " team.<br \> <br \> " 
       + "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr><th>Issuing Depot</th><th>Delivery Date</th><th>Case Quantity</th></tr><tr><td>"+rowVals.depot+"</td><td>"+rowVals.date+"</td><td>"+rowVals.quantity+"</td></tr></table>" 
       + "<br \>To view the full details of the request, use the link below.<br \> <br \>" + 
       "<a href=\"https://docs.google.com/spreadsheets\">Allocation Requests</a>"
       +"<br \> <br \><i>This is an automated email. Please do not reply to it.<\i>"},
       {from: aliases[0]}
                        );
  }
}

  function getActiveRowValues(sheet){
  var cellRow = sheet.getActiveRange().getRow();
  // get depot value
  var depotCell = sheet.getRange("E" + cellRow);
  var depot = depotCell.getDisplayValue();
  // get date value
  var dateCell = sheet.getRange("F" + cellRow);
  var date = dateCell.getDisplayValue();
  // get quantity value
  var quantCell = sheet.getRange("G" + cellRow);
  var quant = quantCell.getDisplayValue();
  // return an object with your values
  var nameCell = sheet.getRange("B" + cellRow);
  var name = nameCell.getDisplayValue();
  var teamCell = sheet.getRange("C" + cellRow);
  var team = teamCell.getDisplayValue();
  return {
    depot: depot,
    date: date,
    quantity: quant,
    name: name,
    team: team
  } }

I've managed to get the email to send from my alias, but it just sends and message containing [object], whereas not sending it from an alias works fine.

Could someone take a look and see what I'm doing wrong? I've not been able to find an answer on here yet. Thanks.

There are 4 parameters to sendEmail(TO, SUBJECT, Email BODY, OPTIONS) You have the Options object in the 3rd parameter for the email body. You probably made this mistake because you are trying to put everything together in one place. You should create a variable for the options object and the Html, so that the sendEmail() is more readable. — Alan Wells
Thanks, noticed my error. However how do I set the variable as an html body? — P.Seymour

Alan Wells Alan Wells · Accepted Answer · 2017-02-21T13:34:39

Create an object then add elements to the object:

var bodyHTML,o,sendTO,subject;//Declare variables without assigning a value

o = {};//Create an empty object

bodyHTML = "There has been a new allocation request from " + rowVals.name;

o.htmlBody = bodyHTML;//Add the HTML to the object with a property name of htmlBody
o.from = aliases[0]; //Add the from option to the object

sendTO = "[email protected]";
subject = "Allocation Request - " + rowVals.quantity + " cases on " + rowVals.date;

GmailApp.sendEmail(sendTO,subject,"",o);//Leave the third parameter as an empty string because the htmlBody advanced parameter is set in the object.

Sending an email with an html body from another email address via google sheets

1 Answers