How to get Email address from outlook contacts for the names listed in a column?

Question

I am new to VBA and I need help doing something:

As seen in the picture below, I have a list of names. And what I want to do is retrieve their email address, based on their names in column A, from outlook contact list(the email addresses are scattered in different contact folders) and paste them into column B.

Or, Is it possible to get the email address from outlook contacts, for each name and send an email to them automatically with outlook so that I can get rid of the column B.

It's all possible. But to qualify for help here you need to show own effort. Start your code somewhere so that you can claim to be "stuck" and someone will help you to go one or two steps further. — Variatus

Mitch Mitch · Accepted Answer · 2017-08-05T13:37:51

This code assumes names are in column A. It further assumes that the name of the address book you are tapping into is named "Contacts", and that they are formatted according to your diagram.

Option Explicit 
Private Sub GetAddresses() 
Dim o, AddressList, AddressEntry 
Dim c As Range, r As Range, AddressName As String 
Set o = CreateObject("Outlook.Application") 
Set AddressList = o.Session.AddressLists("Contacts") 
 'Change this range accordingly
Set r = Range("A1:A25") 
  For Each c In r 
    AddressName = c.Value 
    For Each AddressEntry In AddressList.AddressEntries 
        If AddressEntry.Name = AddressName Then 
            c.Offset(0, 1).Value = AddressEntry.Address 
            Exit For 
        End If 
    Next AddressEntry 
  Next c 
End Sub

If the addresses are in the Global Address List, In Outlook, go to Tools--> Address Book. Then use the drop-down list to identify which list your addresses are in. Replace "Contacts" in the code with the name of the address book the addresses are stored in.

I didn't write this, I found it on Ozgrid and modified a couple of things to fit your situation. It may take a little tweaking for your application. Hope this helps or gets you going in the right direction.

How to get Email address from outlook contacts for the names listed in a column?

2 Answers