subject: exchange

Playing around with VBScript (and PrimalScript)

Although PowerShell is becoming the de facto standard for scripting with Exchange Server, I still like to use VBScript for some tasks.

Recently I had to add a new Proxy Address to all the users in active directory and make it the primary SMTP address. This time, instead of using a text file as input, I decided to use Excel. The script is expecting 2 columns: the first one with the e-mail alias (mailNickname) and the second one with the new smtp address. The script will add a third column with the values "Found" or "Not Found".

Here's the code:

Const ADS_SCOPE_SUBTREE = 2
Const ADS_PROPERTY_UPDATE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\smtp_table.xls")
objExcel.Visible = True

i = 1

Do Until objExcel.Cells(i, 1).Value = ""
    strName = objExcel.Cells(i,1)
    objCommand.CommandText = _
        "SELECT distinguishedName FROM 'LDAP://dc=virtual,dc=com' WHERE objectCategory='user' " & _
            "AND mailNickname='" & strName & "'"
    Set objRecordSet = objCommand.Execute

    If objRecordset.RecordCount = 1 Then
        objExcel.Cells(i,3) = "Found"
        strDN = objRecordSet.Fields("distinguishedName").Value
        Set objUser = GetObject("LDAP://" & strDN)
        strMail = objExcel.Cells(i,2)

        arrProxyAddresses = objUser.GetEx("proxyAddresses")
        nArr = UBound(arrProxyAddresses)
            For n=0 To nArr
                If InStr(arrProxyAddresses(n), "SMTP:") Then
                    arrProxyAddresses(n) = "smtp:" & Mid(arrProxyAddresses(n), 6) 
                End If
            Next

        ReDim Preserve arrProxyAddresses(nArr + 1)
        arrProxyAddresses(nArr+1) = "SMTP:" & strMail
        objUser.PutEx ADS_PROPERTY_UPDATE, "proxyAddresses", arrProxyAddresses
        objUser.Put "Mail", strMail
        objUser.SetInfo
    Else
        objExcel.Cells(i,3) = "Not found"
    End If
    
    i = i + 1
    objRecordset.Close
Loop

objConnection.Close

And since we are living in modern times, I thought it was time to leave Notepad behind and start using a more professional tool for scripting. I chose PrimalScript!

PrimalScript has so many nice features that make my work easier, that I couldn't possible enumerate them all in this post. Just give it a try and judge for yourself.