Hyperlink formulae goes to Value Error (too many characters )

[antonio32]

Hi All

I have the following formulae

=HYPERLINK("mailto:"&Y7&";"&Z7&"?cc="&AA7&"&subject="&AB7&"&body="&X7,"Send Email")


Formulae shows value error when is bigger then 220 character approximately

Is there a way to make it work for 500 characters or more without vba or do i need Vba?

any solution? I looked everywhere i cannot make it work

thanks in advance

Antonio

[vba_php]

Antonio,

There are multiple ways you can follow a hyperlink to a local address or an internet address using code. However, to answer your question about length of characters and counts that are accepted inside of cells, you should look it up on Google because Microsoft obviously has an article that addresses that if they have an article that addresses the maximum row count in an Excel workbook and maximum memory limits of the program itself. personally I've never looked it up because I've never seen this type of question before. But more than likely if the error message actually says the words quote value air quote, which of course you did not specify, then more than likely there's a problem inside your functions and you have not reached the maximum character count inside a cell yet. That only makes perfect sense if the air actually says those words because of value air has nothing to do with limits at least I don't think it does.

[antonio32]

HI thanks

for your reply

I attached the file

perhaps it is easier to understand

thanks in advance

Antonio

[davsth]

I see no relationship between your attached file and the question. The only hyperlinks are to 3 email addresses nothing with the concatenation of cells

[antonio32]

There are 3 emails, (1 of them in CC), the body of the email and the subject.

if you try to insert more carachters into the body (Cell B3) it gets to a point that the formulae goes to #VALUE!. I think is 228 caracthers the max the formulae can take in all cells combined in my example.

Or perhaps my formulae is not well written
the goal is to be able to send an email from excel but it doesn't work when the body of the text for instance is too long.

i hope now is more clear . please let me know if not. thanks

[vba_php]

according to this, you might have gone over the limits:

https://support.microsoft.com/en-us/...7-269d656771c3

answer seen from here:

https://answers.microsoft.com/en-us/...f-97446bcb5e87

but then again, see the attached file. it makes no sense whatsoever. and that is typical of ms office products because MS doesn't pay attention to them.
they make no money for MS. they are good programs, but this is classic in terms of inconsistencies. The only thing that can be drawn from the test I
am showing you with this file is that the person giving the answer in the above link is wrong, and the true limit is the Total number of
characters that a cell can contain, NOT Column width like he said it was. but considering the attached book and the images, that can't be
right either because my test only uses 2460 characters and it errors out!

[antonio32]

What i dont get if he true limit is the Total number of
characters that a cell can contain. Why my formulae is failing at 229 caracters (when combining all cells) instead of working fine until you get to 256? please see atached.

When LEN is greater than 228 carachters gives error value , should be working up to 256? thanks again. A

[vba_php]

your length is 256, which IS over the limit! you're using the wrong function to detect the limit. you use this:

Please Login or Register  to view this content.

not this:

Please Login or Register  to view this content.

sum has nothing to do with length of a value. LEN() is NOT SUM(). SUM() is math. LEN() is non-math. and the problem obviously
is that you're over the limit. I took &B3 out of your cell and I got what you see in the images below. so there ya go. now you know how to fix it.

[antonio32]

Many thanks for the LEN explanation I get it now very useful now I understand I was already surpassing 256 hence the Error.

However you removed B3 which will contain the body text in the email. Hence there is not Text in my email anymore

The whole point of my question is to be able to go beyond 256 with hyperlink.[/B]

In order to be able to send an outlook email automatically that contains

a) Email addresses in "to" and "cc",
b) Email subject
c) And of course a body text in this case contained in cell B3

Sorry if my question was unclear. I hope now is more clear. how can we solve it? thanks again. Antonio

[vba_php]

there are many ways. this would be the most simplistic, I would think:

Please Login or Register  to view this content.

see book.

https://docs.microsoft.com/en-us/off...ollowhyperlink