Using Apps Script to automate sending multiple emails from a google sheet, each with a different specific pdf attachment stored in Google Drive

Question

This works to send automated emails from the same Google sheet, but when I added lines 9, 18 and 21(attachments part) it keeps returning

Exception: Cannot retrieve the next object: iterator has reached the end. SendMails @ Code.gs:21

Google Drive API has been added

 1  function SendMails() {

 3  var wrkBk = SpreadsheetApp.getActiveSpreadsheet();
 4  var wrkShtEmailIDs= wrkBk.getSheetByName("Email_ID");
 5  var wrkShtMessage= wrkBk.getSheetByName("Mail_Details");
 6  var subject = wrkShtMessage.getRange('A2').getValue();
 7  var message = wrkShtMessage.getRange('B2').getValue();
 8
 9  var folder = DriveApp.getFolderById('xxxxxxxxxxxxxxxxxxxxxx');
10
11
12  for (var i=41;i<43;i++){
13
14  var fname = wrkShtEmailIDs.getRange('A' + i).getValue();
15  var lname = wrkShtEmailIDs.getRange('B' + i).getValue();
16  var emailAddress = wrkShtEmailIDs.getRange('C' + i).getValue();
17  var fileName = wrkShtEmailIDs.getRange('D' + i).getDisplayValue();
18  var file = folder.getFilesByName(fileName);
19  var finalmsg = "";
20  finalmsg = fname + "\n" + message;
21 MailApp.sendEmail(emailAddress, subject, finalmsg, {attachments:[file.next().getAs(MimeType.PDF)]})
22  }
23  }

I don't really know what else to try, I initially was using .getFolderByName in place of getFolderById on line 9, but got the same result.

Answer 1

Your fileName isn't gonna match with what's in the folder because you are using getDisplayValue() which will return the cell value with all its formatting. Simply use getValue() and also check if the file even exists.

function SendMails() {
  const wrkBk = SpreadsheetApp.getActiveSpreadsheet();
  const wrkShtEmailIDs = wrkBk.getSheetByName("Email_ID");
  const wrkShtMessage = wrkBk.getSheetByName("Mail_Details");
  const subject = wrkShtMessage.getRange('A2').getValue();
  const message = wrkShtMessage.getRange('B2').getValue();

  const folder = DriveApp.getFolderById('xxxxxxxxxxxxxxxxxxxxxx');

  for (let i = 41; i < 43; i++) {
    let fname = wrkShtEmailIDs.getRange('A' + i).getValue();
    let lname = wrkShtEmailIDs.getRange('B' + i).getValue();
    let emailAddress = wrkShtEmailIDs.getRange('C' + i).getValue();
    let fileName = wrkShtEmailIDs.getRange('D' + i).getValue();
    let files = folder.getFilesByName(fileName);

    if (files.hasNext()) {
      let file = files.next();
      let finalmsg = fname + "\n" + message;
      MailApp.sendEmail(emailAddress, subject, finalmsg, {
        attachments: [file.getAs(MimeType.PDF)]
      });
    } else {
      Logger.log('No file found with the name: ' + fileName);
    }
  }
}

I also suggest using GmailApp class in case you need more options.

P.S. I couldn't stand the use of var so you are welcome :-)