Column “X” has the mails, column “Y” has the name of the PDF files to send (stored in folder “Z” in Drive), how do I send each email with its PDF?

Question

I have this script which does the following:

Prompt for the column with the emails.
Prompt for the subject of the email.
Prompt for the body of the email.
Prompt for the starting row.
The script sends one email for each row on the sheet.

I would like to expand the script to also prompt for the column with the attachment that should be added to each email (prompting for the column is not the issue). Such a column will have the file name.

The idea is that given the file name, it will search for it on a certain folder and will add it as an attachment (this is the part I need your help with).

function emailSenderSameMessageDifferentPdf() {

  var activeSs = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeSsLastRow = activeSs.getLastRow();

  // Prompt for the column with the emails

  var activeSsUi = SpreadsheetApp.getUi();
  var activeSsUiEmailsColumn = activeSsUi.prompt("Email", "Enter the number of the column with the emails", activeSsUi.ButtonSet.OK_CANCEL);
  var activeSsUiEmailsColumnText = activeSsUiEmailsColumn.getResponseText();
  var activeSsUiEmailsColumnButton = activeSsUiEmailsColumn.getSelectedButton();

  if(activeSsUiEmailsColumnButton == activeSsUi.Button.CANCEL) {

    return;

  }

  // Prompt for the subject of the email

  var activeSsUiEmailsSubject = activeSsUi.prompt("Email", "Enter the subject of the email", activeSsUi.ButtonSet.OK_CANCEL);
  var activeSsUiEmailsSubjectText = activeSsUiEmailsSubject.getResponseText();
  var activeSsUiEmailsSubjectButton = activeSsUiEmailsSubject.getSelectedButton();

  if(activeSsUiEmailsSubjectButton == activeSsUi.Button.CANCEL) {

    return;

  }

  // Prompt for the body of the email

  var activeSsUiEmailsBody = activeSsUi.prompt("Email", "Enter the body of the email", activeSsUi.ButtonSet.OK_CANCEL);
  var activeSsUiEmailsBodyText = activeSsUiEmailsBody.getResponseText();
  var activeSsUiEmailsBodyButton = activeSsUiEmailsBody.getSelectedButton();

  if(activeSsUiEmailsBodyButton == activeSsUi.Button.CANCEL) {

    return;

  }

  // Prompt for the starting row

  var activeSsUiEmailsRow = activeSsUi.prompt("Email", "Enter the number of the starting row", activeSsUi.ButtonSet.OK_CANCEL);
  var activeSsUiEmailsRowText = activeSsUiEmailsRow.getResponseText();
  var activeSsUiEmailsRowButton = activeSsUiEmailsRow.getSelectedButton();

  if(activeSsUiEmailsRowButton == activeSsUi.Button.CANCEL) {

    return;

  }

  // Iterate through all the rows with emails

  for (activeSsUiEmailsRowText = activeSsUiEmailsRowText; activeSsUiEmailsRowText <= activeSsLastRow; activeSsUiEmailsRowText++) {

    var activeSsUiEmailsColumnCurrentAddress = activeSs.getRange(activeSsUiEmailsRowText, activeSsUiEmailsColumnText).getValue();

    GmailApp.createDraft(activeSsUiEmailsColumnCurrentAddress, activeSsUiEmailsSubjectText,  null, {htmlBody:activeSsUiEmailsBodyText, cc: "[email protected]"});
    // GmailApp.sendEmail(activeSsUiEmailsColumnCurrentAddress, activeSsUiEmailsSubjectText,  null, {htmlBody:activeSsUiEmailsBodyText, cc: "[email protected]"});

  }
}

Seems like a lot prompting. I think I'd tire of that pretty quickly. A couple of things you might like to change is to use folder and file id's rather than names because in Google Drive files can have the same name. Spreadsheet.getUi() contains all of the dialogs available server side. You can also build your own dialogs via HtmService. You can send email with MailApp or GmailApp look in documentation. Check the versions with options for attachments — Cooper

ZektorH ZektorH · Accepted Answer · 2019-10-08T07:05:37

You can do this by creating your message/draft using the options object.

Your code will look a little like this:

var filename = "whatever you got from the column";
var file = DriveApp.getFilesByName(filename);
if (file.hasNext()) {
  MailApp.sendEmail(emailAddress, subject, message, {
    attachments: [file.next().getAs(MimeType.PDF)],
    name: filename
}

However, this will select the first file with that name. I'd recommend you use the file ID instead.

Hope this helps!

Column “X” has the mails, column “Y” has the name of the PDF files to send (stored in folder “Z” in Drive), how do I send each email with its PDF?

1 Answers