Contacts in Excel

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Active Member
Joined
Jan 13, 2002
Messages
424
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,743
Messages
5,726,022
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top