Blog

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 protected] 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:

1. Open the Excel spreadsheet you want to update the email addresses on.
2. Right-click on the sheet name and select "View Code"
3. Paste the code snippet shown below into the window that displays.

   Sub CreateEmailHyperlinks()

   For Each e_address In Range("BF2:BF604")

   ActiveSheet.Hyperlinks.Add Anchor:=e_address, Address:="mailto:" & e_address.Value, TextToDisplay:=e_address.Value

   Next

   End Sub

4. 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".
5. 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.

Many of us just take what we get with DNS servers. For the most part all that back-end stuff is just handled for us quietly by whomever we happen to be getting our Internet connectivity from at our homes or places of work.

The challenge is that Internet Service Providers may not have put a priority on keeping their DNS servers up to date or may not refresh them as quickly as needed. I run into this issue sometimes when setting up a new domain name and website for a client. While it seems to be available to me online, there are other people, on different ISP’s that still don’t show the domain as being live yet.

The challenge is your DNS servers and here’s how you change them from the defaults on your computer.

Although these instructions are for Windows XP Professional, the general idea should be fairly similar for other operating systems.

1. Go to Start > Network Connections
2. Right-click on your "Local Area Network" or "Wireless" connection and select "Properties" in the right-click menu that displays.
3. In the "This connection uses…" window find and select "Internet Protocol (TCP/IP")
4. Click on the "Properties" button
5. At the bottom of the "Internet Protocol (TCP/IP) Properties" window you’ll see an area for specifying the DNS addresses to use.

Just enter the addresses as specified by the DNS service you want to switch to and use and enter them accordingly. Click on the "OK" button and you’re done.

You know a domain name isn’t really what you think it is. Computers don’t talk to each other using domain names and when you’re wanting to surf on over to your favorite websites, you’re not really going to a server named whatever the domain name is you want.

All a domain name does is point a person to the computer and network that’s hosting the files that you see when you type in that domain name. It’s like an alias for what’s really happening behind the scenes. It’s a representation that we understand, that is really made up of something more.

For example, we know what water is, but the chemical make-up is H20.

We’ve probably all used Google, but the actual computer network it’s located on is known as 74.125.67.100, which is an IP (Internet Protocol) address.

When we type in a domain name in our browser, a Domain Name Server works like a giant index pointing those domain names to the appropriate IP addresses of the computers and networks the websites reside.

Most of us never think about it, and the DNS task is usually handled by our own Internet Service Providers by default. We don’t have to rely on them though, sometimes they can be slower than what’s available elsewhere.

Google has released their own public DNS service now. I’ve been using it and it does seem to be a bit snappier than what I was using before, which was OpenDNS. However, it should be stated that Google Public DNS and OpenDNS are not purely the same. OpenDNS offers some more controls and functionality you may find helpful, than what you’re going to get with what’s being handed to you by Google Public DNS.

You can read more about the Google Public DNS service here. I’ll follow-up with another post on how to change your settings if you want to make the switch.