Solved

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

Posted on 2006-07-10
16
399 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now