0

I would like to create a list of emails, which are listed in the column of a sheets.

how can I do this?

example:

column A email 1 email 2 email 3

mailling = column A

MailApp.sendEmail({
     to: mailling,
     subject: "test",
     body: "Test message",
Alvim Silva
  • 1
  • 2
  • Have you explored the Google Apps Script SpreadsheetApp class? You can see documentation [here](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app), and specifically, I would suggest the following chaining of functions to get the array you want: `let mailing = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEET NAME').getRange('A1:A20').getValues().flat()`. Does that help at all? (Also, please change the range, currently `A1:A20`, to whatever range the emails are in!) – Lle.4 Jul 17 '21 at 03:15
  • this responds like creating a list, but it didn't work to send email error: invalid email – Alvim Silva Jul 17 '21 at 14:46
  • To send emails using an array, you need to use `GmailApp.sendEmail(mailing, 'subject', 'content')` instead of `MailApp.sendEmail()`. Does that work better? – Lle.4 Jul 17 '21 at 19:19
  • thanks for answering, but it didn't work for me – Alvim Silva Jul 18 '21 at 14:54

2 Answers2

0

if your emails are in column A starting from row 2:

function getEmails() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const emails = sh.getRange(2,1,sh.getLastRow() - 1, 1).getValues().flat();
  return emails.join(',');
}

function sendEmails() {
  MailApp.sendEmail({to: getEmails(),subject: "test",body: "Test message"});
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • this responds like creating a list, but it didn't work to send email error: invalid email – Alvim Silva Jul 17 '21 at 14:47
  • As your question suggests it's about creating a mailing list. – Cooper Jul 17 '21 at 17:57
  • thanks for replying, this worked for me, but as i tried to insert an attachment i got an error ... `const pdf = sh.getRange(numRows, 3).getValue(); const fileid = pdf.slice(33, 66); const file = DriveApp.getFileById(fileid) MailApp.sendEmail({to: getEmails(),subject: "test",body: "Test message",attachments: [file.next().getAs(MimeType.PDF)]}); }` – Alvim Silva Jul 18 '21 at 14:59
  • actually I messed up using file.next replace with file.getAs and it worked perfectly thank you hehe – Alvim Silva Jul 18 '21 at 15:08
  • I believe that you need to us this version of sendmail `sendEmail(recipient, subject, body, options)` In your version to,subject and body should not be in the object – Cooper Jul 18 '21 at 15:09
  • how can I include in sendMail CC? – Alvim Silva Jul 18 '21 at 15:44
  • https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object) – Cooper Jul 18 '21 at 17:28
  • I replicated the getEmails function to get the emails that go in copy I imagine it's not the best way to do this, but it worked hehe – Alvim Silva Jul 18 '21 at 19:31
0

I think you should check the documentation of the class like suggested in the comments, it has really good features that can improve the way you email. Anyway, here is an example that can help you.


    function sendMail() {
    
    //Each numer means the column of the sheets thats going to grab the value
      var first = 0;
      var second = 1;
      var third = 2;
      
    //In the Column D must have the emails  
      var fourth =3;
      
    //Specifies the HTML document that going to give the structure of the email  
      var emailTemp = HtmlService.createTemplateFromFile("email");
      
    //Tells wich is the sheet to take information
    //in this case the sheets name is "Data Base"
      var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Base");
      
    //Gives the range of the columns that the information is going to be  
      var data = ws.getRange("A1:E" + ws.getLastRow()).getValues();
      
    //This is an optional filter in the Column F so you can filter wich rows to run
      data = data.filter(function(r){ return r[5] == true });
      
    //To use the variables in between the HTML file your going to use the value after "emailTemp." as <?= fr ?>
      data.forEach(function(row){
        
        emailTemp.fr = row[first];
        emailTemp.se = row[second];
        emailTemp.th = row[third];
        var htmlMessage = emailTemp.evaluate().getContent();
        GmailApp.sendEmail(row[fourth], "HEEERE GOES THE SUBJECT OF THE EMAIL", "Your email doesn't support HTML.", {name: "Email", htmlBody: htmlMessage})
       });

Here I used a html template to send emails with variables extracted from other columns. You can check out this github

Francisco Lozada
  • 21
  • 5