A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel Contains E-mail Addresses



 
 
Thread Tools Display Modes
  #1  
Old March 6th, 2009, 09:11 PM posted to microsoft.public.excel.newusers
Carl
external usenet poster
 
Posts: 473
Default Excel Contains E-mail Addresses

I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl

  #2  
Old March 7th, 2009, 07:34 AM posted to microsoft.public.excel.newusers
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default Excel Contains E-mail Addresses

Can you give a few examples of the hyperlinks?

Do they have mailto: before the email address?
If yes, then try this in B1 (assuming that you have the address in A1)
=RIGHT(A1,LEN(A1)-7)
if this works then you can copy it down...


--------
If this is what you wanted then press the ''''YES'''' button (if you see it)


"Carl" wrote:

I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl

  #3  
Old March 7th, 2009, 12:48 PM posted to microsoft.public.excel.newusers
Ron de Bruin
external usenet poster
 
Posts: 2,861
Default Excel Contains E-mail Addresses

This will add the addess in the column next to it
Be sure that there is a empty column next to the hyperlimks column

Sub ShowLinks()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Mid(hlnk.Address, 8, 100)
Next
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Carl" wrote in message ...
I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl

  #4  
Old March 7th, 2009, 04:44 PM posted to microsoft.public.excel.newusers
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default Excel Contains E-mail Addresses

Ron,

I tried to use hlink.Address in a macro but it did not work for links
inserted using formulas like;
","ABC Mail Id")

I tried to come up with a code which will take care of that for all
addresses and came up with the following;
(It assumes all hyperlinks are in Col A and writes out in Col B)

Sub test()

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = 1 To lastRow

endPos = InStr(13, Cells(i, 1).Formula, ",")

If (endPos 13) Then
lastpos = Len(Cells(i, 1).Formula)
Cells(i, 2) = Mid(Cells(i, 1).Formula, 13, (endPos - 14))
Else
If (Left(Cells(i, 1), 7) = "mailto:") Then
Cells(i, 2) = Right(Cells(i, 1), (Len(Cells(i, 1)) - 7))
Else
Cells(i, 2) = Cells(i, 1)
End If

End If
If (Left(Cells(i, 2), 7) = "mailto:") Then
Cells(i, 2) = Right(Cells(i, 2), (Len(Cells(i, 2)) - 7))
End If
Next
End Sub


"Ron de Bruin" wrote:

This will add the addess in the column next to it
Be sure that there is a empty column next to the hyperlimks column

Sub ShowLinks()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Mid(hlnk.Address, 8, 100)
Next
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Carl" wrote in message ...
I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl


  #5  
Old March 7th, 2009, 06:41 PM posted to microsoft.public.excel.newusers
Carl
external usenet poster
 
Posts: 473
Default Excel Contains E-mail Addresses

Thanks Ron, works perfectly. And is easy to implement for a VB neophyte.


"Ron de Bruin" wrote:

This will add the addess in the column next to it
Be sure that there is a empty column next to the hyperlimks column

Sub ShowLinks()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Mid(hlnk.Address, 8, 100)
Next
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Carl" wrote in message ...
I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl


  #6  
Old March 7th, 2009, 06:48 PM posted to microsoft.public.excel.newusers
Ron de Bruin
external usenet poster
 
Posts: 2,861
Default Excel Contains E-mail Addresses

Hi Sheelo

Maybe this is easier

Sub ShowLinks2()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Mid(hlnk.Address, 8, InStr(1, hlnk.Address, "%") - 8)
Next
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sheeloo" Click above to get my email id wrote in message ...
Ron,

I tried to use hlink.Address in a macro but it did not work for links
inserted using formulas like;
","ABC Mail Id")

I tried to come up with a code which will take care of that for all
addresses and came up with the following;
(It assumes all hyperlinks are in Col A and writes out in Col B)

Sub test()

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = 1 To lastRow

endPos = InStr(13, Cells(i, 1).Formula, ",")

If (endPos 13) Then
lastpos = Len(Cells(i, 1).Formula)
Cells(i, 2) = Mid(Cells(i, 1).Formula, 13, (endPos - 14))
Else
If (Left(Cells(i, 1), 7) = "mailto:") Then
Cells(i, 2) = Right(Cells(i, 1), (Len(Cells(i, 1)) - 7))
Else
Cells(i, 2) = Cells(i, 1)
End If

End If
If (Left(Cells(i, 2), 7) = "mailto:") Then
Cells(i, 2) = Right(Cells(i, 2), (Len(Cells(i, 2)) - 7))
End If
Next
End Sub


"Ron de Bruin" wrote:

This will add the addess in the column next to it
Be sure that there is a empty column next to the hyperlimks column

Sub ShowLinks()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Mid(hlnk.Address, 8, 100)
Next
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Carl" wrote in message ...
I inherited an Excel 2003 file with 985 email address in them. The problem is
that they exist in Excel as hyperlinks, which when clicked on launch a new
Outlook email.
I need to send all these email address to an outside vendor. How can I
convert all these hyperlinks, displayed as John Doe in Excle, to a column of
email address (such as ).
I posted in an Outlook group and an Outlook MVP thought I might be better
posting this question here.
Thank you
Carl


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.