Solved

Export contacts to a coimma delimited file

Posted on 2006-11-29
23
436 Views
1 Endorsement
Last Modified: 2012-08-13
i need to export just the full name and email address of all of my contacts from Outlook to a csv file.  when i try to map the fields i don't have an option for full name.  instead of choosing first name, last name, etc. i chose name from the outlook fields and that does export the full name but it exports it as: last name, first name, middle name.  is there anyway to export the full name as: first name middle name last name?

i need the export to be in this format for an upload into my db.  this process will be something my users will have to go through to get their contact list into their address book on my site.  i know there is probably an easy way to do this with a vb script, but since this will be for my end users i need to figure out a way they can do the export without using script.

is there anyway to do this just using the tools that outlook has available so i can provide a tutorial for my users to go through this process?
1
Comment
Question by:scottspivey
  • 11
  • 9
  • 3
23 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 18038187
Hi scottspivey,

Would a script be a viable solution if all the users had to do was to click on a link?

Cheers!
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18038211
BlueDevilFan,

it may be if we are able to incorporate in into the upload page for the user to access.  what do you have in mind?

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18039219
scott,

I don't think that will work because web pages typically are not allowed to access anything on the local computer just through scripting.  That would require an ActiveX control (i.e. DLL).  I was thinking about a VB Script that would sit in a shared location and appear as a link on a page.  When the user clicked the link they'd get the usual Run, Open options dialog-box.  They'd select Run.  The script would then run under their credentials, and export the data per your requirement.
0
 
LVL 11

Expert Comment

by:Rob132332
ID: 18040402
Hello - one solution would be to create a document that your users could follow instructing them to export their contacts to excel format (in Outlook Contact, File -> Import and Export), use a formula to concatenate the name fields (something like =D2&" "&B2), and then delete all other non-needed columns, save as .csv from excel.  That's my suggestion.  Let me know if this helps, or if you need more detailed steps.  
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18040436
BlueDevilFan,

when you say, "shared location," what type of location are you referring to?

scott
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18040458
Rob132332,

i have considered that as a solution, and it certainly is an option.  my concern is the level of technical expertise that many of our users have.  while that is a very simple solution in my mind, it would be not-so-simple for our users.  and then i have to rely on them having excel to make that work.

but without many other good options, that is still a possible solution.

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18040787
scott,

It can be a network location (e.g. \\Server\Share) or a location on a web server.
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18040889
BlueDevilFan,

then that may be a workable solution.  how much user interaction will there need to be after they click on the button to run this script?

scott
0
 
LVL 11

Expert Comment

by:Rob132332
ID: 18041099
Another way to do it, if you're concerned with some users not having Excel, is to have the user shoot the file in .csv format directly from their Outlook contacts, and have the user save the .csv file to a shared network location.  Then, you could get everyone's individual .csv contact files and combine these files using a script, manipulating the data to the necessary format.  I'm guessing you're not on Exchange, but if you are, you may be able to query the master contacts list and get at the data yourself without any user interaction at all.  Rob
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18041126
Rob132332,

we are not on exchange so this wouldn't be an option for me.  the users that will be uploading these addresses are nationwide so a central server for them to save the csv file to is not the best option.

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18042312
Assuming that the contacts are all in the default contacts folder, then the only interaction required would be to click the Open button on the dialog-box that'll appear after they click the link.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:scottspivey
ID: 18045180
BlueDevilFan,

that sounds like it may work.  do you have that type of script already?

scott
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 18047139
scott,

Here's the code for doing this.  Copy and paste the code into Notepad and save it with a .vbs extension.  I need to make a correction to one thing I said earlier and clarify a point.  Clarification first.  If the user does not have Outlook open when they click the link to the script, then they will be prompted to login to Outlook.  Now the correction.  I had not looked back at the details of this question when I answered your post about what interaction is required.  As a result I wasn't taking into account that part of what you want to export are email addresses.  The email fields in Outlook are protected.  Accessing them from code is going to trigger a security dialog-box warning that an application is accessing your mailbox and asking for your permission to allow it to continue.  There is no way to avoid that without involving third-party products.  My apologies for the misleading answer.

Dim olkApp, olkNS, olkContacts, olkContact, objFSO, objFile

'Create the CSV file
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Change the CSV file name and path on the following line as needed
Set objFile = objFSO.CreateTextFile("C:\eeTesting\Outlook Export.csv")

'Open Outlook
Set olkApp = CreateObject("Outlook.Application")
Set olkNS = olkApp.GetNamespace("MAPI")
olkNS.Logon
Set olkContacts = olkNS.GetDefaultFolder(10)

'Loop through the individual contacts and write them to the CSV file
For Each olkContact In olkContacts.Items
    'Test the item type so we can skip over distribution lists
    x = olkContact.class
    If olkContact.Class = 40 Then
        objFile.WriteLine Chr(34) & olkContact.FullName & Chr(34) & "," & Chr(34) & olkContact.Email1Address & Chr(34)
    End If
Next

'Close Outlook
Set olkContact = Nothing
Set olkContacts = Nothing
olkNS.Logoff
Set olkNS = Nothing
Set olkApp = Nothing

'Save the CSV file and clean up
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
0
 
LVL 11

Expert Comment

by:Rob132332
ID: 18058035
Has this been resolved?  
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18065382
BlueDevilFan,

i will try this at the office tomorrow and let you know how it works.

soctt
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18165988
BlueDevilFan,

when i tried to run this i got this error:

'Compile error: Invalid outside procedure.'

and this is the line that was highlighted:

Set objFSO = CreateObject("Scripting.FileSystemObject")
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18167123
You got that error message after pasting this into a .VBS file?  Or did you pate this into VB?
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18235416
BlueDevilFan,

i pasted it into VB, not a VBS file.  is that the problem?

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18239221
> is that the problem?
Yes and no.  It's a problem inasmuch as the code must not have been pasted into a VB subroutine or function.  If you're going to use it in VB, then you have to put the code into a subroutine or function.  Something like this:

Sub ExportContacts()
Dim olkApp, olkNS, olkContacts, olkContact, objFSO, objFile

'Create the CSV file
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Change the CSV file name and path on the following line as needed
Set objFile = objFSO.CreateTextFile("C:\eeTesting\Outlook Export.csv")

'Open Outlook
Set olkApp = CreateObject("Outlook.Application")
Set olkNS = olkApp.GetNamespace("MAPI")
olkNS.Logon
Set olkContacts = olkNS.GetDefaultFolder(10)

'Loop through the individual contacts and write them to the CSV file
For Each olkContact In olkContacts.Items
    'Test the item type so we can skip over distribution lists
    x = olkContact.class
    If olkContact.Class = 40 Then
        objFile.WriteLine Chr(34) & olkContact.FullName & Chr(34) & "," & Chr(34) & olkContact.Email1Address & Chr(34)
    End If
Next

'Close Outlook
Set olkContact = Nothing
Set olkContacts = Nothing
olkNS.Logoff
Set olkNS = Nothing
Set olkApp = Nothing

'Save the CSV file and clean up
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
End Sub
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18239287
BlueDevilFan,

i didn't realize i needed to paste it into a subroutine or function.  but when i used your code above it works like a charm.  so if i pass this along to my developers, they should be able to put this code into a vbs file on our server, and when the user on our site runs the vbs it will open their outlook and export the csv file to a path that we will define for them or let them choose it themselves.  right?

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18239463
Correct.  Just remember that you only need to put the code into a sub/function if you're using it from inside VB.  If you're using it with VBScript, then the code I posted will work as is.  
0
 
LVL 1

Author Comment

by:scottspivey
ID: 18239468
fantastic.

thx for the help BlueDevilFan.

scott
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18239476
Anytime, Scott.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

16 Experts available now in Live!

Get 1:1 Help Now