Help required with hyperlink function

Gazza74

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

coraltea

New Member
Joined
Jul 1, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,949
Office Version
  1. 2016
Platform
  1. Windows
what do you get back if you just use
Rich (BB code):
=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:
Rich (BB code):
=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

New Member
Joined
Jul 1, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,188
Messages
5,570,758
Members
412,340
Latest member
nikitesh95
Top