Excel - Column of email addresses. How do I make each cell hyperlink to that email?

Filed under: addresses email — admin @ 8:56 am

I have an Excel spreadsheet, Column M is populated with email addresses. I want to format this column so that when I click on the email addresses it opens an email addressed to that email address. How can I do this? I'm hoping to do this using formatting or style or something. I really don't want to change each cell individually.

I'm using Microsoft Office Professional Edition 2003.

I'm not sure if 2003 automatically assigns hyperlinks to items it recognises as email addresses. You could try F2 and then Enter to see what it does. Otherwise you can run some VBA code to put the hyperlink into each cell:

Sub Email()

For i = 0 To 10

X = Range("M1").Offset(i, 0)
Y = "mailto:" & X

Range("m1").Offset(i, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
Y, TextToDisplay:=X

Next i

End Sub

The code won't work with numbers or empty cells, so you could refine it a bit.

powered by Yahoo Answers

2 Comments »

  1. I'm not sure if 2003 automatically assigns hyperlinks to items it recognises as email addresses. You could try F2 and then Enter to see what it does. Otherwise you can run some VBA code to put the hyperlink into each cell:

    Sub Email()

    For i = 0 To 10

    X = Range("M1").Offset(i, 0)
    Y = "mailto:" & X

    Range("m1").Offset(i, 0).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    Y, TextToDisplay:=X

    Next i

    End Sub

    The code won't work with numbers or empty cells, so you could refine it a bit.
    References :

    Comment by kokevi_t — December 31, 2008 @ 2:39 pm

  2. That's funny– when I type an email address in a cell in Excel, I can simply click on it and it does exactly as you're asking here– opens a new outlook message with the To: address already populated. I'm using Excel 2003. I wonder if the cells in your Col M are formatted differently (I believe there's some setting where the hyperlink isn't active) or if you have leading/trailing spaces or some kind of character stuck in there?
    Try typing an email address somewhere else on the sheet (or a new workbook entirely) and see if it does the same thing, maybe?
    References :

    Comment by Jeremy — December 31, 2008 @ 3:28 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment