cell format

[Mindy]

Hello, is there anyway that if you have a column of email addresses, you can
format that column to where if you click on an email address, it will open an
email message?

 

[Jon Quixley]

Mindy,

I am not sure if there is a better way of doing this (there probably is
when Bob Phillips get around to reading your mail) - You would need to
save the e-mails as separate files somewhere on your computer and then
it is a matter of hypertext linking the e-mail address to those files.
Excel will by default link an e-mail address to Outlook and your
address book, but you can edit this by right-clicking on the address
once you have it in Excel and point the hyperlink to a file rather than
to the Outlook address.

Hope this makes sense

Jon

 

[Jon Quixley]

Mindy,

I am not sure if there is a better way of doing this (there probably is
when Bob Phillips get around to reading your mail) - You would need to
save the e-mails as separate files somewhere on your computer and then
it is a matter of hypertext linking the e-mail address to those files.
Excel will by default link an e-mail address to Outlook and your
address book, but you can edit this by right-clicking on the address
once you have it in Excel and point the hyperlink to a file rather than
to the Outlook address.

Hope this makes sense

Jon

 

[brodiemac]

If it doesn't do it automatically, hit the insert hyperlink button and enter
it in there.

 

[Chip Smith]

by default, excel should add a hyperlink to an email message as soon as you
put in a valid email address, ie. (e-mail address removed)

 

[shaunap]

If you are entering an e-mail address and are not automatically getting a
hyperlink then go to Tools -> Auto Correct Options -> Auto Format As You Type
-> and click the checkbox beside Internet and network paths with hyperlinks.
That should change all pathways to hyperlinks.

Shauna

 

[Mindy]

the email addresses have been imported in and excel isn't recognizing them as
email addresses.

 

[shaunap]

On each entry hit F2 to edit the cell then hit Enter. The data in the cell
should automatically change to a hyperlink then. As you probably have a
number of entries to do this to just create a macro to run the two steps.

Shauna

 

[Mindy]

I haven't written a macro before so could you walk me through that process
for this situation?

 

[Gord Dibben]

Mindy

David McRitchie has written a macro to change addresses to hyperlinks.

Sub MakeHyperlinks()
'David McRitchie
Dim cell As Range
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End With
Next cell
End Sub

Hit ALT + F11 to open the VB Editor.

CTRL + r to open Project Explorer

Select your workbook/project by name and right-click and Insert>Module

Copy/paste the code into that module.

Save the file then ALT + Q to go back to Excel.

You can run the macro by Tools>Macro>Macros>Run

Select the range of cells before running the macro.


Gord Dibben MS Excel MVP

 

[Mindy]

that worked, Thanks

Mindy

David McRitchie has written a macro to change addresses to hyperlinks.

Sub MakeHyperlinks()
'David McRitchie
Dim cell As Range
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End With
Next cell
End Sub

Hit ALT + F11 to open the VB Editor.

CTRL + r to open Project Explorer

Select your workbook/project by name and right-click and Insert>Module

Copy/paste the code into that module.

Save the file then ALT + Q to go back to Excel.

You can run the macro by Tools>Macro>Macros>Run

Select the range of cells before running the macro.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

You're welcome.

David has some pretty amazing code and general Excel information on his website.

Take a browse sometime.

http://www.mvps.org/dmcritchie/excel/excel.htm


Gord

that worked, Thanks

Gord Dibben MS Excel MVP

 

[Tamara]

Thank you shaunap for this answer. It is the easiest to understand of all
the answers posted on this subject.