email addresses in Excel

Toshiba
October 9, 2007 at 13:04:40
Specs: XP, pentium 3; ram is 512

I have a column of email addresses in a worksheet. Somehow they got imported / entered as text and do NOT show as a hyperlink. Therefore, I cannot simply click on one to send an email; which is what I NEED it to do.

Is there a way to have the entire column of email addresses convert to a hyperlink / active email addresses that will open an Outlook email upon clicking it?

I do not want to have to go cell by cell making the change.

Thank you very much for your assistance.

Mike D.
Austin, Texas


See More: email addresses in Excel

Report •


#1
October 9, 2007 at 14:40:58

Try this:

Save a backup copy of your sheet in case things go terribly wrong.

Paste this code into the VBA editor, change Range("A1:A45") to match the cells you need fixed and run it.

Watch the word wrap. When you paste it into the VBA editor, the line that Starts with ActiveSheet and ends with TextToDisplay:=e_address.Value is all one line.

Sub MakeEmails()
For Each e_address In Range("A1:A45")
ActiveSheet.Hyperlinks.Add Anchor:=e_address, Address:="mailto:" & e_address.Value, TextToDisplay:=e_address.Value
Next
End Sub


Report •

#2
October 9, 2007 at 15:50:05

Sorry, I don't know how to use the VBA.

Is that something I need to install?

Thank you very much for your help.

Mike D.


Report •

#3
October 9, 2007 at 17:42:32

If clicking on the cell doesn't generate a new email message, highlight all the cells, click Format, Style, Select Hyperlink, OK.

That should correct the problem.

Life's more painless for the brainless.


Report •

Related Solutions

#4
October 10, 2007 at 05:53:14

I tried Jennifer's solution in XL 2003 and while it does make the text *look* like a hyperlink, I needed to go into each cell and double click it (i.e. put it into Edit mode) before it would actually become a hyperlink that would open my e-mail app.

There may be some way to select the whole column and force all cells into Edit mode in one action, but I don't know what that is.

If you want to use my code, there are multiple ways to do it. The easiest would be to do this:

Right-click the sheet tab for the sheet with the email addresses in it and select "View code". Paste the code into the window that opens. (If there are 2 panes, use the upper one)

To run it, click the Green triangle or chose Run...Run Sub/UserForm or click anywhere within the code and hit F5.

BUT - you need to make sure you change the Range that the code will work on. Where I have A1:A45, you need to put the actual Range that contains your e-mail addresses, and it must be enclosed in quotes.



Report •


Ask Question