convert cell content to html automatically

P

Prometheus

I am managing in excel a flat database of over 1000 names, committees, etc., where email addresses are found using vlookup function. How do I convert these results to html so that when they are published on the web they are active email addresses. I know I can laboriously on each cell do this: F2 (edit formula) F9 (value) Enter. That seems to convert what looks like an email address to html (but nowhere can I find instructions about that). I need to be able to do this for all cells at once. I've tried writing a macro, but I don't seem to be able to recortd the editing steps described above.
 
M

Myrna Larson

Select all of the cells containing email addressses and run this macro.

Sub MakeHyperLinks()
Dim Cell As Range
Dim WS As Worksheet

Set WS = Selection.Parent
For Each Cell In Selection
WS.Hyperlinks.Add Cell, Cell.Value
Next Cell

End Sub


I am managing in excel a flat database of over 1000 names, committees, etc.,
Click to expand...
where email addresses are found using vlookup function. How do I convert these
results to html so that when they are published on the web they are active
email addresses. I know I can laboriously on each cell do this: F2 (edit
formula) F9 (value) Enter. That seems to convert what looks like an email
address to html (but nowhere can I find instructions about that). I need to be
able to do this for all cells at once. I've tried writing a macro, but I don't
seem to be able to recortd the editing steps described above.
 
P

Prometheus

Thanks, Myrna: that gets me halfway there. However, the hyperlink references are cluttered with the path:file\\c:\\etc.etc. which has to be removed before they can be used as email links.
 
D

David McRitchie

instead of cell.value
try "mailto:" & cell.value
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Prometheus said:
Thanks, Myrna: that gets me halfway there. However, the hyperlink references are cluttered with the path:file\\c:\\etc.etc. which
Click to expand...
has to be removed before they can be used as email links.function. How do I convert these results to html so that when they are published on the web they are active email addresses. I know
I can laboriously on each cell do this: F2 (edit formula) F9 (value) Enter. That seems to convert what looks like an email address
to html (but nowhere can I find instructions about that). I need to be able to do this for all cells at once. I've tried writing a
macro, but I don't seem to be able to recortd the editing steps described above.
 
Top