Get that Gravatar!


If your Office application needs images, consider using gravatars. Visit gravatar's website for more information about them.

The best part about gravatars is that they follow you around the web — on websites, forums and blogs that use your email address to locate a publicly stored image used as an avatar in your profile or next to your comment.

If you store email addresses on a worksheet or table in a Word document, you may want to display gravatars as well. For example, you might have a sales worksheet with email addresses on it. Using those, you can grab the user's gravatar and display it in an Image or WebBrowser control (kind of like an image on a contact card in Outlook). Here's a quick way to do that.

Three steps are required to build the URL for a gravatar image:

  1. Trim excess spaces from an email address
  2. Make the email address lowercase
  3. Apply md5 hashing to the email address

Let's walk through all three steps!

  1. Trim excess spaces from an email address
  2. This one is simple — just apply VBA.Trim to the email address:

  3. Make the email address lowercase
  4. Another simple step — apply VBA.LCase$ to the resulting string:

  5. Apply md5 hashing to the email address
  6. Huh? Sorry, us VB programmers aren't as fortunate as PHP programmers, who have md5 hashing built in. So we'll have to go digging for an algorithm. After some searching I took one I found here. I won't reprint the code, it's too long and the authors don't want that anyway. So you'll have to visit the link and paste the code into a VBA module.

    Now all we need to do is call the provided md5 function to get our hash:


Finally, we need to make the hash lower case again:


Create the Image

To create the final image URL, just prepend to the hash we just created. For this example, I validate the email address first, then request a (hardcoded) 200px gravatar.

Function GetGravatar(emailAddress As String, _
  Optional validateEmail As Boolean = False) As String

Dim hashedEmail As String
Dim imageURL As String

If validateEmail Then
  If Not IsValidEmail(emailAddress) Then
    Exit Function
  End If
End If

hashedEmail = VBA.LCase$(MD5_string(VBA.LCase$(VBA.Trim(emailAddress))))
imageURL = "" & hashedEmail & ".jpg?s=200"

  GetGravatar = imageURL

End Function

Visit Email validation in VBA for the IsValidEmail function.

There are many other options; visit Image Requests for full options list.

What to do with the image?

You could…

  • Load the image into a WebBrowser control
  • Use the WebBrowser.Navigate method to "visit" the URL you just created, thereby displaying the image. Besides ease of use, this has the added benefit of not requiring any images downloaded to your computer. On the downside however, even when properly sized the WebBrowser control always seems to display a disabled scrollbar on the side.

  • Load the image into an Image control
  • Use the URLDownloadToFile API to download and save the image somewhere, then use the LoadPicture method to load the image into the Image control: Image1.Picture = LoadPicture("C:\my_image.jpg")

    Of course you'll need to specify a location (I like the local temp folder Environ("temp")). Personally I would this method, especially if the image size isn't going to change. It would use much less bandwidth and is ultimately faster (since you are loading cached pictures on file instead of fetching them from the web every time).

And if you were curious, here's my gravatar, located using the functions above: image

If you don't hear from me again this week, happy holidays and I will post something next week!

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
excel school learn dashboards