900 names with e-mail addy's under them

[Scarwalker]

I need to take the e-mail adresses out from under a column and copy them into a seperate collumn, or how to get the e mail adresses and names into Outlook ? That is my ultimate goal. Seperate the single column with the e-mail addy's as links under the names into either 2 seperate columns in Excel or into the Name and Email adress feilds in Outlook. Seperating them would do.
This message was edited by Scarwalker on 2002-11-06 12:58
This message was edited by Scarwalker on 2002-11-06 13:09
This message was edited by Scarwalker on 2002-11-06 13:11

 

[Alriemer]

Scarwalker,

Slick solution. Once you have the data all in the open as text in notepad, it is probably much faster to separate the names and addresses from the HTML in Excel than doing it manually. Find & Replace might work, or Text to Columns. Glad you're getting somewhere.

BTW, I think Steve and Kskinne really did need more clarification. As you'll see from looking around, there are some very skilled people on this board and they will solve all your problems for free so long as you don't jump down their throats. Didn't mean to lecture.

Alriemer
This message was edited by Alriemer on 2002-11-06 17:42

 

[stevebausch]

good riddance

 

[stevebausch]

Now that I am not working, there is time to research this issue.

Here is a code snippet directly from Excel VBA Help:

For Each h in Worksheets(1).Hyperlinks
If Instr(h.Name, "Microsoft")<> 0 Then h.Follow
Next


Since the hyperlinks are a collection, the code to place them in a column to themselves isn't that hard, nor is it hard to place the related name immediately to the side of it.


Here is something I played with:

Sub StripOutHyperlinks()
Dim TheLink As Variant, TheRow As Integer
Const TheCol As Integer = 3
TheRow = 1
For Each TheLink In Sheet1.Columns(1).Hyperlinks
Cells(TheRow, TheCol).Value = TheLink.Name
Cells(TheRow, TheCol + 1).Value = Mid(TheLink.Address, 8) 'Mid() removes 'mailto:'
TheRow = TheRow + 1

Next

End Sub


This code could get quite elaborate; deal with a selected area, or selected areas, deal with multiple columns, multiple sheets, etc.


Edit: Remove smilies
_________________
This message was edited by nateo on 2002-11-08 16:07

 

[Anne Troy]

Dave Hawley has a UDF that does it:

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

First, copy your hyperlinked names to another column just as they are. Run this UDF on it.

Take your FIRST column of hyperlinked names, copy, paste special as RTF into Word (it'll paste as a table). Select all and format the style to the NORMAL style. Then copy/paste back overtop of your hyperlink jobbies in Excel. You should now have a column of names without hyperlinks, and a column of corresponding hyperlinks.

For the record: I understood your first explanation clearly. But people really do mean to help you here, so there's no need to go off the deep end. Next time you ask, you might get someone who gives you a straightforward answer with no questions asked. We all TRY. We're not here to ruin YOUR day.

We'd rather make your day.

 

[Hlatigo]

I really have to admire you guys for being so polite and gentlemenly.

That is one thing that I love about this board...people really want to help here. Great jobs guys!!

My day has always been made when I come here!!

B.