How do I copy the underlying e-mail hyperlink information from a .
 

I have copied a web page into EXCEL that contains numerous hyperlinks labeled
"e-mail". When I click on the hyperlink Outlook opens and the e-mail address
is populated on the "to:" line. I want copy the underlying e-mail address in
the adjacent cell, but can not find a way to do it (all I get is another cell
labeled "e-mail"). I can do it by right clicking on the cell and selecting
"edit hyperlink" and do a copy/paste w/ the e-mail address. However, I have
a ton to do and was hoping to find a quick way to copy the email address,
Thanks for your help.

"John87111" ha scritto nel messaggio


I have copied a web page into EXCEL that contains numerous hyperlinks
labeled "e-mail". When I click on the hyperlink Outlook opens and
the e-mail address is populated on the "to:" line. I want copy the
underlying e-mail address in the adjacent cell, but can not find a
way to do it (all I get is another cell labeled "e-mail"). I can do
it by right clicking on the cell and selecting "edit hyperlink" and
do a copy/paste w/ the e-mail address. However, I have a ton to do
and was hoping to find a quick way to copy the email address, Thanks
for your help.

You can use this UDF I found on www.ozgrid.com:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Just copy it in a module in your Personal.xls and then you can find the
function in your funnctin insert, under UDF.

Hoping to be helpful...

Regards

--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

"Franz" wrote:

"John87111" ha scritto nel messaggio


I have copied a web page into EXCEL that contains numerous hyperlinks
labeled "e-mail". When I click on the hyperlink Outlook opens and
the e-mail address is populated on the "to:" line. I want copy the
underlying e-mail address in the adjacent cell, but can not find a
way to do it (all I get is another cell labeled "e-mail"). I can do
it by right clicking on the cell and selecting "edit hyperlink" and
do a copy/paste w/ the e-mail address. However, I have a ton to do
and was hoping to find a quick way to copy the email address, Thanks
for your help.

You can use this UDF I found on www.ozgrid.com:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Just copy it in a module in your Personal.xls and then you can find the
function in your funnctin insert, under UDF.

Hoping to be helpful...

Regards

--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

Hello Franz, thanks for the answer. I'm not quite sure how to do this. Can you please provide more information about how to make this work?

A million thanks!

"John87111" ha scritto nel messaggio


"Franz" wrote:

"John87111" ha scritto nel
messaggio

I have copied a web page into EXCEL that contains numerous
hyperlinks labeled "e-mail". When I click on the hyperlink Outlook
opens and the e-mail address is populated on the "to:" line. I
want copy the underlying e-mail address in the adjacent cell, but
can not find a way to do it (all I get is another cell labeled
"e-mail"). I can do it by right clicking on the cell and selecting
"edit hyperlink" and do a copy/paste w/ the e-mail address.
However, I have a ton to do and was hoping to find a quick way to
copy the email address, Thanks for your help.

You can use this UDF I found on www.ozgrid.com:

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Just copy it in a module in your Personal.xls and then you can find
the
function in your funnctin insert, under UDF.

Hello Franz, thanks for the answer. I'm not quite sure how to do
this. Can you please provide more information about how to make
this work?


1) If you hade no macro in your Personal.xls workbook, display th macro
toolbar, record an empty macro and choose as position the personal workbook;
2) Enter in the VBA Editor (with ALT + F11) and, eventually, delete the
blank macro you recorded;
3) Copy the macro I posted;
4) Close VBA Editor and go back to Excel;
5) Suppose your data with hyperlinks are in column A, go to column B and
digit, in the cell near the first with hyperlink, suppose B1, an "=" sign;
6) When Insert function windows opens, chose category user defined and
select GetAddress function;
7) select the cell with hyperlinks, i.e. A1 andpress ENTER;
8) copy the function till the and of your list (select the cell containing
the function, place the mouse pointer in the right down corner of the cell,
till the pointer become a little black cross and double click on it).

Hoping to behelpful...

Regards


--
Franz

----------------------------------------------------------------------------------------
per rispondermi traduci InVento in inglese (no maiuscole)
----------------------------------------------------------------------------------------