View Full Version : Get SMTP address

03-21-2006, 05:15 AM

I have a spreadsheet with the user ID's. I would like to have a function which looks up the Global Address List and returns the email address.

There are 2 ways of getting the email address:

1. From GAL
2. From Active Directory

Can you please give me the sample code to do this.

Thanks in advance,
Sunder :dunno

05-31-2006, 03:21 AM

I've been working on this problem also. Trying to get the user email / SMTP address.

I've just found this...


Hope it helps us!


05-31-2006, 04:27 AM
Hi and welcome to VBAX :hi:

You can try this code:'set the name of the address book here
Const strAddressBookName As String = "Global Address Book"
Dim myAddressList As AddressList
Dim i As AddressEntry

Set myAddressList = Application.Session.AddressLists(strAddressBookName)
For Each i In myAddressList.AddressEntries
'output to Immediate window
If i.Type = "SMTP" Then Debug.Print i.Address
Next i

05-31-2006, 05:02 AM
Many thanks Killian,

I do have a question. Is this code designed to run in Outlook? I tried to run it in Excel with no luck. :-(

05-31-2006, 05:52 AM
Yes, it's using the Outlook object model, so it will run "as-is" in Outlook - to run it in Excel, you will need to add a reference (in the Excel project VBE, Tools>References) to the Outlook object Model.

06-01-2006, 05:29 AM
Hi Killian,

I'm still working on this thread. Not having any luck running this code in Outlook or Excel!
What I'm actually trying to do is retrieve the full Outlook email address of the person logged onto Windows NT.

I can get the user name using the Environ function. Is there anything simple like that I can use to get the email address that you know of?

Sorry to be a nuisance!

06-01-2006, 11:07 AM
Hi again,
my response was for Sunder's original post (getting email addresses from address lists).
I didn't check your link...
To get the current user in Outlook, you can use the expressionSession.CurrentUser.Address
So this will provide the email address of the current user of Outlook (the active account) which I think is what you need...