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

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
YockosSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Greetings, Yockos.

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

Cheers!
0
YockosSystems AdministratorAuthor Commented:
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
David LeeCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

YockosSystems AdministratorAuthor Commented:
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
David LeeCommented:
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
YockosSystems AdministratorAuthor Commented:
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
David LeeCommented:
> the script would also utilize the name in the saving process
As in using the name for the filename?
0
YockosSystems AdministratorAuthor Commented:
That is correct.  An example would be John Jones.  Login would be jjones.  Script would save it as jjones.xls.
0
David LeeCommented:
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
YockosSystems AdministratorAuthor Commented:
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
YockosSystems AdministratorAuthor Commented:
It would have to.  I would assume it can be found in the registry.
0
David LeeCommented:
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
YockosSystems AdministratorAuthor Commented:
The username does show up, so it would work.  It lists it as USERNAME=jjones underneath the domain.
0
David LeeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YockosSystems AdministratorAuthor Commented:
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
David LeeCommented:
You're welcome, Shane.  Glad I could help out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.