Hyperlink mailto concatenate body new line signature

OxmoPuccino

New Member
Joined
Feb 9, 2017
Messages
5
Hi,

I'm trying to click a hyperlink that will open a new e-mail with Outlook and the e-mail would be all filled with values from Excel cells.
Here's what I got so far:
=HYPERLINK("mailto:" & R1530 & "?subject="& CONCATENATE(A1530," ",B1530) & "&body=" & CONCATENATE("HI","YO",C1530)&"hello", "send e-mail to PM")
I have 2 problems:
  1. I'm trying to create the body of the e-mail based on cell values with multiple lines. I've found this on a forum somewhere, but I don't understand how to make it work with my above formula: "mailto:name@domain.e_t?subject=Header&body=This is the first line%0D%0AThis is the second" the "%0D%0A" seems to create a new line, but I don't know how to integrate it with my formula
  2. The automatic signature is missing from the e-mail. Whenever I click new e-mail in Outlook, my signature is automatically added to the e-mail, but not when I create an e-mail through a hyperlink in Excel.

Anyone have a clue on how to fix any of these problems?

Thank you,
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

OxmoPuccino

New Member
Joined
Feb 9, 2017
Messages
5
Thanks for your link, I noticed that you were putting quotes around %0D%0A which I wasn't, so that problem is fixed:
=HYPERLINK("mailto:" & R1530 & "?subject="& CONCATENATE(A1530," ",B1530) & "&body=" & CONCATENATE("HI", "%0D%0A", "YO",C1530), "send e-mail to PM")
As for the signature, thank you for letting me know it's impossible so I can stop searching for that. Our signatures usually have our company logo, but for this purpose I will just have to accept the fact that the logo will be missing.
Thank you!
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,855
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks for the feedback. Glad you got it working.
 

OxmoPuccino

New Member
Joined
Feb 9, 2017
Messages
5
I though my problems were over, but it seems like there's a maximum amount of "&" I can put in my formula for the body of the e-mail.
Concatenate doesn't seem to help either.
Our signature format has 5 lines and when I add the 3rd line excel return my hyperlink with #value !
Do you have a magic trick for this?
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,855
Office Version
  1. 2013
Platform
  1. Windows
It's never straightforward.
Try bunging the signature lines in a single cell and referencing the cell
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,871
Messages
5,544,787
Members
410,635
Latest member
phoenix7771
Top