Convert an entire column of email addresses to hyperlink emails

[bigfatdummy]

I have a column of 5800 email addresses. If I click on the email address and press enter, the entry becomes a hyperlink (mailto:xxx@xxx.com). Can I do this to the entire column at once, rather than clicking each entry individually?

 

[eeek18]

you copy the first cell you converted to a hyperlink then highlight the rest and apply 'Paste Special' and select 'Format'

 

[VoG]

Or you can use the Format Painter. With the hyperlinked cell selkected click on the format painter (paintbrush icon) then select your other cells.

 

[bigfatdummy]

Sorry thank you for the help but that doesn't work. Sure it copies the format but only changes the font to blue and underlines it. You can't click the email address to create an email.

I have been toying with a macro and this is what I have so far.

Sub EmailHylink()
For Each Cell In Selection
Range(Cell.Address).Hyperlinks.Add Anchor:=Range(Cell.Address), Address:="mailto:" & Cell.Value
Next Cell
End Sub

This does what I need it to do, but it adds mailto: into the cell. The end result works just like I want it to but It sure would be great if I could have only the email address without the mailto: in the cell.

 

[Jonmo1]

Range(Cell.Address).Hyperlinks.Add Anchor:=Range(Cell.Address), Address:="mailto:" & Cell.Value, TextToDisplay:= Cell.Value

 

[Norie]

Why don't you take a look at the HYPERLINK function and/or the TextToDisplay argument?

 

[bigfatdummy]

I did look at the hyperlink function.

Thanks, thats what I needed, "TextToDisplay:=Cell.Value".

Thanks for the help. I know I could have just done and Find & Replace on Mailto: as well but I have these lists generated twice a week and it will be nice to have it working correctly.

 

[keiranjames93]

Sorry to bring up an old thread but how do I define the range I want the Macro to run on.

Ie. My email addresses are in column Q. How do I only run the macro on that column?

Thanks in advance.

 

[reberryjr]

Jonmo1, I realize you posted your response quite some time ago, but I'm having difficulty in understanding how to code for this. I have a sheet ("Bios") that has a column ("O") with text email addresses in it. Column O is also a named range ("Email"). These entries are made via a UserForm. Ideally, I'd like for the email addresses to populate "Bios" formatted as a hyperlink, but my understanding through other research is that isn't possible.

I am relatively new to VBA, so I'm struggling how to code for the email addresses to convert to hyperlink. Any assistance you can offer, would be greatly appreciated.

Private Sub Worksheet_Activate()

Dim ws1 As Worksheet
Dim cEmail As Range


Set ws1 = ThisWorkbook.Sheets("Bios")


range("O2").hyperlinks.add _
    anchor:=range("O2"), _
    Address:="mailto:"&("O2".value), _
    texttodisplay:=("O2")


End Sub

 

[reberryjr]

Actually, I was able to get the solution with help on a different post.

With ws1.Range("O" & NextRow)    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="mailto:" & Me.txt_Email.Value, _
    TextToDisplay:=Me.txt_Email.Value
End With