Converting Text email addresses to Hyperlinked email addresses

[Michael Koerner]

I have just received a spread sheet where on column contains email addresses
(1600). the column format is text format, and I would like to convert into a
hyperlinked email address, so that I can email directly form the sheet. Is this
a change one address at a time? Or, is there away to do it all at once?

 

[E Oveson]

Not sure if there's some cleaner way to do this, but you could run this code
(assumes all values in the particular range need to be converted, where
you'd change convertRng to the range you desire):

Sub convertToHypers()
Dim convertRng As Range
Set convertRng = Range("Y1:Y1600")
Dim rng As Range

For Each rng In convertRng
If rng.Value <> "" Then
ActiveSheet.Hyperlinks.Add rng, rng.Value
End If
Next rng

End Sub

-Erik

 

[Michael Koerner]

Tried your macro. but when I clicked on the hyperlink, it tried to link to an
existing file or webpage. I would like the link to be an email address.

--

Regards
Michael Koerner


Not sure if there's some cleaner way to do this, but you could run this code
(assumes all values in the particular range need to be converted, where
you'd change convertRng to the range you desire):

Sub convertToHypers()
Dim convertRng As Range
Set convertRng = Range("Y1:Y1600")
Dim rng As Range

For Each rng In convertRng
If rng.Value <> "" Then
ActiveSheet.Hyperlinks.Add rng, rng.Value
End If
Next rng

End Sub

-Erik

 

[keepITcool]

try:

will Process all textvalues in Column A on the active sheet


Sub MailTo2()
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

With Columns(1).SpecialCells(xlConstants, xlTextValues)
For Each ra In .Areas
For Each rc In ra
rc.Hyperlinks.Add rc, "mailto:" & rc.Text
Next
Next
End With
Application.ScreenUpdating = False

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[E Oveson]

Ok, does each cell just look like (e-mail address removed) ? In that case, change
(like in keepItCool's code):
ActiveSheet.Hyperlinks.Add rng, rng.Value
to
ActiveSheet.Hyperlinks.Add rng, "mailto:" & rng.Value

 

[Michael Koerner]

Worked like a charm. Thank you all very much.

--

Regards
Michael Koerner


Ok, does each cell just look like (e-mail address removed) ? In that case, change
(like in keepItCool's code):
ActiveSheet.Hyperlinks.Add rng, rng.Value
to
ActiveSheet.Hyperlinks.Add rng, "mailto:" & rng.Value