How do I convert email addresses to clickable links?
 

I have imported a column of valid email address. How can I make them
clickable links to email. No problem if I type them but there are far to
many.

Help apprecited. Thanks.

Rick Wetzel

Hi Rick

Try this one for Column B in the activesheet

Sub test()
For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "*@*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rick Wetzel" wrote in message ...
I have imported a column of valid email address. How can I make them
clickable links to email. No problem if I type them but there are far to
many.

Help apprecited. Thanks.

Rick Wetzel

small suggestion:

Instead of "*@*", the pattern "?*@?*.?*" will be more
discriminating for email addresses.

See http://mcgimpsey.com/excel/subs/converttomaillink.html



In article ,
"Ron de Bruin" wrote:

Try this one for Column B in the activesheet

Sub test()
For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "*@*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub

Thanks J.E for this suggestion



--
Regards Ron de Bruin
http://www.rondebruin.nl



"JE McGimpsey" wrote in message ...
small suggestion:

Instead of "*@*", the pattern "?*@?*.?*" will be more
discriminating for email addresses.

See http://mcgimpsey.com/excel/subs/converttomaillink.html



In article ,
"Ron de Bruin" wrote:

Try this one for Column B in the activesheet

Sub test()
For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "*@*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub