Search code examples
google-apps-scriptgoogle-sheetsgmail-apigoogle-sheets-api

Can I use script to send an email with HTML formatting?

I am creating an invoicing script add-on for Google Sheets. I want to be able to write in a value for a cell in the sheet that has an email message in it. I now understand how to send an email through Google script, however, I can only send simple messages without any formatting. My end goal is to be able to send an email with an image, chart [that holds the invoice data in it] as well as additional formatting, such as paragraphing and font.

I tried to add HTML into the actual cell, but it just sends the email with the HTML as the message [without any actual formatting applied to the email]. For example: I tried to add two paragraphs by placing the following into the sheet cell B2: 

Hello World. \n My name is Jennifer. \n What is your name?

and it came out exactly like that with the \n written into the message.

 function sendEmail2(){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3').getRange('B2');
var emailAddress = emailRange.getValues();


  //email info
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
var range = sheet.getRange('C2');
  var newMessage = range.getValue();
// Send Alert Email.
var message = 'This is your Alert email!'; // Second column
var subject = 'Your Google Spreadsheet Alert';
MailApp.sendEmail(emailAddress, subject, newMessage);
}

The email is sent but without the actual HTML formatting inside the email.

How do we sent emails with formatting through Google Apps Script?

Solution

  • Here is an example of how to send HTML email using Apps Script also attaching the chart to the HTML document. It's based on a Spreadsheet I have. You can create a variable with the HTML body and replace the information you want with variables and retrieve the information from the same Spreadsheet. 

     function sendEmailHtml(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var as = activeSheet.getSheets()[0]; /*selecting the very first sheet within the spreadsheet*/ 
  var name = activeSheet.getRange("A2:A2").getValue();
  var age = activeSheet.getRange("B2:B2").getValue();  
  var country = activeSheet.getRange("C2:C2").getValue();  
  var fontColor,chartsTable;
  var email = "[email protected]";/*send the email to*/
  var subject = "Information";    
  var charts = as.getCharts();  
  var chartBlobs=new Array(charts.length);   
  var emailImages={};
  for(var i=0;i<charts.length;i++){
    var builder = charts[i].modify();   
    var newchart = builder.build().getBlob();
    chartBlobs[i]= newchart.getAs('image/png');   
    chartsTable = "<table>" +
    "<tr>" +
      "<th><p>Chart</p><img width='400' height='250'src='cid:chart"+0+"'></th>" +  /*adding the chart 0 on the table*/    
     "</tr>"+     
      "</table>";    
     emailImages["chart"+i]= chartBlobs[i];    
    Logger.log(charts[i].getChartId());
  } 
  var body = "<html><head><style type='text/css'>.ritz .waffle a { color: inherit; }.ritz .waffle .s1{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;border-left:1px SOLID #000000;background-color:#ffffff;text-align:left;color:#000000;font-family:Arial;font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s0{border-bottom:1px SOLID #000000;border-right:1px SOLID #000000;background-color:#1155cc;text-align:left;font-weight:bold;color:#ffffff;font-family:Arial;font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style></head><body><div class='ritz' dir='ltr'><table class='waffle' cellspacing='0' cellpadding='0'><tbody><tr style='height:20px;'><th id='0R0' style='height: 20px;' class='row-headers-background'><div class='row-header-wrapper' style='line-height: 20px;'></div></th><td style='width:100px' class='s0' dir='ltr'>Name</td><td style='width:100px' class='s0' dir='ltr'>Age</td><td style='width:100px' class='s0' dir='ltr'>Country</td></tr><tr style='height:20px;'><th id='0R1' style='height: 20px;' class='row-headers-background'><div class='row-header-wrapper' style='line-height: 20px;'></div></th><td class='s1 '>" + name +  "</td><td class='s1'>"+age+"</td><td class='s1'>"+country+"</td></tr></tbody></table></div></body></html><br>" + chartsTable;  
  MailApp.sendEmail(email, subject, body, {htmlBody: body, name:'Department Name', inlineImages:emailImages}); /*the inlineImages adds the png to the email's body*/ 
}

    The spreadsheet looks like this

    enter image description here