0

I have "to" in column A & "message/body" in column "B". I have a code which sends an email. However in my message I have a word hyperlinked to another sheet, while sending the email, the hyperlink is not considered. Please find the screenshot below.The hyperlink is visible in column B

However when the email is sent, the hyperlink is not visible. Please the image below

The hyperlink is not considered in email

    function sendEmails() {    
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 1;
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var subject = 'Sending emails from a Spreadsheet';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}
Balaji G
  • 41
  • 2

1 Answers1

2

Use Templated HTML

var htmlBody = HtmlService.createTemplate('Email with <a href=<?=link?>> Some link </a>');  

htmlBody.link = row[1];

MailApp.sendEmail({
  to: row[0],
  subject: 'Sending emails from a Spreadsheet',
  htmlBody: htmlBody.evaluate().getContent()
});

If you have embedded cell links, you can extract them using Advanced Sheets services, as describe here, for example

var values = Sheets.Spreadsheets.get(SpreadsheetApp.getActive().getId(), {ranges: "Sheet1!B1:B10", fields: "sheets/data/rowData/values/hyperlink"})
var links = values.sheets[0].data[0].rowData.map(v => v.values[0].hyperlink);

them use them in templated HTML example I provided.

roma
  • 1,512
  • 10
  • 20