multiple e-mail links over 255 characters.

[MasterBash]

I have a list with over 20 hyperlinks to send e-mails. All the links send to the same contacts, but only one single word changes in the subject and body text.



Cat
Send e-mail to CAT


Dog
Send e-mail to DOG


Pig
Send e-mail to PIG



Problem is, the contact list is quite long.


=HYPERLINK("mailto:"&I$1&"?Subject="&$I$2&" "&A1&"&body=Hello, %0d%0a%0d%0a"&$I$3&" "&$A1;"Send e-mail to "&A1&"")
I$1 being the list of e-mail, which is too long.
I$2 being the subject + A1, which is "Requesting... cat" in this case.
I$3 being the body text + A1, which is "Please provide... cat" in this case.

I got to do this for the entire list (20+ e-mails).

What would be the easiest way to accomplish what I am trying to do ? All e-mail contacts in the list all ends with the same @something.com. Also, it appears that when I click one of the links, outlook does not add my signature. When I create a new e-mail or reply directly from Outlook, it does add my signature, but not when using Excel.

Thank you !

[arnelgp]

maybe use Outlook automation.

[MasterBash]

Unfortunately, we can't. That sheet contains some sort of verification to make sure we do not send the wrong e-mail. That one word difference really matters.

[MasterBash]

I wonder what is the easiest way of doing this.

=HYPERLINK("mailto:"&I$1&"?Subject="&$I$2&" "&A1&"&body=Hello, %0d%0a%0d%0a"&$I$3&" "&$A1;"Send e-mail to "&A1&"")

Right now this is what I am using, but there are over 255 characters and it gives me value error.
I believe it would be easier to make every text that has a "Send e-mail to..." a clickable text with vba to open outlook with the correct e-mail address, subject, body text for that specific text. I am not sure if a signature would be possible. I would have to ditch the hyperlink text altogether, I believe.

Thoughts ?

[Aussiebear]

I]
Right now this is what I am using, but there are over 255 characters and it gives me value error.

Microsoft 365 has the following limitations

Microsoft Excel has a limitation on the number of characters that can be entered in a cell. If you have worksheets with large amount of text data, you may find the following information helpful.


The total number of characters that a cell can contain is 32,767.
A cell can only display 1,024 characters. At the same time, the Formula bar (https://www.ablebits.com/office-addins-blog/formula-bar-excel/) can show all 32,767 symbols.
The maximum length of a formula is 8,192 characters in Excel 2007 and higher (1,014 in Excel 2003).

Please consider the facts when you are going to merge or import data from an external source.


I'm not sure what you mean by impling that you have over 255 characters. Are you able to test the hyper link by splitting the sections and testing them alone to ensure they are not responsible for creating the "value" error.

You provided this twice so Im assuming its not a typo when you injected the "?" character. Should there not be a comma in front of "Subject"?

=HYPERLINK("mailto:"&I$1&"?Subject="&$I$2&" "&A1&"&body=Hello, %0d%0a%0d%0a"&$I$3&" "&$A1;"Send e-mail to "&A1&"")

[MasterBash]

It is correct, it is only when I add my 3rd/4th e-mail address that I get a value error. It appears that hyperlink() are limited to 255 characters.
Insert -> Link does not appear to have the same limitation, but then I am not sure how to add a body to the e-mail.