How to retrieve current user's Outlook email address with late binding?

Question

I was trying to figure out how to use late binding in an excel module to retrieve the current user's email address for 'auto-emailing' upon execution of a macro.

I'll post the solution below. I am using Excel 2010 but it works on 2013 as well.

Answer 1

Dim OL, olAllUsers, oExchUser, oentry, myitem As Object
Dim User As String

Set OL = CreateObject("outlook.application")
Set olAllUsers = OL.Session.AddressLists.Item("All Users").AddressEntries

User = OL.Session.CurrentUser.Name

Set oentry = olAllUsers.Item(User)

Set oExchUser = oentry.GetExchangeUser()

msgbox oExchUser.PrimarySmtpAddress