Extract Outlook Address book properties into Excel

[Junoon]

Hi All,

How is it possible to extract all Addressbook properties like phone,
email address, full name, business contact details & Customer user
fields like EmpID & put them each in 1 column after another in an Excel
sheet, 1 record (Row) for each mail received in Outlook.

i.e. each Row would contain each mail sender's address book properties
& each property would be in a Column.

I want to especially get the EmpID (Employee ID) for each Sender
collected in an Excel column.

How do i do that?

Warm Regards,

Junoon

 

[Michael Bauer]

Am 20 Jun 2006 11:53:57 -0700 schrieb Junoon:

This sample shows how to insert data in a new Worksheet:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim rn as Excel.Range

Set xl=New Excel.Application
Set wb=xl.Workbooks.Add
Set ws=wb.Worksheets(1)

' Write data into first column, second row
Set rn=ws.Range("a2")
rn.Value = "something"

' Write data into next column
rn.Offset(0,1).Value="more"

Now simply loop through your contacts, read whatever you´re interested in
and write it into the worksheet.

 

[Junoon]

Hi Michael,

My main concern is not the code to get the data into the worksheet
columns, but HOW TO access (Loop Thru) ALL the Outlook Contact
properties one by one.

Warm Regards,

Junoon

 

[Junoon]

Hi Michael,

What i am trying to do is to access the Mail Senders properties (the
Office people -belonging to our Global Address List), who have sent me
mails & all mails are having a Unique Subject.

I am trying to scan each mail & then access the Mail senders Employee
ID (EmpID, i guess custom defined) & other properties also like his
phone # or mobile # etc.

Then dump them one by one in columns in Excel.

But how to access these mail item properties, especially a
Custom-defined type like EmpID. The reason why i am trying to get the
EmpID is because its a Unique ID given to an Employee & would help in a
VLOOKUP with my Depts HeadCount.xls file for storing correct Mailitem
data under the correct Employee ID in the HeadCount.xls file which we
have to update on a Daily basis.

Hope this Helps!

Warem Regards,


Junoon

 

[Michael Bauer]

Am 21 Jun 2006 01:53:03 -0700 schrieb Junoon:

You know how to loop through folder items, you did it in a previous thread
(Find...FindNext).

All custom properties are available via the UserProperties collection:

Dim v as Variant
v=Item.UserProperties("EmpID").Value

 

[Junoon]

Thanks will try that & let you know.

Warm Regards,

Junoon

---------------

 

[Junoon]

Hi Michael,

Tried the solution you gave, but did not seem to work.

I think since the Employee names & Emp Code belongs to a Global Address
list, so they donot come under User defined properties.

What i tried to do was search for the Emp Code (Emp ID) by
double-clicking on a mail address. The Emp Code shows under the
"General" Tab but when i search for it in Contacts, Journals,
User-defined fields etc, i am unable to locate it.

I am stumped! How do i get access to it in such a scenario.


Warm Regards,


Junoon

 

[Michael Bauer]

Am 26 Jun 2006 14:37:02 -0700 schrieb Junoon:

You asked me how to access user properties, and the sample works.

Please ask your administrator, or whoever is responsible, where your data is
stored.

 

[Sue Mosher [MVP-Outlook]]

Data in the GAL is not Outlook contact data. You can use Outlook objects to get only the bare basics, like name and address (and not even the SMTP address at that). For other properties, you must use CDO 1.21 or, to avoid security prompts, Redemption. See http://www.outlookcode.com/codedetail.aspx?id=594 for a sample that shows how to return the mobile phone number from a GAL user entry.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Hi Michael,

Tried the solution you gave, but did not seem to work.

I think since the Employee names & Emp Code belongs to a Global Address
list, so they donot come under User defined properties.

What i tried to do was search for the Emp Code (Emp ID) by
double-clicking on a mail address. The Emp Code shows under the
"General" Tab but when i search for it in Contacts, Journals,
User-defined fields etc, i am unable to locate it.

I am stumped! How do i get access to it in such a scenario.

 

[Junoon]

Hi Sue,

I had downloaded & installed Outlook Spy on my Office PC, but am still
unable to Locate the "Emp.Code" field.

Can you please provide some guidelines as to where to look for it on
the Toolbar thats installed in MS Outlook now.


Warm regards,

Junoon

 

[Sue Mosher [MVP-Outlook]]

1) IAddrBook

2) Switch to the GetSearchPath tab. Select GLobal Address Book and click OpenEntry.

3) Switch to the GetContentsTable tab. Select an entry that you has known data in the property you're interested in. Click OpenEntry.

4) Browse the Value column until you find the known data. That will be the property you want. Make note of that property's "Tag num" value at upper right. That is the MAPI property tag you need to use to extract that value programmatically from the AddressEntry.Fields collection.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Hi Sue,

I had downloaded & installed Outlook Spy on my Office PC, but am still
unable to Locate the "Emp.Code" field.

Can you please provide some guidelines as to where to look for it on
the Toolbar thats installed in MS Outlook now.


Warm regards,

Junoon

 

[Junoon]

Thanks Sue,

Will try that & let you know.

1) IAddrBook

2) Switch to the GetSearchPath tab. Select GLobal Address Book and click OpenEntry.

3) Switch to the GetContentsTable tab. Select an entry that you has known data in the property you're interested in. Click OpenEntry.

4) Browse the Value column until you find the known data. That will be the property you want. Make note of that property's "Tag num" value at upper right. That is the MAPI property tag you need to use to extract that value programmatically from the AddressEntry.Fields collection.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Hi Sue,

I had downloaded & installed Outlook Spy on my Office PC, but am still
unable to Locate the "Emp.Code" field.

Can you please provide some guidelines as to where to look for it on
the Toolbar thats installed in MS Outlook now.


Warm regards,

Junoon