Help required with hyperlink function

[Gazza74]

Hi folks,

Hopefully im on the right place to sort out a problem ive been having. Basically im trying to create a daily report template that will be sent out at the end of the shift to multiple people within my company. Im using the hyperlink formula to do this and although ive got it partiall working im still experiencing the following problems,

When i click the hyperlink to send the report only one email address appears in the email even though ive included the complete range in the formula, is there something i can do to include all email addresses in the list ive created(around 14 i think).

I cant seem to capture all the cells involved in the report template if i include all in the range it doesnt work, the subject is no problem but this is only 1 cell.

The formula im usong is as below,

=HYPERLINK("mailto:"&A4:A19&"?subject="&title&"&body="&B1,"send report")

Thanks in advance for anyone that can help.

 

[coraltea]

I'm guessing it isn't working because the individual emails are not separated by a comma. Try this:

=HYPERLINK("mailto:"& TEXTJOIN(",",TRUE,A4:A19)& "?subject="&title&"&body="&B1,"send report")

The TEXTJOIN function will take the range you select and string them together with the designated delimiter, "," in this case.
I haven't tested it but give it a shot and see if it works for you.

 

[Gazza74]

hh

I'm guessing it isn't working because the individual emails are not separated by a comma. Try this:

=HYPERLINK("mailto:"& TEXTJOIN(",",TRUE,A4:A19)& "?subject="&title&"&body="&B1,"send report")

The TEXTJOIN function will take the range you select and string them together with the designated delimiter, "," in this case.
I haven't tested it but give it a shot and see if it works for you.

Hi many thanks for your suggestion which i have now tried unfortunatly i doesnt seem to work, is there anything else you can think of that i can try?

Thanks in advance

 

[pjmorris]

what do you get back if you just use

=TEXTJOIN(",",TRUE,A4:A19)

I only have Excel 2016 so can't test.

What is the text shown in the cell with the Hyperlink? - if its anything other than 'send report' I believe its because the function returns an error which means it doesn't understand some element.

I tried this:

=HYPERLINK("mailto:"& A4 & "," & A5 & "," & A6&"?subject="&title&"&body="&B1,"send report")

which worked, but is a bit tedious for the list of addresses you have.

HTH

 

[coraltea]

I agree with PJ. Check to see if TEXTJOIN is even an available function in your version. If not you will have to go through the work of creating the address list with commas. Shouldn't be a big deal if you aren't constantly changing the number of people you are mailing to.