Concatenate and Hyperlinks

While creating the grandchild of the mother of all
functions (It's complicated, but not the most complicated)
I've had many road blocks, becuase I'm a general newbie
when it comes to Excel, but not when it comes to figuring
out computer stuff. Especially Microsoft. That said, my
problem is:

This function's job is to compile the information in the
cells into an email, which was an easy task, but one of
the cells it grabs from has a hyperlink. Concatenate
doesn't grab this hyperlink, so I only get the "Friendly
name". (Which, although useful, isn't what I want.) The
other problem is that CONCATENATE() seems to have a limit
of what it can, well, concatenate. I can survive with the
small text that it seems to allow, but is there a way that
this auto e-mail can be as wordy as it needs to be?

Hi Zack,
I know you've got a question somewhere, but don't know what it
is. One stumbling block you appear to have encountered is
extracting the hyperlink email address or URL from a hyperlink.

You can use this function to obtain the hyperlink that you
get if you copy and paste from a web page for instance,
or enter one from right-click, edit hyperlink. More of
similar things on my page
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url

Install the following code in a standard module
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(B14)

Are you trying to write a whole email letter to someone in
a cell (oops sounds like a prisoner). Are you trying to
put a large amount of data into a cell -- I would use rows
and loop through them you can find an example on
John Walkenbach's site.
Sending Personalized Email from Excel, Tip 86, Using Outlook
http://www.j-walk.com/ss/excel/tips/tip86.htm
I used that with send out some email with some customizations
for Email address, name, that person's url that linked to
my site, and an optional comment about person's site
into various places along with the basic canned information about
my old links and my new links to be updated..

You can find additional links on my mailmerg.htm page including
to Ron de Bruin's site where he is rather focused on email
Some Coding examples for use with Sendmail.
http://www.rondebruin.nl/sendmail.htm

As far as the amount of actual data you can put into a cell
and this is from HELP look up Specification as in specification limits

Length of cell contents (text)32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.

You can increase the number of characters displayed by include
some line feeds Alt+Enter to generate som hex 0010 characters

If you are not familiar with the Google Groups (newsgroups) archives
you should be. Some hints for usage in
http://www.mvps.org/dmcritchie/excel/xlnews.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Zack Ferraro" <zferraro@massmutual.com> wrote in message news:074d01c34496$a7d89cc0$a001280a@phx.gbl...
> While creating the grandchild of the mother of all
> functions (It's complicated, but not the most complicated)
> I've had many road blocks, becuase I'm a general newbie
> when it comes to Excel, but not when it comes to figuring
> out computer stuff. Especially Microsoft. That said, my
> problem is:
>
> This function's job is to compile the information in the
> cells into an email, which was an easy task, but one of
> the cells it grabs from has a hyperlink. Concatenate
> doesn't grab this hyperlink, so I only get the "Friendly
> name". (Which, although useful, isn't what I want.) The
> other problem is that CONCATENATE() seems to have a limit
> of what it can, well, concatenate. I can survive with the
> small text that it seems to allow, but is there a way that
> this auto e-mail can be as wordy as it needs to be?

Sorry about the lack of a question. I tend to obfusticate
things.

The HyperLinkAddress() worked wonderfuly. And was almost
exactly what I wanted. The only problem is now I can't
seem to get a "friendly name" for the hyperlink.
=HYPERLINK(HyperLinkAddress($A2),$A2) just gives me
"http://defunkurl.com" where I want it to read "De-Funk"

I'm sure I just have to add one line of code into the code
you gave me, and I'll probably figure it out rather
quickly, I just wanted to say thanks for the fxn.

I'm learning to live with the size limitation, because I
want to work with as little scripting as possible, and do
this all by Formula, which, by j-walk.com's testament,
only seems to like 255 characters.

>-----Original Message-----
>Hi Zack,
>I know you've got a question somewhere, but don't know
what it
>is. One stumbling block you appear to have encountered
is
>extracting the hyperlink email address or URL from a
hyperlink.
>
>You can use this function to obtain the hyperlink that you
>get if you copy and paste from a web page for instance,
>or enter one from right-click, edit hyperlink. More of
>similar things on my page
> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url
>
>Install the following code in a standard module
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>Function HyperlinkAddress(cell)
> On Error Resume Next
> HyperlinkAddress = cell.Hyperlinks(1).Address
> if hyperlinkaddress = 0 then hyperlinkaddress = ""
>End Function
>
>usage:
>=HyperlinkAddress(B14)
>
>Are you trying to write a whole email letter to someone in
>a cell (oops sounds like a prisoner). Are you trying to
>put a large amount of data into a cell -- I would use rows
>and loop through them you can find an example on
>John Walkenbach's site.
> Sending Personalized Email from Excel, Tip 86, Using
Outlook
> http://www.j-walk.com/ss/excel/tips/tip86.htm
>I used that with send out some email with some
customizations
>for Email address, name, that person's url that
linked to
>my site, and an optional comment about person's site
>into various places along with the basic canned
information about
>my old links and my new links to be updated..
>
>You can find additional links on my mailmerg.htm page
including
>to Ron de Bruin's site where he is rather focused on email
> Some Coding examples for use with Sendmail.
> http://www.rondebruin.nl/sendmail.htm
>
>As far as the amount of actual data you can put into a
cell
>and this is from HELP look up Specification as in
specification limits
>
>Length of cell contents (text)32,767 characters.
> Only 1,024 display in a cell; all 32,767 display in
the formula bar.
>
>You can increase the number of characters displayed by
include
>some line feeds Alt+Enter to generate som hex 0010
characters
>
>If you are not familiar with the Google Groups
(newsgroups) archives
>you should be. Some hints for usage in
> http://www.mvps.org/dmcritchie/excel/xlnews.htm
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
>My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
>
>"Zack Ferraro" <zferraro@massmutual.com> wrote in message
news:074d01c34496$a7d89cc0$a001280a@phx.gbl...
>> While creating the grandchild of the mother of all
>> functions (It's complicated, but not the most
complicated)
>> I've had many road blocks, becuase I'm a general newbie
>> when it comes to Excel, but not when it comes to
figuring
>> out computer stuff. Especially Microsoft. That said, my
>> problem is:
>>
>> This function's job is to compile the information in the
>> cells into an email, which was an easy task, but one of
>> the cells it grabs from has a hyperlink. Concatenate
>> doesn't grab this hyperlink, so I only get the "Friendly
>> name". (Which, although useful, isn't what I want.) The
>> other problem is that CONCATENATE() seems to have a
limit
>> of what it can, well, concatenate. I can survive with
the
>> small text that it seems to allow, but is there a way
that
>> this auto e-mail can be as wordy as it needs to be?
>
>
>.
>

OH HOhohoho
Upon further reflection... I don't know how to make an
outlook message with a "Friendly Name". It doesn't seem to
like hyperlinks with text other than the URL. Oh well.
It's okay the way it is. There isn't a problem with your
function. Thanks a bundle!


>-----Original Message-----
>Sorry about the lack of a question. I tend to obfusticate
>things.
>
>The HyperLinkAddress() worked wonderfuly. And was almost
>exactly what I wanted. The only problem is now I can't
>seem to get a "friendly name" for the hyperlink.
>=HYPERLINK(HyperLinkAddress($A2),$A2) just gives me
>"http://defunkurl.com" where I want it to read "De-Funk"
>
>I'm sure I just have to add one line of code into the
code
>you gave me, and I'll probably figure it out rather
>quickly, I just wanted to say thanks for the fxn.
>
>I'm learning to live with the size limitation, because I
>want to work with as little scripting as possible, and do
>this all by Formula, which, by j-walk.com's testament,
>only seems to like 255 characters.
>
>>-----Original Message-----
>>Hi Zack,
>>I know you've got a question somewhere, but don't know
>what it
>>is. One stumbling block you appear to have encountered
>is
>>extracting the hyperlink email address or URL from a
>hyperlink.
>>
>>You can use this function to obtain the hyperlink that
you
>>get if you copy and paste from a web page for instance,
>>or enter one from right-click, edit hyperlink. More of
>>similar things on my page
>> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url
>>
>>Install the following code in a standard module
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>Function HyperlinkAddress(cell)
>> On Error Resume Next
>> HyperlinkAddress = cell.Hyperlinks(1).Address
>> if hyperlinkaddress = 0 then hyperlinkaddress = ""
>>End Function
>>
>>usage:
>>=HyperlinkAddress(B14)
>>
>>Are you trying to write a whole email letter to someone
in
>>a cell (oops sounds like a prisoner). Are you trying
to
>>put a large amount of data into a cell -- I would use
rows
>>and loop through them you can find an example on
>>John Walkenbach's site.
>> Sending Personalized Email from Excel, Tip 86, Using
>Outlook
>> http://www.j-walk.com/ss/excel/tips/tip86.htm
>>I used that with send out some email with some
>customizations
>>for Email address, name, that person's url that
>linked to
>>my site, and an optional comment about person's site
>>into various places along with the basic canned
>information about
>>my old links and my new links to be updated..
>>
>>You can find additional links on my mailmerg.htm page
>including
>>to Ron de Bruin's site where he is rather focused on
email
>> Some Coding examples for use with Sendmail.
>> http://www.rondebruin.nl/sendmail.htm
>>
>>As far as the amount of actual data you can put into a
>cell
>>and this is from HELP look up Specification as in
>specification limits
>>
>>Length of cell contents (text)32,767 characters.
>> Only 1,024 display in a cell; all 32,767 display in
>the formula bar.
>>
>>You can increase the number of characters displayed by
>include
>>some line feeds Alt+Enter to generate som hex
0010
>characters
>>
>>If you are not familiar with the Google Groups
>(newsgroups) archives
>>you should be. Some hints for usage in
>> http://www.mvps.org/dmcritchie/excel/xlnews.htm
>>---
>>HTH,
>>David McRitchie, Microsoft MVP - Excel [site changed
>Nov. 2001]
>>My Excel Pages:
>http://www.mvps.org/dmcritchie/excel/excel.htm
>>Search Page:
>http://www.mvps.org/dmcritchie/excel/search.htm
>>
>>"Zack Ferraro" <zferraro@massmutual.com> wrote in
message
>news:074d01c34496$a7d89cc0$a001280a@phx.gbl...
>>> While creating the grandchild of the mother of all
>>> functions (It's complicated, but not the most
>complicated)
>>> I've had many road blocks, becuase I'm a general newbie
>>> when it comes to Excel, but not when it comes to
>figuring
>>> out computer stuff. Especially Microsoft. That said, my
>>> problem is:
>>>
>>> This function's job is to compile the information in
the
>>> cells into an email, which was an easy task, but one of
>>> the cells it grabs from has a hyperlink. Concatenate
>>> doesn't grab this hyperlink, so I only get
the "Friendly
>>> name". (Which, although useful, isn't what I want.) The
>>> other problem is that CONCATENATE() seems to have a
>limit
>>> of what it can, well, concatenate. I can survive with
>the
>>> small text that it seems to allow, but is there a way
>that
>>> this auto e-mail can be as wordy as it needs to be?
>>
>>
>>.
>>
>.
>

Hi Zack,
Thought that was a legitimate site, that I needed, and I actually
think that there is such a site. Too bad there isn't some
automatic registry based on META tags., but I guess it would
be too open to abuse.

A2: De-Funk [with hyperlink of http://defunkurl.com]

=HYPERLINK(HyperLinkAddress($A2),$A2)

worked for me comes up with message cannot locate server
Change to a legitimate site and if works fine has the correct
link and has the correct display.

BTW, did not find such a site but ran across
HTML Broken Link Finder/Fixer
http://mindprod.com/projhtmlbrokenlink.html
and practically everything it describes is in xenu mentioned at
the bottom of that article -- just installed xenu yesterday, and contrary
to the indication in the above article, it did identify bad links within a page.
I discovered though that you want to run Xenu 1 level deep and that
you want to look at the options so you report by (web) page instead of
by URL. Doesn't fix links but does recognize redirections, though
not mine -- probably because I have a 15 second delay instead of
immediate redirection. I have enough to work on just on the broken ones.
Find broken links on your site with Xenu's Link Sleuth (TM)
http://home.snafu.de/tilman/xenulink.html


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Zack Ferraro" <zferraro@massmutual.com> wrote in message news:02b801c344c5$c5f2dde0$a501280a@phx.gbl...
> Sorry about the lack of a question. I tend to obfusticate
> things.
>
> The HyperLinkAddress() worked wonderfuly. And was almost
> exactly what I wanted. The only problem is now I can't
> seem to get a "friendly name" for the hyperlink.
> =HYPERLINK(HyperLinkAddress($A2),$A2) just gives me
> "http://defunkurl.com" where I want it to read "De-Funk"

In that case you didn't even need the function.


"Zack Ferraro" <zferraro@massmutual.com> wrote in message news:0cb401c344c7$3d167b10$a001280a@phx.gbl...
> OH HOhohoho
> Upon further reflection... I don't know how to make an
> outlook message with a "Friendly Name". It doesn't seem to
> like hyperlinks with text other than the URL. Oh well.
> It's okay the way it is. There isn't a problem with your
> function. Thanks a bundle!