Make email address a hyperlink

**lvalesko** · 01-18-2011, 09:33 PM

I currently have an Excel 2003 doc with a column of email address.

The problem is that the email addresses are just plain text. I would like to be able to just click on an email address and have it open a blank email with the address I just clicked on as the recipient.

Is there a way I can select this column of email addresses that are plain text and change them into hyperlinks?

Thanks :-)

**Ron Coderre** · 01-18-2011, 10:33 PM

I'm pretty sure this VBA code will work in Excel 2003:

• Select the workbook you want to contain the macro
• ALT+F11...to open the VBA editor
• Insert.Module...to create a new module
• Copy the below VBA code and paste it into that module

Sub ConvertTxt2EmailLink()
Dim cCell As Range
Dim intResponse As Integer
Dim strURL As String
Dim strLinkText As String
Dim strPrefix As String

intResponse = MsgBox( _
      Title:="mailto Option", _
      Prompt:="OK that each link begins with 'mailto:'" & vbCr _
               & "Click: YES ...to prepend mailto:, if missing" & vbCr _
               & "Click: NO  ...to evaluate each link 'as is'", _
      Buttons:=vbYesNoCancel + vbQuestion)

Select Case intResponse
   Case Is = vbYes
      strPrefix = "mailto:"
   Case Is = vbNo
      strPrefix = ""
   Case Is = vbCancel
      Exit Sub
End Select

For Each cCell In selection.Cells
   If cCell.Hyperlinks.Count = 0 Then
      With cCell
         If .Value <> "" Then
            strLinkText = .Value
            strURL = strPrefix & Replace( _
                     Expression:=.Value, _
                     Find:=strPrefix, _
                     Replace:="")
            
            On Error Resume Next
            ActiveSheet.Hyperlinks.Add _
               Anchor:=cCell, _
               Address:=strURL, _
               TextToDisplay:=strLinkText
         End If
      End With
   End If
Next cCell
End Sub

• Select your list of emails to be converted to email-links
• ALT+F8...to see the list of available macros
• Select: ConvertTxt2EmailLink
• Click: Run...and follow the instructions.

Does that help?

**lvalesko** · 01-19-2011, 12:25 PM

WOW - that may be over my head but I'll certainly give it a try. :-)

Thanks!

**fliexcelhelp** · 11-29-2012, 04:33 PM

It did work without any issues.
Thank you very much!!!!!

**ranjangupta** · 01-28-2014, 10:26 PM

works well.
Thanks

**Naved90** · 02-09-2014, 08:56 PM

thanks for the Info

It really helped me

**sswoll** · 08-12-2015, 11:33 PM

I just tried this on Office for Mac 2011 and I get "Compile error Invalid outside procedure". The Title:='mailto Option" line is highlighted. Can you tell me what the problem may be?

**FDibbins** · 08-13-2015, 02:22 AM

sswoll welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.