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

Installation

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.

ESEUtilGUIIco[1] CreateMBX-CSV-WordMerge.exe

 

Upon extraction you will see that you have the following two files in the destination directory:

WM-ExtractFiles

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

WM-Funct

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

PSSetExePol

  • Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
  • Add-PSSnapin Quest.ActiveRoles.ADManagement

PSAddSnaps

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

ChooseCSVWM

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

EnterOU-ChsDB

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

ScriptWMCompleted

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

CdrWMdocs

Upon opening one of these files you will see the following information:

WMcontDoc

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.

Sharing is caring!:

3 thoughts to “Creating Mailbox Users in Powershell from a CSV – and then Merging the details into Microsoft Word…”

  1. Thanks for this awesome script! I’m creating a test lab and this has come in EXTREMELY handy along with an Excel spreadhseet I found online with random names.

    When I execute the script I am getting the following error:
    Argument: ‘1’ should be a System.Management.Automation.PSReference. Use [ref].
    At C:\Users\juan\Downloads\CreateMBX\CreateMailboxes-WordMerge.ps1:156 char:14
    + $oDoc.SaveAs <<<< ($filename)
    + CategoryInfo : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : NonRefArgumentToRefParameterMsg

    Line 156 starts here:
    (156) $oDoc.SaveAs($filename,
    $oMissing, $oMissing,
    $oMissing, $oMissing,
    $oMissing, $oMissing,
    $oMissing, $oMissing,
    $oMissing, $oMissing)

    I have not modified this at all. Any help with this is greatly appreciated. FYI, the first user account/mailbox gets created just fine. It gets hung up when creating the Word document.

    Thanks again!

  2. I was able to resolve this by changing the code to the following:

    $filename = $Fname
    $oDoc.SaveAs([ref]$filename,
    [ref]$oMissing, [ref]$oMissing,
    [ref]$oMissing, [ref]$oMissing,
    [ref]$oMissing, [ref]$oMissing,
    [ref]$oMissing, [ref]$oMissing,
    [ref]$oMissing, [ref]$oMissing)

    Once I did that the script ran successfully and created the Word documents.

    Thanks again!

  3. Thank you for this script! It worked AWESOME. We tested it first in a lab, then created over 200 new users accounts in a production environment in a few minutes. Great script.
    Thank you,
    Daniel

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.