Getting Outlook contact info from an Excel Macro

Posted by Tony on February 13, 2002 10:57 AM

I'd like to be able to access phone numbers in my Outlook Contacts database from an Excel Macro.

For example just fill in a worksheet with a simple phone list (Name, Home Number).

I want to be able to do this from within an Excel Macro, as opposed to exporting the list from within Outlook.

If the answer is too complex for this forum, can someone suggest some resources / tutorials on the web that can help me?

Thanks, Tony

Posted by Tommy Bak on February 13, 2002 3:40 PM

Hi Tony
Try this. it's not perfekt but worksSub GetOutlook()
' This procedure retrieves all contacts from the Outlook
Range("A1").Select
Dim olapp As Outlook.Application
Dim nspNameSpace As Outlook.NameSpace
Dim fldContacts As Outlook.MAPIFolder
Dim objContacts As Object
Dim objContact As Object
Dim intCntr As Integer
'On Error GoTo GetAll_Err
Set olapp = New Outlook.Application
Set nspNameSpace = olapp.GetNamespace("MAPI")
Set fldContacts = nspNameSpace.GetDefaultFolder(olFolderContacts)
Set objContacts = fldContacts.Items
For Each objContact In objContacts
Cells(intCntr + 1, 1).Value = objContact.FullName
Cells(intCntr + 1, 2).Value = objContact.Email1Address
Cells(intCntr + 1, 3).Value = objContact.HomeTelephoneNumber
intCntr = intCntr + 1
Next objContact
GetAll_Bye:
Exit Sub
GetAll_Err:
MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number
Resume GetAll_Bye
End Sub

regards Tommy

Posted by Tony on February 13, 2002 7:34 PM

Thanks. This looks close enough to what I want to do, but when I try to run this I get
"Compile Error: User-defined type not defined"
on this line:

Note this is Office 10.0 (XP)

Any suggestions?

Posted by Tommy Bak on February 14, 2002 4:10 AM

Yes.
In the VBA-editor under TOOLS/References find Microsoft Outlook 10,0 object library and add a checkmark to this library
Regards Tommy

Posted by Tony on February 14, 2002 7:25 AM

Perfect, thanks!

Oops, that's just what I needed. I haven't used VB since '94 and this is my first foray into VBA so excuse the silly mistakes.

Thanks again for your help!