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.
