CODEWITHSUNDEEP
emailgoogle-apps-scriptgoogle-sheets-api
P.Seymour
P.Seymour

Reputation: 63

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

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.

Upvotes: 1

Views: 725

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

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.

Upvotes: 1

Related Questions