Creation of bulk Mailto hyperlinks

**jay540** · 10-05-2009, 12:26 PM

Dear listmates,

I would like to start off with apologizing for the newbie post. I need some assistance in writing a macro that will enable me to create mailto links in any particularly selected columns.

I have excel ss that have plain text email addresses in one colume, in hundreds of rows. If I could write a macro that would simply create mailto hyperlinks in the selected column that would help me avoid manually creating for each email address.

Thanks a bunch in advance.

Jay

**shg** · 10-05-2009, 12:32 PM

Really need a macro?

If there is a plaintext email address in A1,

=HYPERLINK("mailto:" & A1, "Email")

**DonkeyOte** · 10-05-2009, 12:33 PM

You could use the HYPERLINK function in adjacent cells ?

B1: =HYPERLINK("mailto:"&A1&"?subject=Subject&Body=Hello",A1)

If you do want VBA then assuming existing values are constants (curious as to why they're not already hyperlinks by default) then

Public Sub Example()
Dim rngCell As Range
For Each rngCell In Selection.SpecialCells(xlCellTypeConstants)
    With rngCell
        If .Hyperlinks.Count Then .Hyperlinks(1).Delete
        .Hyperlinks.Add rngCell, "mailto:" & rngCell.Value, , , rngCell.Value
    End With
Next rngCell
End Sub