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

Question

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?

Take a look at this, it might help you. webapps.stackexchange.com/questions/54670/… — UnknownPerson, Commented Jul 19, 2019 at 0:01

Mario R. · Accepted Answer · 2019-07-19 17:33:52Z

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

These are super helpful answers. Thank you! I wanted to ask how I might add a simpler range of cells into the email - to simply put the section of the spreadsheet directly into the email. For example, if I wanted to copy paste A2 to D4 into the email through the script app. The table you made is similar to what I want to do, however, how can I take the contents directly in the spreadsheet and put that as a table into the email? Thank you! — Katie Melosto, Commented Jul 20, 2019 at 23:02

Jescanellas · Accepted Answer · 2019-07-19 15:29:42Z

1

Try attaching the body with htmlbodyand use <br /> instead of /n.

I stored Hello World. <br /> My name is Jennifer.<br /> What is your name? in the A1 cell:

var body = sheet.getRange("A1").getValue();

   MailApp.sendEmail({
    to: "[email protected]",
    subject: "Your Google Spreadsheet Alert",
    htmlBody:  '<html><body>' + body + '</body></html>'});

answered Jul 19, 2019 at 15:29

Jescanellas

2,6082 gold badges11 silver badges20 bronze badges

Add a comment |

ADW · Accepted Answer · 2019-07-19 01:35:42Z

To send an email with images, charts, formatting etc, you may want to look at how to create and server HTML in Google Apps Script as well as the section on Templated HTML.

For simpler formatting like line breaks, there are two options:

You can add the line break in the cell using ⌘ + Enter on a Mac or Ctrl + Enter on Windows (and not by using "\n" in the cell text). When you fetch the value of this cell and send it as email, the email will contain the line breaks.
Or write each line of the email in a different cell. And then string the message together for the email like this:

.

   var message = cell1Value + "\n" + cell2Value;

Collectives™ on Stack Overflow

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

3 Answers 3

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Related