The other day a friend of mine had a Microsoft Excel question for me. They had a large Excel spreadsheet with the names of a few hundred people in it along with their email address. The email addresses weren’t clickable to pop-open their email client and they wanted them to be.
If you’ve ever played with Excel, you probably have already experienced this before. The challenge is that usually, if you click on one of the email cells to edit them and hit enter, it automatically reformats the cell into a clickable hyperlink.
What happens is that Excel inserts a hyperlink control into the cell and formats the link as a mailto:email@example.com link to get everything to work.
In this situation, the user didn’t want to manually click each row of the spreadsheet to auto-format the contents into a clickable email hyperlink.
The good news is you can accomplish what you’re looking for with a little bit of VB coding that’s not too hard to use. We found this little bit of code online and it worked for us and did exactly what we needed.
Follow these steps:
- Open the Excel spreadsheet you want to update the email addresses on.
- Right-click on the sheet name and select "View Code"
- Paste the code snippet shown below into the window that displays.
For Each e_address In Range("BF2:BF604")
ActiveSheet.Hyperlinks.Add Anchor:=e_address, Address:="mailto:" & e_address.Value, TextToDisplay:=e_address.Value
- In the second line, change the cell range to match the range of cells you have in your spreadsheet. I tested this on a contact export from Microsoft Outlook and the email address was located in column "BF".
- Click on the green triangle in the toolbar to run the VB and your spreadsheet should be all set.
From my experience the VB above fails if you have a cell with no email address in it. The VB code could be tweaked to trap for a blank and handle it, but I’ve not done that.