turn large email list into hyperlinks

[John Caines]

Hello all,
Hope someone can help me here.

I have a large list of email addresses, approx 62,000.
All I want to do is turn them into hyperlinks.

All the list is in Col A from A3 downwards.
I'm using excel 2007.

I do have a macro that I thought might work
(I think it's a VOGII special), sorry VOGII if I'm wrong

Sub AllHyperlinksConverter()
Dim lastrow As Long, i As Long, web As String, t As Date
t = Now()
Application.ScreenUpdating = False
Application.Calculation = xlManual
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
For i = 4 To lastrow
    web = Cells(i, 3).Value
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=web, TextToDisplay:=web
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "Hyperlink Converting Run Time: " & Format(Now() - t, "hh:mm:ss")
End Sub

Now I know very little on coding (Just above zero actually),
But I just thought this might work as it works for http type URLs.
Thought it might convert emails also.

I've just tried running it and it didn't work
Thing is, I'm sure this macro is set to run from cell C4.
I've tried changing a few numbers etc, but I just must be changing the wrong parts
as I can't get this to work with data starting from cell A3

Is there anyway to alter the code so this kind of macro can convert emails
into active (Blue hyperlinks)?
Hope someone can advise
many thanks
John Caines

 

[Norie]

John

Why not take a look at the HYPERLINK worksheet function?

You might want to check out the correct syntax for creating an email hyperlink, it normally involves mailto.

 

[VoG]

Hi John. Please try this

Sub MailHyperlinksConverter()
Dim lastrow As Long, i As Long, web As String, t As Date
t = Now()
Application.ScreenUpdating = False
Application.Calculation = xlManual
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow
    web = Cells(i, 1).Value
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="mailto:" & web, TextToDisplay:=web
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "Mail link Converting Run Time: " & Format(Now() - t, "hh:mm:ss")
End Sub

 

[John Caines]

Hello VOGII,
many thanks for this.
It worked a dream.
It was a fair bit slower than he "Flag Bad" email highlight macro
you also wrote yesterday. Where as that took 3 seconds, this took
1minute 30seconds.
(Still miles quicker than doing it by hand)

Many thanks again.
As to your thread Nori, I'm not too ofay with the "HYPERLINK worksheet function"

I am aware of hitting F2 then enter, but of course that only converts 1 email
at a time.
For such a large list, a macro similar to what VOGII has kindly wrote is perfect for
this situation.

Again, many thanks VOGII
All the best
John Caines

 

[cornflakegirl]

Norie - thanks for the tip about HYPERLINK. I really wouldn't have guessed from the help file that you could use it to do what the OP requested.