Copy contact list from Outlook to Excel

[MrVillareal]

Hi Mrexcel users,

How can I copy all contact list from the address book of Outlook to excel.

Thank you in advance.
Marvin

 

[VoG]

Try this. You need to set a reference to Outlook in Tools > References

Sub GetOutlookContacts()

Dim olapp As Outlook.Application
Dim nspNameSpace As Outlook.Namespace
Dim fldContacts As Outlook.MAPIFolder
Dim objContact As Outlook.ContactItem
Dim iRow As Long
Dim sName As String

On Error Resume Next
Application.ScreenUpdating = False
Set olapp = New Outlook.Application
Set nspNameSpace = olapp.GetNamespace("MAPI")
Set fldContacts = nspNameSpace.GetDefaultFolder(olFolderContacts)
iRow = 1
Cells(iRow, 1).Resize(1, 5).Value = Array("Full Name", "First Name", "Middle Name", "Last Name", "Email Address 1")
For Each objContact In fldContacts.Items
    With objContact
        If objContact.Class = olContact Then

            iRow = iRow + 1
            Cells(iRow, 1).Value = .FullName
            Cells(iRow, 2).Value = .FirstName
            Cells(iRow, 3).Value = .MiddleName
            Cells(iRow, 4).Value = .LastName
            Cells(iRow, 5).Value = .Email1Address
            ActiveSheet.Hyperlinks.Add Cells(iRow, 5), Cells(iRow, 5).Value
        End If
    End With
Next objContact
Application.ScreenUpdating = True
Set objContact = Nothing
Set fldContacts = Nothing
Set nspNameSpace = Nothing
Set olapp = Nothing
End Sub

 

[MrVillareal]

Hi Peter,

Thank you for your time..In your answer is it extracting all list in the address book in outlook? And how will I work with your given code in outlook to copy them in excel? Should I copy the code and paste it using Alt F11 in outlook or in excel? And again I greatly appreciate your time thank you very much

 

[MrVillareal]

I am using 2010 MS Office

 

[VoG]

This code is run from Excel. It will write out your Outlook contact details to the active sheet.

 

[MrVillareal]

what are the requirements before I run it? because its not working with me..tnx..

 

[MrVillareal]

Actually Im looking for a way wherein from the address book of the outlook wherein global list of contacts are in there I want them to be extracted and copy it to excel.. Even better if the outlook list is updated then the excel copy should also be updated. Is this possible? Im considering the connections features in excel but I do not how to use that..