Recipe5.10.Bulk-Adding Mailboxes from an Excel Worksheet

Recipe 5.10. Bulk-Adding Mailboxes from an Excel Worksheet

Problem

You have an Excel spreadsheet (or some other data source that can easily be turned into an Excel sheet) and you want to add many mailboxes at once based on its contents.

Solution

Using VBScript

' This code will read the contents of an Excel file and use the fields ' to create a set of mailboxes  ' ------ SCRIPT CONFIGURATION ------  strComputerName = "<ServerName>"  ' e.g., batman  strWorksheetName = "<pathToExcelFile>" ' e.g., "c:\data\excelSheet.xls"    strTargetMDB = "<mailboxDBDN>"  strTargetContainer = "cn=Users, <ForestRootDN>"  ' ------ END CONFIGURATION ---------   ' create an invisible Excel instance and open our specified file    set objExcel = CreateObject("Excel.Application")    Set objWorkbook = objExcel.Workbooks.Open(strWorksheetName)   ' assume that row 1 is header data, and that rows 2-N contain data   ' Columns:   ' 1: first name   ' 2: last name   ' 3: CN   ' 4: alias      currentRow = 2   Do until objExcel.Cells(currentRow, 1).Value =""      userFirst = objExcel.Cells(currentRow, 1).Value      userLast =  objExcel.Cells(currentRow, 2).Value      userCN =  objExcel.Cells(currentRow, 3).Value      userAlias =  objExcel.Cells(currentRow, 4).Value       Set objTargetContainer = GetObject(strTargetContainer)     Set objUser = objOU.objTargetContainer _         ("User", "cn=" & userCN)     objUser.sAMAccountName = userAlias     objUser.GivenName = userFirst     objUser.SN = userLast     objUser.AccountDisabled = true     objUser.SetInfo         objUser.CreateMailbox targetMDB     currentRow = currentRow + 1   Loop   objExcel.quit

Discussion

Many administrators never realize that Office applications like Excel, Word, PowerPoint, Visio, and Access are scriptable, too; in fact, the Visual Basic for Applications (VBA) script language shipped long before Microsoft decided to support broader scripting for administrators. You might be wondering why you'd want to use Excel as the front end for mass creation of mailboxes, but it actually makes a lot of sense to do so. First, Excel can easily digest CSV files, so if you've used csvde, the Exchange 5.5 Administrator application, or some other means of generating a CSV file, Excel makes it easy to clean up or reformat the contents of that file with minimal fuss. Second, Excel is scriptable and can easily connect to databases, so if you want to be able to tie account creation to some kind of existing business process that revolves around creating user records in a database, it's a natural candidate. Third, Excel is a familiar tool to many otherwise nontechnical userslike your HR department. Providing a tie between Excel and Exchange means that you can more easily accept data from them without writing your own custom tools.

You can do something very similar with the exchmbx tool's -cr switch, since you can pipe a text file into it. To use exchmbx , create a text file that contains the CNs of the mailboxes you want created. Optionally, you can specify the mailbox database where you want them created. Here's a sample file, mailboxes.txt, that lists three users, one of which has a specific database given.

"CN=Paul Robichaux,ou=partners,dc=3sharp,dc=com" "cn=Peter Kelly,ou=partners,dc=3sharp,dc=com" RED-EXCH01:SG1:MDB2 "cn=John Peltonen,ou=partners,dc=3sharp,dc=com"

Then use the -cr switch like this:

> Exchmbx -cr RED-EXCH01:SG1:MDB1 < mailboxes.txt

The two accounts that don't have a mailbox database specified will end up in the database specified as an argument to exchmbx; the other will be created in its specified database.