Can I use Mailto and body to generate an email that includes working hyperlinks

[Andje]

Hello,

I am new to excel and I am unsure if what I am trying to dois possible. I cannot use VBA/macros for this as our sharepoint site does notsupport macro enabled workbooks.
I am using a mailto:formula to create an email, and I have body and subject set up.
=HYPERLINK(CONCATENATE("mailto:"&VLOOKUP(C3,MasterList!$A$1:$BK$76,2,FALSE),"?subject=",C22,"&body=",C24),"Create Email")
All of this changes dynamically based upon selecting anemployee from a list (c3). I would like the auto-generated body (c24) of thatemail to include a hyperlink listed within the workbook.
I am already running into the 280 character limit issue whenI try and make the written message in the body section longer then 160characters.
Basically when I click Create Email, I want something likethis to generate;

Hello,
You are listed on projects
PRJ1
PRJ2
PRJ3
Please follow the links and report your status.


Where PRJ1/2/3 are working hyperlinks that link to asharepoint site, and that list updates automatically depending on theoriginally selected employee. I can obviously store these addresses as a listwithin the document, but all the addresses will look like this;
https://synergi/LOB/EWMP-PMCTE/Lists/SSC%20WLM%20Participation/EditForm.aspx?ID=97&Source=https%3A%2F%2Fsynergi%2Essc%2Dspc%2Egc%2Eca%2FLOB%2FEWMP%2DPMCTE%2FLists%2FSSC%2520WLM%2520Participation%2FBy%2520Employee%2520Name%2Easpx
Many characters.
If this isn’t possible I will have the linksgenerate and displa within the same sheet and we will have to copy them across manually oncethe mailto: generates the email, but that is an inelegant solution.

 

[BiocideJ]

If the e-mail is generated with an HTML body then yes. If not, I don't believe you can put a link in anyway (even manually unless you convert it to HTML body)

Basically, you would just need to put the HTML equivalent of your web address in the body text of C24 via an anchor(link).. for example...

C24

="some text...  Click <A HREF="https://synergi/LOB/EWMP-PMCTE/Lists/SSC%20WLM%20Participation/EditForm.aspx?ID=97&Source=https%3A%2F%2Fsynergi%2Essc%2Dspc%2Egc%2Eca%2FLOB%2FEWMP%2DPMCTE%2FLists%2FSSC%2520WLM%2520Participation%2FBy%252 0Employee%2520Name%2Easpx">this link</A> to preview the site.<BR>BR is a line break."

</a="https:>

 

[mlast1991]

Hi Andje,

Personally I would do the VLOOKUP separately, in another random cell:
As an example in cell C4 do the formula =VLOOKUP(C3,MasterList!$A$1:$BK$76,2,FALSE)

Then do VLOOKUPS for your project links that you want to appear at C24, you may need more that one cell for multiple hyperlinks for display reasons. (I'm not sure how you have configured your hyperlinks currently)

Then for your mailto: you can simply
=HYPERLINK("mailto:"&C4&"?subject="&C22&"&body="&"Hello,"&"%0A"&"You are listed on the current projects"&"%0A"&"%0A"&C24&"%0A"&C25&"%0A"&C26&"%0A"&"%0A"&"Please follow the links and report your status","Create Email")

Hope this helps a little: to save on the amount of characters once again, I would have the text parts as random cells:
"Hello" @ cell M1
"You are listed on the current projects" @cell M2
"Please follow the links and report your status" @ cell M3
"Create Email" @ cell M4 etc.

Kind regards,
mlast1991