Recipe 5.10. Bulk-Adding Mailboxes from an Excel WorksheetProblemYou 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. SolutionUsing 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 DiscussionMany 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. See AlsoRecipe 5.1 for creating an individual mailbox, Recipe 5.2 for mailbox-enabling an existing user, Chapter 6 of Active Directory Cookbook, and MSDN: Create User Accounts from Information in an Excel Spreadsheet (http://msdn.microsoft.com/library/en-us/dnclinic/html/scripting04132004.asp) |