Hyperlinks

C

C Tate

I have a spreadsheet which is a sort of database. One column is a list of
email addresses. At present they are not clickable hyperlinks. Is it
possible to convert all of them in one go so I can click on them and send an
email from my mail program?
 
D

Dave Peterson

Depending on what you have in the cell, you could use a helper column with a
formula:

=HYPERLINK("mailto:"&A1)
or
=HYPERLINK(A1)
depending if you had [email protected] or mailto:[email protected] in
the cell.

Or you could use a macro to add them in place:

Option Explicit
Sub MakeHyperlinks()

Dim myCell As Range
Dim myAddr As String
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing found"
Exit Sub
End If

For Each myCell In myRng.Cells
myAddr = ""
If InStr(1, myCell.Value, "@") > 0 Then
If LCase(Left(myCell.Value, 7)) <> "mailto:" Then
myAddr = "mailto:" & myCell.Value
Else
myAddr = myCell.Value
End If
ElseIf LCase(Left(myCell.Value, 5)) <> "http:" Then
myAddr = "http://" & myCell.Value
Else
myAddr = myCell.Value
End If

If myAddr <> "" Then
With ActiveSheet
.Hyperlinks.Add Anchor:=myCell, _
Address:=myAddr, _
ScreenTip:=myAddr, _
TextToDisplay:=myCell.Value
End With
End If
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top