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

Andje

New Member
Joined
Sep 16, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
HTML:
="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:>
 
Last edited:

mlast1991

New Member
Joined
Mar 12, 2020
Messages
3
Office Version
365
Platform
Windows, Mobile, Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,953
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top