Creating Mailbox Users in Powershell from a CSV – and then Merging the details into Microsoft Word…
Following on very loosely from my article here which provides a sample of 300 LAB users within a CSV file and a Powershell script to create them within Active Directory, I would like to present to you an article which is based on a more common production scenario – batch creation of Mailboxes.
In essence there are times when Mail administrators are presented with an Excel Spread Sheet (or more to the point CSV file) with the details of a number of starters – which then need to be created within their mail environments.
Post the creation of these accounts – each customer then needs to be notified of the new account details – all of which can be a very time and labour intensive process.
In this article I would like to present a script which gives you and overview on how to accomplish the following tasks:
- Create Mailbox Users from a CSV File (my original script only created the AD accounts)
- Allows for you to choose the destination OU for the AD Users accounts
- Allows for you to choose the destination Database within your Exchange Environment for the Mailboxes
- Merges all of the created account information into a number of Word Documents which can then be distributed to the customers
Before I continue – if you are interested in using this script – you should take note of the following:
- The script is NOT compatible with the x32 version of Exchange 2007 – this script can only be used against x64 installations
- Powershell 2.0 (This script is NOT compatible with Version 1.0 as it uses a 2.0 specific CMDLet)
- Quest Active Roles Server CMDLets installed
- Microsoft Word 2003 – 2010 installed
- Exchange 2007 / 2010 Management Tools installed
Download the following SEA to either your Management Workstation or Exchange server (it is recommended that you use a Management workstation as I do not personally recommend that Word is installed on an Exchange Server) – execute the program and extract the files to a desired location.
Upon extraction you will see that you have the following two files in the destination directory:
The main script is called “CreateMailboxes-WordMerge.ps1” – and the other file is a demonstration CSV file which gives you the format which the script expects to be presented with the users Data.
Using the Script
Customising the Script
Before execution it is recommended that you open and customise the script using Windows Notepad – or perhaps even better Quest PowerGUI.
Once open locate the function called “Function export-ToWord($Person,$sam,$uPass,$Fname)” – see below
You will see a comment block entitled “Change the following variable to match that of your own company” – between here you can set the following options:
- $Company – Company Name
- $Webmail – OWA Address
- $PersonTo – This is the name of the customer whom the password details Word document will be addressed to
- $SupportDesk – The contact information of your companies IT support Desk
- $MainBody – Sample Letter wording
- $AccessPara – Sample Letter wording for gaining access
- $Closing – Sample wording to close the letter
- $Sincerly – Whom it is from
You can change any of the above to match the format of your own firm – of course the above is purely an example of what can be achieved via the Powershell COM interface into Word and you are free to experiment as much as you would like.
I would also like to thank Mark Alexander Bain of suite 101.com for the sample code for the export-ToWord function.
When you have made your changes above you are ready execute the script against your environment.
Open a Powershell Window and type in the following CmdLets:
- Set-ExecutionPolicy RemoteSigned (confirm yes when prompted) – see below
From within the Powershell console navigate to the directory which contains the script file and type in the following command to execute:
PS X:\merge> .\Create-MailboxesFromCSV-MergeWord.ps1
You will be prompted for the source CSV file which contains the source user data – navigate to the file and then click on the OK button – see below
You will then be asked to provide the name of the destination OU where the users accounts in AD will be placed – this should be entered in the format of the OU name as it appears in AD Users and Computers; the script will then check for it existence.
If the OU exists you will then best asked to select the Mailbox Database where you would like to place the Mailboxes – this is in the format of a numbered list – choose the corresponding number which matches the correct database – see below
The script will then process the entries in the CSV file – when the script has finished processing you will be presented with the “Script Completed” message – see below
Accessing the Merged Documents
By default the script places the Word user documents in the root of the C:\ on the machine where the script was executed – the will be named according to <samAccountName.doc> – see below
Upon opening one of these files you will see the following information:
The document can then be printed out and sent to the customer / or adapted to suit whatever process your organisation follows.
I hope that you find the above useful; if nothing else I hope that some of the code within the Powershell script will give you some ideas for customisations.