Concatenation giving #VALUE error?

  • Thread starter Thread starter denisecook
  • Start date Start date
D

denisecook

I have a column of email addresses, a cell with "mailto:" in it, and
cell with "?subject=blahblah" in it. I am using concatenation to pul
all of the email addresses into one cell, and then the HYPERLIN
formula to turn that cell into a link that will open email an
basically populate the To and Subject lines. This works fine BUT fo
some reason, after I enter more than 8 email addresses in the column
my hyperlink cell turns into a #VALUE error. The concatenation is se
for about 20 rows (cells) and if you hover over the link (even as i
appears as #VALUE) it will show the string containing all of th
emails, but if you click it, it won't do anything because of the error
Then I go back and erase some of the emails until I get back down to
or so, and the #VALUE instantly changes back to the correct hyperlink
:confused:

Thanks in advance
 
Denise,

I'm using Excel 2002 and I was able to concatenate 13 names with n
problem.

For the CONCATENATE formula in B19, I used:
=CONCATENATE(b2, ";", b3, ";", b4, ";", b5) //all the way to B14

For the HYPERLINK formula in B21, I used
=HYPERLINK(a1&B19&b1)

In a1 was Mailto:
a20 contained the CONCATENATE function
b1 contained ?Test msg
cells b2...b14 contained email addresses

This worked fine for me. It displayed the hyperlink while I wa
hovering and when it was clicked it brought up my Outlook with th
fields filled in as appropriate
 
Thanks, umfaninnc but that's still not working for me. I am using Exce
2000 on my desktop and 2003 on my laptop and I can't get it to work o
either. I noticed you used ";" as your separator whereas I used ","
I wonder if that has anything to do with it? Although I did try i
exactly as you instructed. Back to the drawing board! :
 
...
....
for some reason, after I enter more than 8 email addresses in the
column, my hyperlink cell turns into a #VALUE error. The
concatenation is set for about 20 rows (cells) and if you hover over
the link (even as it appears as #VALUE) it will show the string
containing all of the emails, but if you click it, it won't do anything because of the error.
Then I go back and erase some of the emails until I get back down to
8 or so, and the #VALUE instantly changes back to the correct
hyperlink.
Click to expand...

Hyperlinks may be no more than 255 characters.
 
Wow, I didn't know that. I figured it had something to do with th
length but you just confirmed it for me!

Well, do you think there is a macro that I could use to grab all of th
email addresses in Column X (which would be in a range, say X1:X30)
concatenate them separated by a comma, and then make them into a mailt
link? I need to do this all on the same sheet because the workshee
consists of 6 sheets and each sheet will have a different set of email
in Column X. The idea is that the user can just click a link or butto
and generate the emails to the necessary individuals.

Thanks a bunch
 
Wow, I didn't know that. I figured it had something to do with the
length but you just confirmed it for me!

Well, do you think there is a macro that I could use to grab all of the
email addresses in Column X (which would be in a range, say X1:X30),
concatenate them separated by a comma, and then make them into a mailto
link? I need to do this all on the same sheet because the worksheet
consists of 6 sheets and each sheet will have a different set of emails
in Column X. The idea is that the user can just click a link or button
and generate the emails to the necessary individuals.
Click to expand...

This has been addressed in the past in the .programming newsgroup, and you
should start by looking through the Google Groups archives in that newsgroup. I
recall that either Dick Kusleika or Dana DeLouis wrote something about using
Automation to control Outlook from Excel in order to do this. If you don't find
anything suitable, that's still the better newsgroup in which to follow up.
 
Hi Denise,
Exactly what are you using for your HYPERLINK formula
you may be attempting a string over 255 bytes so let's
see what limits might apply according to
HELP --> Specification Limits

This is probably the one that affected me:
Length of formula contents --- 1,024 characters
that is content of the formula itself. Do you concatenations
outside to reduce formula size.
 
Hi Denise,
I looked up some earlier testing of mine and the HYPERLINK
was limited to 255 as Harlan indicated. I forgot that
most of what I had been doing at the time involved running
a macro to generate the letter.
 
Back
Top