Get Email Address from Outlook Contacts

[Alex501]

Hi

Is there any way that within Excel I can lookup an email address from Outlook contacts based on a Name in a cell?

Thanks in advance.

Alex

 

[ceranes]

Hi Alex,

Here is something you can try. Note, this method only works if the person's name in your Excel sheet exactly matches the name in your Outlook contacts.

1. Open Excel and create a new workbook.
2. Enter the person's name in a cell. For example, let's assume the person's name is "John Smith", and you have entered it in cell A2.
3. Go to the Developer tab (if you don't see this tab, you may need to enable it in the Excel options).
4. Click on "Visual Basic" in the "Code" section of the Developer tab. This will open the Visual Basic Editor.
5. In the Visual Basic Editor, click on "Tools" and then "References". This will open the References dialog box.
6. Scroll down and check the box next to "Microsoft Outlook XX.X Object Library", where XX.X is the version of Outlook that you have installed.
7. Click "OK" to close the References dialog box.
8. In the Visual Basic Editor, click on "Insert" and then "Module". This will create a new module.
9. Copy and paste the following code into the new module:

Function GetEmailAddress(ByVal name As String) As String

    Dim oApp As Outlook.Application
    Set oApp = New Outlook.Application

    Dim oNamespace As Outlook.Namespace
    Set oNamespace = oApp.GetNamespace("MAPI")

    Dim oAddressList As Outlook.AddressList
    Set oAddressList = oNamespace.GetDefaultFolder(olFolderContacts).AddressList

    Dim oRecipient As Outlook.Recipient
    Set oRecipient = oNamespace.CreateRecipient(name)

    If oRecipient.Resolve Then
        GetEmailAddress = oRecipient.AddressEntry.GetExchangeUser.PrimarySmtpAddress
    Else
        GetEmailAddress = ""
    End If

    Set oRecipient = Nothing
    Set oAddressList = Nothing
    Set oNamespace = Nothing
    Set oApp = Nothing

End Function

1. Save the module.
2. Go back to your Excel worksheet.
3. In another cell, enter the following formula:
   

=GetEmailAddress(A2)

This formula will call the GetEmailAddress function that you created in the module and pass it the person's name that you entered in cell A2.

1. Press "Enter" to execute the formula.
2. The formula will return the email address associated with the person's name in your Outlook contacts.

Best of luck! Let me know how it turns out.

Chris