Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I need to export ALL Outlook 2003 contact fields to a CSV, Excel, or Access File.

Posted on 2006-07-10
16
Medium Priority
?
426 Views
Last Modified: 2008-02-01
I am trying to export our employees individual contacts to a CSV, Excel, or Access file that will then be important into a custom designed CMS.  I can map most of the fields requested by IT, but there some that I cannot and they are highly required.  Specifically, I really need to know the name of the employee who entered the contact (or "owns" it), when it was created, and if and when it was modified.  I believe Outlook does track this, but I cannot located it.

I want to avoid making custom fields if possible.  Also, I need to be as little intrusive as possible (i.e. cannot work on our employees machines for too much time, which results in a loss of productivity).  I also want to avoid any VB sc

Any suggestions will be greatly appreciated.

Thanks,
Shane
0
Comment
Question by:Yockos
  • 8
  • 8
16 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 17077575
Greetings, Yockos.

I'm pretty confident that a script is your only solution.  I can help with that if you're interested.

Cheers!
0
 

Author Comment

by:Yockos
ID: 17081975
HI BDF!

Thanks for the prompt reply.  I would be interested in any help you could offer.  Unfortunately, I have only a little knowledge of VB scripting.  I know how to open it (alt-F11) and some scripting...

I am trying to talk the IT department into having them export the contacts from the Exchange Server instead of me creating a protocol for the staff to export their contacts to send to me.

Again, I am interested in using any help you can give me in case I have to do it myself.

Thanks,
Shane
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17086968
Hi, Shane.

The script below will export all available contact fields to a spreadsheet.  Follow these instructions to use it.

1.  Open Notepad.
2.  Copy the script below and paste it into Notepad.
3.  Save the file.  You can name it anything you want so long as it ends with a .vbs extension.
4.  Close Notepad
5.  Double-click the file you saved in step #3.
6.  A dialog-box will pop up warning that a program is accessing your mailbox and asking for permission to allow it to continue.  You have to say Yes for the script to run.

That's all there is to it.  When the script has finished you can open the spreadsheet and review the information exported.

'Constants
Const olOutlookInternal = 0
Const olFolderContacts = 10

Dim olkApp, olkNS, olkFolder, olkContact, olkProperty
Dim excApp, excBook, excSheet
Dim intRow, intCol, bolHeadersWritten

'Get Outlook ready
Set olkApp = CreateObject("Outlook.Application")
Set olkNS = olkApp.GetNamespace("MAPI")
Set olkFolder = olkNS.GetDefaultFolder(olFolderContacts)

'Get Excel ready
Set excApp = CreateObject("Excel.Application")
Set excBook = excApp.Workbooks.Add()
Set excSheet = excBook.Sheets.Item(1)

'Main Routine
intRow = 2
intCol = 1
For Each olkContact In olkFolder.Items
    For Each olkProperty In olkContact.ItemProperties
          If olkProperty.Type <> olOutlookInternal Then
                If Not bolHeadersWritten Then
                      excSheet.Cells(1, intCol) = olkProperty.Name
                End If
                excSheet.Cells(intRow,intCol) = olkProperty.Value
                intCol = intCol + 1
          End If
    Next
    bolHeadersWritten = True
    intCol = 1
    intRow = intRow + 1
Next

'Clean up
'Change the file name and path as needed
excBook.SaveAs "C:\eeTesting\Contacts.xls"
excBook.Close
Set excApp = Nothing
Set excBook = Nothing
Set excSheet = Nothing
Set olkApp = Nothing
Set olkNS = Nothing
Set olkFolder = Nothing
Set olkContact = Nothing
Set olkProperty = Nothing
WScript.Echo "All done!"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Yockos
ID: 17091607
Blue Devil,

Hey, that works great.  However, we need two things yet.  I was unable to locate two fields that are required.  The first field which contains info regarding the "owner" of the contact (i.e.  who entered the contact).  I know that I can track this by other titidious means, but it would be nice if it put the "owner's" user name in a column next to the contact.  Second, I am having the staff go through their contacts and check the Private box if the contact is private so we can filter those out.  I did not see that column in the exported Excel file.  Any way to get that on there, too?

I think we will have this wrapped up, if these two additions are possible.

Thanks for all your help.
Shane
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17095268
Hi, Shane.

There is no "owner" property (field) for a contact.  Here's a link to an Microsoft page that lists all the properties of a contact: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol11/html/olobjContactItem_HV05247818.asp?frame=true

I can export the name of the account logged in when the script is run if you want to consider that to be the owner.  Let me know if you want to do that and I'll modify the script.

The field denoting a Private item is there, it's just not called Private.  In Outlook it's named Sensitivity.  A value of 2 indicates the Private box is checked.
0
 

Author Comment

by:Yockos
ID: 17095754
BDF,

I was thinking along those lines, but I have not had time to research it more.  I think your suggestion for logged-in account name will be sufficient, because the employees will be running that script.  They will log into the domain and we will then get our desired name.  I just need the data tagged with that user's account name.  What would be really nice is if the script would also utilize the name in the saving process so I will not have to do a script for every person.  Is that possible?

When I did a regular export and map the fields, it listed the field as Private with a boolean statement of True or False.  That was why I was confused about the Private issue.

Again, you have been a great help and I do appreciate it greatly.

Thanks,
Shane

PS  any suggestions on a good place/book to learn scripting?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17095806
> the script would also utilize the name in the saving process
As in using the name for the filename?
0
 

Author Comment

by:Yockos
ID: 17095815
That is correct.  An example would be John Jones.  Login would be jjones.  Script would save it as jjones.xls.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17095908
Ok.  When you login to your domain does the process set an environment variable containing the login name?  If so, I could read that variable and use it.  
0
 

Author Comment

by:Yockos
ID: 17096218
You know, I am not sure.  I am not the network admin, but I would suggest that it does.  Any way to find that out?
0
 

Author Comment

by:Yockos
ID: 17096230
It would have to.  I would assume it can be found in the registry.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17097884
Yes, there's an easy way to tell.  Go to a command prompt (click Start->Run type cmd and press Enter) and enter the word "set" (without the quotes).  That'll list all of your environment variables.
0
 

Author Comment

by:Yockos
ID: 17102357
The username does show up, so it would work.  It lists it as USERNAME=jjones underneath the domain.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 17105225
Ok, try this.

'Constants
Const olOutlookInternal = 0
Const olFolderContacts = 10

Dim olkApp, olkNS, olkFolder, olkContact, olkProperty
Dim excApp, excBook, excSheet
Dim intRow, intCol, bolHeadersWritten, objShell, strUser

'Get Outlook ready
Set olkApp = CreateObject("Outlook.Application")
Set olkNS = olkApp.GetNamespace("MAPI")
Set olkFolder = olkNS.GetDefaultFolder(olFolderContacts)

'Get Excel ready
Set excApp = CreateObject("Excel.Application")
Set excBook = excApp.Workbooks.Add()
Set excSheet = excBook.Sheets.Item(1)

'Main Routine
Set objShell = WScript.CreateObject("WScript.Network")
strUser = objShell.UserName
intRow = 2
intCol = 2
excSheet.Cells(1,1) = "Owner"
For Each olkContact In olkFolder.Items
    excSheet.Cells(intRow,1) = strUser
    For Each olkProperty In olkContact.ItemProperties
         If olkProperty.Type <> olOutlookInternal Then
              If Not bolHeadersWritten Then
                   excSheet.Cells(1, intCol) = olkProperty.Name
              End If
              excSheet.Cells(intRow,intCol) = olkProperty.Value
              intCol = intCol + 1
         End If
    Next
    bolHeadersWritten = True
    intCol = 2
    intRow = intRow + 1
Next

'Clean up
'Change the file name and path as needed
excBook.SaveAs "C:\eeTesting\" & strUser & "-Contacts.xls"
excBook.Close
Set excApp = Nothing
Set excBook = Nothing
Set excSheet = Nothing
Set olkApp = Nothing
Set olkNS = Nothing
Set olkFolder = Nothing
Set olkContact = Nothing
Set olkProperty = Nothing
Set objShell = Nothing
WScript.Echo "All done!"
0
 

Author Comment

by:Yockos
ID: 17122195
BlueDevil,

Sorry, I was out of the office for a couple of days.  

The script works great.  Very nice!  I think this will work.  Thanks for all your help.

Shane
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17125101
You're welcome, Shane.  Glad I could help out.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question