Exporting Outlook Distribution Lists to CSV Files using VBScript

Have you ever needed to export the members of a distribution list to a comma delimited file? If so, this script may help you out.
It connects to Outlook and then cycles through all the distribution lists creating a separate csv (comma separated value) file for each distribution list. The csv file contains a header row that identifies the columns of data and then the data itself, the name and email address of all the members of the distribution list.
This script takes one command line argument, the path where the csv files will be stored.
[More:]
This script only works if you run it from a command prompt using cscript. The following is an example of executing this script.
cscript exportDistLists.vbs /p:C:\scripts\outlookDLs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
This sample script uses thenamed command line arguments to pass the target folder path to script. This is the folder path that will be used to store the csv files.
‘==========================================================
‘==     Extract Distribution Lists Script Sample
‘==
‘==           Copyright © 2009, Dave Moats
‘==
‘== This sample is provided ‘AS-IS’, without any
‘== express or implied warranty. In no event will the
‘== authors be held liable for any damages arising from
‘== the use of this sample code.
‘==
‘== Permission is granted to anyone to use this sample
‘== code for any purpose, including commercial applications,
‘== subject to the following restrictions:
‘==
‘== The origin of this code must not be misrepresented;
‘== you must not claim that you wrote the original code.
‘==
‘== If you use this code, an acknowledgment in the
‘== documentation is requested – shown below:
‘==
‘==             Portions Copyright © 2009,
‘==        Dave Moats (http://www.davemoats.com/).
‘==
‘==========================================================
‘==========================================================
‘== NOTE: watch for wrapped lines and html special
‘==        characters in the web representation of this
‘==        sample code
‘==========================================================
‘==========================================================
‘== exportDistLists.vbs – a script used to export
‘==                   distribution lists from outlook
‘==                   and save them in csv format
‘==========================================================
option explicit
‘==========================================================
‘== declare the local variables to be used
‘==========================================================
dim scriptName, namedArgs, folderPath, fso, scriptHost, tmpArr
‘==========================================================
‘== get the name of the interpreter being used to run the
‘== script – have to parse it out of the full path that is
‘== stored in the fullname property
‘==========================================================
scriptHost = wscript.fullname
tmpArr = split( scriptHost, “”\”” )
scriptHost = tmpArr( ubound(tmpArr) )
if lcase( scriptHost ) “”cscript.exe”” then
   wscript.echo “”This script must be executed using cscript.exe””
   wscript.quit
end if
‘==========================================================
‘== get the name of the running script
‘==========================================================
scriptName = wscript.scriptname
‘==========================================================
‘== get the named command line arguments
‘==========================================================
set namedArgs = wscript.arguments.named
if not namedArgs.exists(“”p””) then
   wscript.echo “”Usage: “” & scriptName & “” /p: is required””
   wscript.echo “”Example: cscript “” & scriptName & “” /p:c:\path to the folder””
   wscript.quit
else
   folderPath = namedArgs.item(“”p””)
end if
set namedArgs = nothing
set fso = createobject( “”Scripting.FileSystemObject”” )
‘==========================================================
‘== now call the subroutine that does all the work
‘==========================================================
exportToCSV folderPath, “”.csv””
set fso = nothing
wscript.quit
‘==========================================================
‘== sub exportToCSV – subroutine that connects to outlook
‘==               and exports all distribution lists
‘==               to the path specified in the
‘==               exportPath argument
‘==========================================================
sub exportToCSV(exportPath, ext)
   
   ‘======================================================
   ‘== declare the local variables
   ‘======================================================
   dim outApp, fldDLs, dlEntry
   ‘======================================================
   ‘== create the outlook object and then get the contacts
   ‘======================================================
   set outApp = createobject(“”Outlook.Application””)
   set fldDLs = outApp.getnamespace(“”MAPI””).getdefaultfolder(10)
   ‘======================================================
   ‘== here we are looping the entries in the contacts
   ‘== folder looking for contacts – when a contact is
   ‘== found it will be exported
   ‘======================================================
   for each dlEntry in fldDLs.items
      if typename(dlEntry) = “”DistListItem”” then
         
         dim memCnt, distInfo, outFileHandle, fileName, outPath
         
         ‘===============================================
         ‘== make sure the file name that will be used
         ‘== doesn’t have any unacceptable characters
         ‘===============================================
         fileName = dlEntry.dlname
         fileName = replace( fileName, “”‘””, “””” )
         fileName = replace( fileName, “”””, “””” )
         fileName = replace( fileName, “”:””, “””” )
         fileName = replace( fileName, “”””””””, “””” )
         fileName = replace( fileName, “”/””, “””” )
         fileName = replace( fileName, “”\””, “””” )
         fileName = replace( fileName, “”|””, “””” )
         fileName = replace( fileName, “”?””, “””” )
         fileName = replace( fileName, “”*””, “””” )
         ‘===============================================
         ‘== start building the output string
         ‘===============================================
         distInfo = “”Name,Address”” & vbcrlf
         ‘===============================================
         ‘== loop through the distribution list getting
         ‘== the member information
         ‘===============================================
         for memCnt = 1 To dlEntry.MemberCount
            distInfo = distInfo & dlEntry.GetMember(memCnt).Name & “”,”” & dlEntry.GetMember(memCnt).Address & vbcrlf
         next

         ‘===============================================
         ‘== if the distribution list had members create
         ‘== a csv with the info found
         ‘===============================================
          if memCnt > 1 then
            outPath = exportPath & “”\”” & fileName & ext
            set outFileHandle = fso.createtextfile( outPath )
            outFileHandle.write( distInfo )
            outFileHandle.close
            set outFileHandle = nothing
         end if
      end if
   next
   ‘======================================================
   ‘== dumping the object references that were created
   ‘======================================================
   set fldDLs = nothing
   set outApp = nothing
end sub

Pay close attention to the lines wrapping in this sample, the script does not have any multi-line statements.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s