Email multiple addresses contained in one cell

[chocho...@gmail.com]

Hello, I am trying to use app script to email multiple addresses in one cell of a spreadsheet.  Some rows of my spreadsheet contain only one address in the cell, but others need to go to multiple directors.  The below snippet works when there is only one email address in the director cell, but not when they are two, which I've separated with a comma:

dire...@domain.org, dire...@domain.org

Here's the snippet:

for (var i = 0; i < data.length-1; ++i) {
   var row = data[i];
   var link = row[28];
   var emailAddress = row[29];
   var director = row[33];
   Logger.log(director);
   var manager = row[34];
   Logger.log(manager);
   var htmlBody2 = "Dear Provider,";
   var emailSent = row[30];
  if (emailSent === '' && emailAddress !== '')
     {GmailApp.sendEmail(emailAddress, subject, '', { htmlBody: htmlBody2, cc: 'em...@thisaddress.org'+','+ manager + ','+ director});

Can anyone help with how to set up either the script or the cell to have it send to both addresses in one cell?

Thank you!
     

[chocho...@gmail.com]

Just bumping this post to see if anyone can help?

[Clark Lind]

See if this makes a difference. I usually build my 'To:' and 'Cc:' fields outside the email object which makes it easier to read and understand 2-3 years from now.. your future self will appreciate it! lol

for (var i = 0; i < data.length-1; ++i) {  //open for

   var row = data[i];
   var link = row[28];
   var emailAddress = row[29];
   var director = row[33];
   Logger.log(director);
   var manager = row[34];
   Logger.log(manager);
   var htmlBody2 = "Dear Provider,";
   var emailSent = row[30];

   var cc = `em...@thisaddress.org, ${manager}, ${director}`;   //or even:     var cc = `em...@thisaddress.org, ${ row[34] }, ${  row[33]  }`;
  if (emailSent === '' && emailAddress !== '') {            //open if
    GmailApp.sendEmail(emailAddress, subject, '', {  //open object  //I assume the subject is defined above somewhere
    htmlBody: htmlBody2,
    cc: cc
       }) //close object
      }   //close if
  } //close for

[Clark Lind]

You may need to add quotes around the emails if the above doesn't work:

var cc = ` "em...@thisaddress.org", "${manager}", "${director}" `;   //or even:     var cc = `"em...@thisaddress.org", "${ row[34] }", "${  row[33] }" `;

[p]

Thanks, Clark, for your response. 

This still doesn't work for what I am trying to do.

Row 33 could be, eg, thisdi...@domain.org, or it could be thisdi...@domain.org, thisothe...@domain.org

The script works fine with just one director, thisdi...@domain.org, but stops working when I add another director in the spreadsheet cell.  

Does that make better sense?  I imagine I could try putting the other director in a different row, but wanted to know if there was some one to have the script email both directors in one cell and how to do that.

Thanks for any help you can provide!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/46e7c4ad-ff04-43d9-80f5-53d659788cb4n%40googlegroups.com.

[Jon Couch]

I don't know if this helps but in my applications the addresses are passed to bcc as "thisdi...@domain.org, thisothe...@domain.org". I think I discovered that the entire list of addresses need to be within a single set of quotes. 

I also have a routine that concatenates two addresses as 

var combinedEmail = emailOne + "," + emailTwo; 

I don't know if either of these things help. I haven't been following the thread so you may have already tried these.

Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAARs47MsLZb3thFNEez7C2NYQNFwYWLwvGc--uKXu1M8StB%2B1g%40mail.gmail.com.

[Clark Lind]

Can you paste the sanitized results of a console.log(row[33], row[34])  for single and multiple email addresses in row[33]?  Jon is right; you only want to pass on a single string like maybe:
var cc =  "em...@thisaddress.org"

cc = cc.concat(", ", row[33]);
cc = cc.concat(", ", row[34]);

[p]

Thanks, Joh, but I am pulling the two email addresses from one cell in the spreadsheet.  The script works fine with only one email address.  I'm just not sure how to pull cells that contain two addresses into the script and have the script not throw an error (read them as two addresses).

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADjEj85ePBEJyoBRBsioXWE-T9QvvjENkyPqe2xsE%3DCqsRzsNw%40mail.gmail.com.

[Phillip Bainbridge]

Hi

I'm not seeing anywhere in your code that you need to account for splitting the contents of the cells containing multiple email addresses - you will need to do this first before you can use them. Example: https://www.pbainbridge.co.uk/2019/09/split-single-cell-contents-with-new-line.html

Kind regards

Phil