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.
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:
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: