This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
Most people want to know how to turn off automatic hyperlinks. But what if you converted your mailto: links to plain text, then needed to convert them back?
This macro will check the selected range (or, optionally, the whole sheet), and convert any text of the form <text1>@<text2>.<text3> to an email hyperlink.
Public Sub ConvertToMailLinks()
Const sPATTERN As String = "?*@?*.?*"
Dim vResult As Variant
Dim rCell As Range
Dim rCheck As Range
If TypeName(Selection) = "Range" Then _
If Selection.Count > 1 Then _
Set rCheck = Selection
If rCheck Is Nothing Then
vResult = MsgBox( _
Prompt:="Search the entire worksheet?", _
Buttons:=vbYesNo, _
Title:="Convert to MailTo: Links")
If vResult = vbYes Then
Set rCheck = ActiveSheet.Cells
Else
Set rCheck = ActiveCell
End If
End If
On Error Resume Next
Set rCheck = rCheck.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rCheck Is Nothing Then
For Each rCell In rCheck
If rCell.Value Like sPATTERN Then _
ActiveSheet.Hyperlinks.Add _
anchor:=rCell, _
Address:="mailto:" & rCell.Value, _
TextToDisplay:=rCell.Value
Next rCell
End If
End Sub
You can place this in your Personal Macro Workbook (Personal.xls for WinXL) and attach to a toolbar button or keyboard shortcut.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Unfamiliar with macros? Check out David McRitchie's Getting Started with Macros and User Defined Functions