I am new to this google script. How to send automated emails (consisting a hyperlink) to multiple ad-CodePudding

In the below code I am able to send automated emails and it's partly successful. I am not sure how to get the hyperlink in the email.

//For this code I am able to send it to all the emails but without hyperlink

function testnotif() {
  
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails").activate();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  
  var templatetext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  
  for (var i=2;i<=lr;i  ){
    
    var currentemail = ss.getRange(i, 1).getValue();
    var currenttitle = ss.getRange(i, 3).getValue();
    var currentname = ss.getRange(i, 2).getValue();
    var sheetID = ss.getRange(i, 4).getValue();
    
    var messagebody = templatetext.replace("{name}",currentname).replace("{title}",currenttitle).replace("{sheet}",sheetID);
    var subjectline = "Reminder: "   currenttitle ;
    
    MailApp.sendEmail(currentemail,subjectline,messagebody);
    
  }
  
}

//In this code I am able to send hyperlink but unable to send it to multiple email addresses

function testnotif(){
  
  var ss = SpreadsheetApp.openById('1HVktteGM2wRXNuneZ1VVqtVg3G4WvkeFIcrLq4v-8M4');
  var sheet = ss.getSheetByName("Emails");
  var lr = ss.getLastRow();
  var data = sheet.getRange(lr, 1).getValue();
  
  MailApp.sendEmail({
    to:data,
    subject: "Monthly test",
    htmlBody: "Hi Team,<br><br>" "This is a gentle reminder to start your test and complete it by 25th of this month. You can find your test Dashboard in this "  "<a href=\"https://docs.google.com/spreadsheets/d/1dyjQO51zqBo6DRXB48r7azy_ktFa1LwpIJ55DflDHOA/edit#gid=0\">sheet.</a>" "<br><br>Let me know if you have any questions. " "<br><br>Thank you."
});
}

THANK YOU IN ADVANCE

CodePudding user response：

Just add cc or bcc, whether you want the additional recipients to be visible or not:

MailApp.sendEmail({
        to:data,
        cc: `[email protected], [email protected]`,
        // or hide recipients:
        // bcc: `[email protected], [email protected]`,
        subject: "Monthly test",
        htmlBody: "Hi Team,<br><br>" "This is a gentle reminder to start your test and complete it by 25th of this month. You can find your test Dashboard in this "  "<a href=\"https://docs.google.com/spreadsheets/d/1dyjQO51zqBo6DRXB48r7azy_ktFa1LwpIJ55DflDHOA/edit#gid=0\">sheet.</a>" "<br><br>Let me know if you have any questions. " "<br><br>Thank you."
    });

Edit:

If you have a column of email address you're aiming to put into the cc or bcc field, you will need to convert them into a comma-separated string:

const recipients = SpreadsheetApp.getActiveSpreadsheet()
                                 .getSheetByName(`Emails`)
                                 .getRange(`A2:A`) // Adjust as needed.
                                 .getValues()
                                 .filter(Boolean) // Remove empty rows.
                                 // If the primary recipient is in the last row:
                                 //.slice(0, -1)
                                 .flat()
                                 .join(`, `) // Convert to comma-separated string.

// recipients = "[email protected], [email protected], ..."

MailApp.sendEmail({
        to: data,
        cc: recipients,
        // or hide recipients:
        // bcc: `[email protected], [email protected]`,
        subject: "Monthly test",
        htmlBody: "Hi Team,<br><br>" "This is a gentle reminder to start your test and complete it by 25th of this month. You can find your test Dashboard in this "  "<a href=\"https://docs.google.com/spreadsheets/d/1dyjQO51zqBo6DRXB48r7azy_ktFa1LwpIJ55DflDHOA/edit#gid=0\">sheet.</a>" "<br><br>Let me know if you have any questions. " "<br><br>Thank you."
    });