Distribution List Migration Tool – A Simple VB Program

In article Export Members of a Distribution List (10 March, 2007), I talked about two ways to export a Distribution List members to a file. However, both ways do not export people’s email addresses. I have written a VB program in MS Access which is used in conjunction with the tools such as DSGET, LDIFDE to actually migrate the members’ email addresses to a Distribution List to a TXT or a CSV file.

First, I use DSGET or LDIFDE export the members of the Distribution List to a TXT file and then import it to a Table in Access. It should look like this. (Figure 1)


Figure 1

Then I have included this piece of program in a module.(Sorry about the indenting. It does not work very well here.)


Sub dlmigration()
Dim fs
Dim fsOut
Dim i
Dim strFile
Dim oAccount
Dim arrFields
Dim strFull
Dim strLocation
Dim strUserName
Dim intLocationLength
Dim dbs As Database
Dim strLDAP
Dim intNameLength
Dim strOutputName
Dim Output

Dim rsQuerySQL As Recordset
Dim adQuerySQL
Set dbs = CurrentDb

adQuerySQL = "Select * From WildlifeDL"
Set rsQuerySQL = CurrentDb.OpenRecordset(adQuerySQL, dbOpenDynaset)

strFile = "c:\temp\outdata.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
Set fsOut = fs.OpenTextFile(strFile, ForWriting, True)

Do Until rsQuerySQL.EOF Or rsQuerySQL.BOF

strFull = rsQuerySQL("Name")
arrFields = Split(strFull, ",")
strUserName = arrFields(0)

intNameLength = Len(strUserName)
intNameLength = intNameLength - 4
strOutputName = Right(strUserName, intNameLength)

For i = 1 To UBound(arrFields)
strLocation = strLocation & arrFields(i) & ","
Next

intLocationLength = Len(strLocation)
intLocationLength = intLocationLength - 1
strLocation = Left(strLocation, intLocationLength)

strLDAP = strUserName & "," & strLocation

Set oAccount = GetObject("LDAP://" & strLDAP & "")


Output = strOutputName & ": " & oAccount.EmailAddress
fsOut.WriteLine (Output)
strUserName = ""
strLocation = ""

rsQuerySQL.MoveNext
Loop
On Error Resume Next

fsOut.Close
Err.Clear

rsQuerySQL.Close
Set rsQuerySQL = Nothing
End Sub

Then I get this output TXT file. (Figure 2)


Figure 2

It has saved me a lot of hair tearing time.