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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Export contacts to a coimma delimited file

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
scottspivey
Asked:
scottspivey
  • 11
  • 9
  • 3
1 Solution
 
David LeeCommented:
Hi scottspivey,

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

Cheers!
0
 
scottspiveyAuthor Commented:
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
 
David LeeCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Rob132332Commented:
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
 
scottspiveyAuthor Commented:
BlueDevilFan,

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

scott
0
 
scottspiveyAuthor Commented:
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
 
David LeeCommented:
scott,

It can be a network location (e.g. \\Server\Share) or a location on a web server.
0
 
scottspiveyAuthor Commented:
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
 
Rob132332Commented:
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
 
scottspiveyAuthor Commented:
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
 
David LeeCommented:
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
 
scottspiveyAuthor Commented:
BlueDevilFan,

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

scott
0
 
David LeeCommented:
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
 
Rob132332Commented:
Has this been resolved?  
0
 
scottspiveyAuthor Commented:
BlueDevilFan,

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

soctt
0
 
scottspiveyAuthor Commented:
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
 
David LeeCommented:
You got that error message after pasting this into a .VBS file?  Or did you pate this into VB?
0
 
scottspiveyAuthor Commented:
BlueDevilFan,

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

scott
0
 
David LeeCommented:
> 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
 
scottspiveyAuthor Commented:
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
 
David LeeCommented:
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
 
scottspiveyAuthor Commented:
fantastic.

thx for the help BlueDevilFan.

scott
0
 
David LeeCommented:
Anytime, Scott.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 11
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now