How to convert multiple email addresses to hyperlinks in Excel? 

If you have a list of plain text email addresses in a worksheet, and now, you want to convert theses email addresses to hyperlinks that you can send emails while clicking the addresses. Of course, you can convert them to hyperlinked email addresses, but, this method will be boring if there are multiple addresses needed to be converted. In this article, I will talk about some good tricks to deal with this task.

Convert multiple email addresses to hyperlinks with Formula

Convert multiple email addresses to hyperlinks with VBA code

Convert multiple email addresses to hyperlinks with Formula

With the Hyperlink function, you can quickly convert the column email addresses to hyperlinked addresses at once.

1. Enter this formula =hyperlink("mailto:"&A2) into a blank cell where you want to put the result, see screenshot:

2. Then drag the fill handle down to the cells that you want to contain this formula, and all the email addresses have become clickable hyperlinks as following screenshot shown:

Convert multiple email addresses to hyperlinks with VBA code

As you can see, by using the above formula, a string “mailto:” will be added in front of each email addresses, if you don't want the mailto: within the addresses, the following VBA code may do you a favor.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Convert multiple email addresses to hyperlinks

Sub EmailHylink()
'updateby Extendoffice
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    Dim xUpdate As Boolean
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xCell.Hyperlinks.Add Anchor:=xCell, Address:="mailto:" & xCell.Value
    Next
    Application.ScreenUpdating = xUpdate
End Sub

3. After pasting the code, please press F5 key to this code, and a prompt box will pop out to remind you select the data range that you want to use, see screenshot:

4. And then click OK, all the selected email addresses have been converted to the hyperlinked addresses, see screenshot: