0

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);
 });

*/
  • Does your code work? If not, what's the problem? What exactly are you looking for? – slybloty Apr 4 at 18:47
  • Its sending out the mails fine, but I'm not sure how to add attachments (multiple attachments) also to the mail. And wasn't sure on where to add the code for it either. I have the file on google drive, and need it to attach to the mails being sent out. Could someone help me out with the lines of code? – Amit Apr 4 at 18:51
0

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.

  • And if I have to attach multiple files can I use the same? – Amit Apr 4 at 19:48
  • @Amit I updated the answer with multiple files solution. – slybloty Apr 4 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.