Search code examples
google-apps-scriptgoogle-sheetsgmail-apibulk-email

BulkSend emails with respective attachments in Gmail using appscript

I would like to send bulk google mails with attachments using a spreadsheet. In this spreadsheet, i have put the email address, the content template, and the respective attachment urls.

While i try and execute the below code, everything went well except for the line item #21 "attachments.push(file.getAs(MimeType.PDF));", which shows up an error. While i remove the line#21 and execute, i can send the bulk emails with a HTML Attachment instead of a PDF which contains virus and it is marked as spam.

Can someone help me with the correct code to execute the same and send a pdf directly instead of HTML

var EMAIL_SENT = "EMAIL_SENT";
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  
var numRows = 3; 
var blobs = [];

var dataRange = sheet.getRange(startRow, 1, numRows, 5)

var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[2]; 
var subject = "Attachments";       
var message = row[4];       
var options = {muteHttpExceptions:true};
var attachments = UrlFetchApp.fetch(row[3], options).getBlob();     
attachments.push(file.getAs(MimeType.PDF));
var emailSent = row[0];     
if (emailSent != EMAIL_SENT) {  
  
  GmailApp.sendEmail(emailAddress, subject, message, {attachments: attachments,});
  
  sheet.getRange(startRow + i, 1).setValue(EMAIL_SENT);
 
  SpreadsheetApp.flush();
    }
   }
  }
Solution

  • Modification points:

    • When I saw your script, I thought that the reason of your issue at attachments.push(file.getAs(MimeType.PDF)) is due to var attachments = UrlFetchApp.fetch(row[3], options).getBlob();. At this line, attachments is the HTTPResponse object. For this, your script of attachments.push(file.getAs(MimeType.PDF)) try to put file.getAs(MimeType.PDF) to attachments. By this, the error occurs. And, file is not declared.
    • And, when I saw your Spreadsheet, I noticed that the URL of the file is the link of the publicly shared file like https://drive.google.com/file/d/###/view?usp=sharing. From this situation, I thought that those files might be publicly shared or your files. If my understanding is correct, I thought that you might be able to be directly retrieved the file using Drive Service of DriveApp and the method getBlob. Furthermore, as you can see in GmailApp: sendEmail, the attachments parameter inside of the option parameter works with BlobSource variables, the same that returns getBlob.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 3;
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[2];
    var subject = "Attachments";
    var message = row[4];
    var fileId = row[3].split("/")[5];
    var emailSent = row[0];
    try {
      var file = DriveApp.getFileById(fileId);
      if (emailSent != EMAIL_SENT) {
        GmailApp.sendEmail(emailAddress, subject, message, { attachments: [file.getBlob()] });
        sheet.getRange(startRow + i, 1).setValue(EMAIL_SENT);
        // SpreadsheetApp.flush(); // I think that this might not be required to be used.
      }
    } catch(e) {
      console.log("File of " + row[3] + "cannot be retrieved. Error message is " + e.message);
    }
  }
}

    Note:

    • In above script, when the file of URL (row[3]) cannot be retrieved, that URL can be seen at the log.

    Reference: