"Live" email addresses in an Excel spreadsheet

Norma Posy
Norma Posy used Ask the Experts™
I have developed a VB6 application that generates an Excel spreadsheet.

One of the columns contains email addresses. User would like to copy and paste a selection from that column directly into the "TO" box of his email utility (which happens to be Yahoo email).

Question: How do I write code that creates that column with "live" email addresses. That is, underlined and blue?

I am assuming that code that assigned column attributes would do it.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

as far as I know, the email addresses don't need to be hyperlinks in order to copy them to an email application. If the cell shows


without the blue and underline, it is still a valid email address and can be copied into the To field of an email.

cheers, teylyn
Hi, NormaPosy.

I agree with what teylyn says. Couple of extra points, though...
(1) When you paste a selection of cells into the "To" field, are the carriage returns/line feeds "converted" to the appropriate delimiters? For example, when I paste a number of cells into Outlook, it automatically replaces the CR/LF's by semi-colons (when I click/tab into another field). If this doesn't happen for you then you may need to append a semi-colon (or whatever) to each email address.
(2) You can create a blue, live e-mail address. For example...
ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), Address:="mailto:john@somewhere.com", TextToDisplay:="john@somewhere.com"

Norma PosyPhysicist


Thank you
Thanks, NormaPosy.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial