How do I get email addresses to resolve in a worksheet column?
I’ve got a bunch of email addresses that were imported from a data source. The email address names are correct, but they are not resolved (i.e. they are not hyperlinked; underlined and blue).
I need to have these addresses hyperlinked.
The only way I know how to do this is by double-clicking each cell, which does indeed hyperlink them. But I have hundreds of these guys. There has to be a way to highlight them all and resolve them, right?
What happens if you run a macro that sets each cell to the value it currently has? Or, alternatively, sets a different cell to that value and then reverses the process? Does the result appear as a live hyperlink?
Find an empty cell. Type an email address in there. It should hyperlink automatically. Then copy the format to all the other cells. You can do this by clicking the hyperlink cell, then click on the little paintbrush in the toolbar, and then paint all the the non-hyperlinked cells. It should take about 3 seconds to do the whole thing.
If you’ve got enough rows that using the paint format would be difficult, here’s a macro that’ll do it. You’ll need to edit the range to match yours, and I’ve assumed that all of the email addresses are in the same column.
Sub fixit()
Dim EmAddr As String
For Each x In Range("A1", "A15")
x.Activate
EmAddr = x.Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & x.Value, TextToDisplay:=x.Value
Next x
End Sub
Sub fixit()
For Each x In Range("A1", "A15")
x.Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & x.Value, TextToDisplay:=x.Value
Next x
End Sub
IMO, I think a macro is way overkill for this. If the Format Paintbrush is too difficult, just highlight the one hyperlinked cell, Copy, select the row or column with the plain email addresses, Paste Special/Format. Really it couldn’t take more than a few seconds to do thousands of rows.