General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Converting email addresses to mailto: hyperlinks

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
                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, _
            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.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Where to put your macros

Unfamiliar with macros? Check out David McRitchie's Getting Started with Macros and User Defined Functions

How to prevent automatic hyperlinks