Google Script – How to Attach a File to an Email

attachmentgoogle sheetsgoogle-apps-script

I'm using Google Script to send emails, but I'm not sure how to add attachments (multiple attachments). I am also not sure where to add the code for it either. The file is on Google Drive and I need to attach it to the emails being sent out.
Could someone help me out with the code?

Here is the code I am using to send emails:

function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}


function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}


function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

function normalizeHeader(header) {
  var key = '';
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == ' ' && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}


function isCellEmpty(cellData) {
  return typeof(cellData) == 'string' && cellData == '';
}


function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}


function isDigit(char) {
  return char >= '0' && char <= '9';
}


function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange('A1').getValue();


  var objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];


    var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = 'Mail Merge Test';
    var file = DriveApp.getFilesByName('2019_MA_BenefitsGuide.pdf')
    MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText );


  }
}

function fillInTemplateFromObject(template, data) {
  var email = template;
  // Search for all the variables to be replaced, for instance ${"Column name"}
  var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

  // Replace variables from the template with the actual values from the data object.

  for (var i = 0; i < templateVars.length; ++i) {

    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || '');
  }

  return email;
}

/*
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      var file = DriveApp.getFilesByName('test123.pdf')
      MailApp.sendEmail(emailAddress, subject, message, {
     attachments: [file.getAs(MimeType.PDF)],
     name: 'Automatic Emailer Script'
      MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
 });

*/

Best Answer

In function sendEmails() where you send the email, you'll have to attach the file(s). If you have multiple files you'll need to send them as an array/list.

# Get file from Google Drive
var file = DriveApp.getFilesByName('test123.pdf').next()

# For multiple files
var files = []
files.push(file)

# Send email with file attached
MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText, {attachments: files})

Keep in mind, file is actually a list FileIterator.