Contacts in Excel

[imported_unknown]

I have an Excel 2000 invoice template. I would like to be able to select the customer information from an existing database for automatic insertion into the invoice. The customer database is stored in Outlook 2000 contacts. Any thoughts on how to do this would be appreciated.

 

[Damon Ostrander]

Hi smaynes,

Perhaps this will help. Here is a sample macro that writes the Outlook contacts list to the active worksheet.

Sub MakeContactList()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim Contacts As Object
Dim i As Integer

Set OlApp = CreateObject("Outlook.Application")
Set Contacts = OlApp.getnamespace("MAPI").getdefaultfolder(10)

[a1] = "Index"
[b1] = "Contact Name"
For i = 1 To Contacts.items.Count
Cells(i + 1, 1) = i
Cells(i + 1, 2) = Contacts.items(i)
Next i

End Sub

 

[imported_unknown]

That works great, but I only want one contact of my choosing along with the address and phone number that goes with it. This might get more complicated than I can deal with, but I think it's worth a try.

 

[RichardS]

I think you might want to look at the VLOOKUP worksheet function. Post back if you need further help, but look at the help files first.

Richard

 

[Damon Ostrander]

Hi again smaynes,

Perhaps this will give a bit further help. Say you have the full name of a person in cell A1 and you want to retrieve the person's business phones number and email address from Outlook contacts and write them to cells B1 and C1. Here's the code:

Sub GetContactInfo()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim Contacts As Object
Dim i As Integer

Set OlApp = CreateObject("Outlook.Application")
Set Contacts = OlApp.getnamespace("MAPI").getdefaultfolder(10)

With Contacts.items([a1].Value)
[b1] = .email1address
[c1] = .businesstelephonenumber
End With

End Sub

Damon