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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top