mailto problem - too many characters gives me #VALUE error

[lplaforest]

Hi guys,

I've ran into a little problem here.

Here's a formula I use to send emails to recipients depending on a situation :

=HYPERLINK("mailto:"&G3&subject=ALERT 3185&body=CALL%20TYPE%20%3A%0A%E2%80%A2%09"&C3&"%0A%0ASHOP%20%3A%0A%E2%80%A2%09"&D3&"%0A%0AAGENT%20%3A%0A%E2%80%A2%09"&F3&"%0A%0AFILE%20%3A%0A%E2%80%A2%09"&E3&"%0A%0ACOMMENTS%20%3A%0A%E2%80%A2%09"&I3&"%0A&", "OUTBOUND")

G3 (mail to) = name@emailaddress.com

C3 (Call Type) = intern, outbound or Service Center

D3 (Shop #) = #### - a four digits number representing one of our shops

F3 (Agent) = an agent name - could be any number of characters depending on lenght of name

E3 (File) = ###### - a 7 digits file number

I3 (Comments) = any comment the user wish to send concerning this file


The problem I have is that when I enter the info in my excel spreadsheet, at one point the OUTBOUND button switches to #VALUE error, and I guess it's because of how many characters the link can manage...

How can I resolve this using the same mailto method, I would like the automatic generated emails to have all the info I enter in the spreadsheet


Thanks a lot guys

[6StringJazzer]

The problem I have is that when I enter the info in my excel spreadsheet, at one point the OUTBOUND button switches to #VALUE error, and I guess it's because of how many characters the link can manage...

At what point? Once I fixed your errors (you are missing a quote, missing a "?") it worked fine for me. You should not be hitting the character size limit; a string can have 255 characters and your longest has 36. Here is the formula I ended up with. Your formula is hard to read so I put in some breaks to make it easier:

Please Login or Register  to view this content.

[6StringJazzer]

I'm sorry, you are exactly right. The formula doesn't reach a limit but the hyperlink itself appears to be limited to 255 characters. I can reproduce your problem very easily when the entire hyperlink goes to 256. One reason that I hate Microsoft's technical documentation is that it is incomplete; I couldn't find a spec for hyperlink length.

Apparently there is a workaround to this using VBA using the Hyperlinks.Add method.