I have made some code to automate my email from a spreadsheet and want to expand it across my team but I would like to add a footer/signature to it and also not have to include the recipient and subject in the code itself but rather pull it from the spreadsheet. I have it laid out in the spreadsheet as;
I would like the email footer to look like that but when I run the script it brings back "Range"
function EODReportEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Tasks");; // Use data from the active sheet
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow(); // Number of rows to process
var lastColumn = sheet.getLastColumn(); // Last column
var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet
var data = dataRange.getValues(); // Fetch values for each row in the range
var recipient = "shea.a.murphy@icloud.com" // Email address report will be sent to
var subject = "Shea Murphy - EOD Email" // Subject heading of email e.g. Shea Murphy - EOD Email
var footer = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("C2");
Logger.log(footer);
// Work through each row in the spreadsheet
for (var i = 0; i < data.length; ++i) {
var row = data[i];
// Assign each column a variable
var Point1 = row[0]; // Col A: 1st point to be included in email
var Point2 = row[1]; // Col B: 2nd point to be included in email
var Point3 = row[2]; // Col C: 3rd point to be included in email
var Point4 = row[3]; // Col D: 4th point to be included in email
var Point5 = row[4]; // Col E: 5th point to be included in email
var Num1 = "<b>1. </b>" // Number variables for email points
var Num2 = "<b>2. </b>"
var Num3 = "<b>3. </b>"
var Num4 = "<b>4. </b>"
var Num5 = "<b>5. </b>"
var Task1 = Num1 + Point1 // Note 1 to be inlcuded in the email
var Task2 = Num2 + Point2 // Note 2 to be inlcuded in the email
var Task3 = Num3 + Point3 // Note 3 to be inlcuded in the email
var Task4 = Num4 + Point4 // Note 4 to be inlcuded in the email
var Task5 = Num5 + Point5 // Note 5 to be inlcuded in the email
if (Point1 == undefined) {Task1 = " "};
if (Point2 == undefined) {Task2 = " "};
if (Point3 == undefined) {Task3 = " "};
if (Point4 == undefined) {Task4 = " "};
if (Point5 == undefined) {Task5 = " "};
// Build the email message
var emailBody = '<b style="font-family:georgia;font-size:18px;font-style:italic; color: #D04A02";>EOD Report</b>';
emailBody += '<p>Please see what I have worked on today below:<p>';
emailBody += '<dl><dd>'+ Task1 +'</dd>';
emailBody += '<dl><dd>'+ Task2 +'</dd>';
emailBody += '<dl><dd>'+ Task3 +'</dd>';
emailBody += '<dl><dd>'+ Task4 +'</dd>';
emailBody += '<dl><dd>'+ Task5 +'</dd>';
emailBody += '<p>Let me know if you have any questions.<p>';
emailBody += footer
// Create the email draft
GmailApp.sendEmail(
recipient, // Recipients
subject, // Subject
' ', // Body
{
htmlBody: emailBody, // Options: Body (HTML)
}
)
}
}
Basically, is there any way of bringing the code across the two pages for the recipient, subject and include the signature at the end with all correct formatting rather than writing it out line by line in the code itself?
