Creating Exchange Users with Excel

In previous articles we discussed tools that create users and their mailboxes in Active Directory. LDIFDE has been mentioned and also VBScripts. However, the most powerful and flexible tool in my opinion is Excel. Excel can read information from various formats and can serve as a handy intermediary. You can manipulate the information in Excel as you find necessary before exporting it to Active Directory. A lot of data originates from Excel which is a common place for large tables. You can also combine data from a few sources into Excel.

Note: This article is published with permission from www.msexchange.orglink out ico
Introduction 
Excel and the rest of the Office family support VBA, which is a language somewhere between VBScript and Visual Basic 6 which provides Office objects but can also access any API present on the system with the right DLL. If you’re unfamiliar with programming lingo, this means that VBA can do just about anything once you understand how it works.
Unlike VBScript, VBA comes with a built-in runtime compiler which goes through the logic of a macro (which is how scripts are called in VBA) in the same way that Visual Basic does.  VBA, while not exactly free, is built-in to any Office application so you don’t need to install a separate environment such as Visual Studio. This makes VBA the ideal scripting/programming environment. There were a few occasions where I chose to migrate more complex VBScript scripts into Excel or Word, just for the sake of being able to trace through the inevitable programming mistakes.
Preparations
First thing to do is open a list of users such as can be seen below.
My list doesn’t include a description row, but if it had one that would also be acceptable.

The latest Office applications come with Macro protection that is supposed to protect you from viruses and other malicious software. However, it also disables Office macros. Before beginning your work you should check that your antivirus is updated (and that it is a good one). Then, you should disable Macro security.


If you’re really afraid of Macros, once you’re finished with your macro, you can choose whichever setting suits your environment most.
To go to the VBA environment you have a choice to two (!) environments.  For the purposes of this article we will use the Visual Basic Editor, accessible by pressing Alt + F11.  What’s great about VBA is that it also records your actions within Excel into a regular VBA macro that you can use. This means that instead of buying books or going through Internet newsgroups you can just record a macro of a certain activity if you don’t know how to recreate it in a Macro.
Since our macro will focus on Active Directory and Exchange we need to tell VBA which to use the Exchange and AD APIs, in the same way this is done with Visual Basic 6. To do so choose from the VBE menu, Tools > References.

Selecting the Exchange and ADSI type libraries will enable VBA to access their APIs.
If you do not see the reference libraries above this might mean that you are running Excel on a workstation or a server that does not have Exchange System Manager installed. You should install latest ESM and Exchange service pack before running any Macro.
For the purposes of this article I created using the Active Directory Users and Computers console an OU called “Test” and two departmental groups, dept1 and dept2.

Now we are ready to start creating the macro.
The Macro
A Macro is in fact a subroutine similar to those used in Visual Basic.
Here is the full script. It takes the information entered in the Excel spreadsheet and converts it to Exchange users. Without much scripting knowledge you can tweak it and use it your own environment.

​Sub CreateUsers()
Dim Row As Integer
Dim oMailbox As CDOEXM.IMailboxStore
Dim oUser As IADsUser
Set rootDSE = GetObject("LDAP://RootDSE")
DomainContainer = rootDSE.Get("defaultNamingContext")
Set oOU = GetObject("LDAP://OU=Test,DC=mycompany,DC=local")
Row = 1
Do Until Cells(Row, 1) = Empty
 gname = Trim(Cells(Row, 1).Value)
 sname = Trim(Cells(Row, 2).Value)
 ID = Cells(Row, 3).Value
 mailingaddress = Cells(Row, 4).Value
 city = Cells(Row, 5).Value
 postalcode = Cells(Row, 6).Value
 homephone = Cells(Row, 7).Value
 cellular = Cells(Row, 8).Value
 dept = Trim(Cells(Row, 9).Value)
 FullName = gname & " " & sname
 AliasCount = 2
 Alias = LCase(gname & Left(sname, AliasCount))
 Set conn = CreateObject("ADODB.Connection")
 conn.Provider = "ADSDSOObject"
 conn.Open "ADs Provider"
 ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
 Set rs = conn.Execute(ldapStr)
 While rs.RecordCount > 0
 AliasCount = AliasCount + 1
 Alias = LCase(gname & Left(sname, AliasCount))
 ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
 Set rs = conn.Execute(ldapStr)
 Wend
 ' Update User Record
 Set oUser = oOU.Create("user", "cn=" & FullName)
 oUser.Put "cn", FullName
 oUser.Put "SamAccountName", Alias
 oUser.Put "userPrincipalName", Alias & "@mycompany.local"
 oUser.Put "givenName", gname
 oUser.Put "sn", sname
 oUser.Put "description", ID
 oUser.SetInfo
 oUser.GetInfo
 ' Enable Account
 oUser.AccountDisabled = False
 ' Set Pwd to be same as 123456
 oUser.SetPassword ("123456")
 'Account is not disabled
 oUser.AccountDisabled = False
 ' User must change password at next Logon
 oUser.Put "pwdLastSet", CLng(0)
 oUser.SetInfo
 Set oMailbox = oUser
 MDBName = "Mailbox Store (EXCHANGE)"
 StorageGroup = "First Storage Group"
 Server = "Exchange"
 AdminGroup = "MyCompany"
 Organization = "MyCompany School of Arts"
 DomainDN = "DC=mycompany,DC=local"
 oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
 ",CN=" & StorageGroup & _
 ",CN=InformationStore" & _
 ",CN=" & Server & _
 ",CN=Servers" & _
 ",CN=" & AdminGroup & _
 ",CN=Administrative Groups" & _
 ",CN=" & Organization & _
 ",CN=Microsoft Exchange,CN=Services" & _
 ",CN=Configuration," & DomainDN
 oUser.SetInfo
 StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
 Set objGroup1 = GetObject(StrobjGroup1)
 objGroup1.Add (oUser.ADsPath)
 Set oUser = Nothing
 Row = Row + 1
Loop
End Sub

How the Script Works


First of all, you need to declare objects. VBA Objects are defined in the same way done in VB6.  That said you can also use undeclared objects though this doesn’t work well with complex ones.
For the purposes of this macro we declare a mailbox object (oMailbox) and a user object (IADsUser).

​Dim Row As Integer
Dim oMailbox As CDOEXM.IMailboxStore
Dim oUser As IADsUser

As mentioned in my previous programming articles, there are various ways for creating users and their mailboxes. A common one is creating a user using ADSI and then substituting it with an Exchange mailbox object and creating the mailbox using CDOEXM. The nice thing about being able to declare objects is that you always know which object is which rather than letting the runtime compiler decide for you which can sometimes be tricky with VBScripting.
The next part of the script obtains the domain container in Active Directory, and the OU in which we aim to place the users.

​Set rootDSE = GetObject("LDAP://RootDSE")
DomainContainer = rootDSE.Get("defaultNamingContext")
Set oOU = GetObject("LDAP://OU=Test,DC=mycompany,DC=local")

Now let’s look at the next lines of code:

​Row = 1
Do Until Cells(Row, 1) = Empty
 gname = Trim(Cells(Row, 1).Value)
 sname = Trim(Cells(Row, 2).Value)
 ID = Cells(Row, 3).Value
 mailingaddress = Cells(Row, 4).Value
 city = Cells(Row, 5).Value
 postalcode = Cells(Row, 6).Value
 homephone = Cells(Row, 7).Value
 cellular = Cells(Row, 8).Value
 dept = Trim(Cells(Row, 9).Value)
 FullName = gname & " " & sname
 AliasCount = 2
 Alias = LCase(gname & Left(sname, AliasCount))

We use a “Do” loop in order to go through the Excel sheet until an empty row is discovered. The Cells VBA function is used to extract information from the Excel cells.
For example, “gname” which we use to store the given name of a user obtained from the first cell in each row. I use trim because sometimes when information is manually entered, empty spaces find their ways into the cells which can cause problems later on, when creating the username and alias for the user.
Speaking of the alias for the user, the subroutine constructs it from the given name of the user and the first two letters of the surname of the user (obtained by using the Left function.) This is quite common in many organizations for creating aliases and usernames which need to be short, yet unique. However, if you have two users with same give name and first two letters in the surname you will need to add more letters to the username. The next lines of code achieve this.

​Set conn = CreateObject("ADODB.Connection")
 conn.Provider = "ADSDSOObject"
 conn.Open "ADs Provider"
 ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
 Set rs = conn.Execute(ldapStr)
 While rs.RecordCount > 0
 AliasCount = AliasCount + 1
 Alias = LCase(gname & Left(sname, AliasCount))
 ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
 Set rs = conn.Execute(ldapStr)
 Wend

The subroutine queries Active Directory for users with the same alias. It it finds any, the AliasCount variable is incremented by one and another letter is added.
Of course, these lines of code are not perfect. For example, they do not account for users with same name and surname. You can, for example, enhance the code by adding a number instead of more letters to the alias.
Once the alias field has been decided on you can create the user in Active Directory. Note again that the Alias and the username (SamAccountName) fields are the same. The description field is used to enter the numerical ID for the user, typically a social security or internal employee number.

​Set oUser = oOU.Create("user", "cn=" & FullName)
 oUser.Put "cn", FullName
 oUser.Put "SamAccountName", Alias
 oUser.Put "userPrincipalName", Alias & "@mycompany.local"
 oUser.Put "givenName", gname
 oUser.Put "sn", sname
 oUser.Put "description", ID

A user is created with the general password “123456” which a user is required to change upon first logon. In a more security oriented organization you might choose to generate a different password.

​ oUser.AccountDisabled = False
 oUser.SetPassword ("123456")
 oUser.AccountDisabled = False
 oUser.Put "pwdLastSet", CLng(0)
 oUser.SetInfo

Now, like good Exchange scripting magicians, we perform the world famous switch from AD object to Exchange Mailbox object and create the mailbox.

​Set oMailbox = oUser
 MDBName = "Mailbox Store (EXCHANGE)"
 StorageGroup = "First Storage Group"
 Server = "Exchange"
 AdminGroup = "MyCompany"
 Organization = "MyCompanyOrg
 DomainDN = "DC=mycompany,DC=local"
 oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
 ",CN=" & StorageGroup & _
 ",CN=InformationStore" & _
 ",CN=" & Server & _
 ",CN=Servers" & _
 ",CN=" & AdminGroup & _
 ",CN=Administrative Groups" & _
 ",CN=" & Organization & _
 ",CN=Microsoft Exchange,CN=Services" & _
 ",CN=Configuration," & DomainDN
 oUser.SetInfo

To use the script you would have to modify the fields to suit your Exchange environment. Please note also that some fields are only available once the Exchange mailbox once it is actually created which might take time because the RUS needs to stamp it. For example, if you want to use one of the Exchange extended attributes to store the User’s ID instead of the description you would have to make the script activate the RUS or write another script and activate it a few minutes after the first one is ran.
The rest of the code adds the user to a group specified in the Excel sheet and forwards the Row counter to the next row.
Then the loop is closed and the subroutine is ended.

​ StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
 Set objGroup1 = GetObject(StrobjGroup1)
 objGroup1.Add (oUser.ADsPath)
 Set oUser = Nothing
 Row = Row + 1
Loop
End Sub

Conclusion
A few lines of code in an Excel VBA, at least theoretically, could save you hours of manually entering users. The main thing about scripts is their flexibility. Any field can be set according to a business logic of your choosing. While the macro illustrated in the article is pretty basic it can grow to infinite complexity. You can use it to synchronize two systems, set permissions and much more, according to specifically tailored rules.

Author Bio
Currently working as Project Manager and Systems Consultant, heading and consulting on Exchange and NT/Windows 2000 based migrations and deployments for large companies such as Checkpoint, Comverse, Smarteam, Nice, Aladdin and leading Israeli Banks, Also involved in writing scripts and custom solutions for clients based on ADSI, CDO and Visual Basic and teaching Windows 2000 and Exchange 2000 in MSCE colleges and lecturing in Microsoft User Groups.

Note: This article is published with permission from www.msexchange.orglink out ico