Using Excel to Create Exchange Scripts

"If you get can data into Excel, you can manipulate your data into anything"

Scenario:  Take a list of mailbox aliases and add an additional email alias to each one via an Exchange PowerShell Script.

Desired Result:  Run a PowerShell Script that contains the set-mailbox command for each mailbox alias while the script does not contain a loop.

There are plenty of Exchange articles out there that show you how to import data from a .csv file and run a loop on each data object imported to achieve a result; usually importing the data object and performing some kind of query or modification to each object.  Here is an alternative way of building a script file that does not use any loop commands, rather it creates the Exchange Command for each individual object in a linear format.  From here you can copy all of the Exchange commands and paste them into a .ps1 file and execute them in Exchange Management Shell.  Excel is one of the best tools to use for data modification where you can format, isolate, formulate, and calculate data when required.

Why do it this way??

  • It gives you granular control over what is being ran for each object.
  • The script is completely linear, meaning its not taking extra steps or jumping around.
  • If your paranoid about what is being ran, it gives you a chance to see each command for each object.
  • It can be quicker then importing data, creating a script, and then executing.



1. Get the Data into Excel 

Excel is key here. We need the 'alias' of the objects that will have the email alias applied to it in an Excel format -- such as a .CSV or another native Excel extension.  If you are going to query the objects in Exchange, you can run a command similar to the following that will output it in a csv format:

     get-mailbox -resultsize unlimited | Select Alias | Export-csv \\Server\Share\File.csv

If the data is from somewhere else that cannot be opened in Excel, you can copy and paste it into Excel. Depending on the format of the data you are copying, you may need to adjust it in a way where you can isolate the value you are trying to grab. Again we want to isolate the alias into a column and discard the other information. Common excel tools I use in Excel are "Text to Columns", "Remove Duplicates", and a simple "Replace".  Here is an example of data I have pulled out of a third party system, which I will copy and paste into Excel.

Alias;Database;Servername
jdoe;DB04;MBX04
awall;DB06;MBX02
bmore;DB11;MBX03

The problem with the data above is that when I paste it into Excel, the Alias, Database, and ServerName for each line into a single cell for each row.



Using the Text to Columns feature we can separate each value so we can isolate the alias. To do this, perform the following:

1. Highlight the column where the data resides.



2. Locate and click on 'Text to Columns'.  In Outlook 2013 its under Data on the ribbon.


3. When the Convert Text to Columns wizard opens, 
      a. Select Delimited and click Next. 
      b. Select Semicolon as your delimiter. This is because each column in the copied data was separated with a semicolon.  Click Finish.



4.  Now each column is separated and each value is isolated.  We can remove the columns we do not need,  we will remove columns B and C. All we need is the alias.  The image below was taken before I remove the B and C column.



2. Apply the Exchange shell command-let for each Alias.

This is the Exchange Shell command-let I am trying to achieve for each object:

     Set-Mailbox Alias -emailaddresses @{Add="SMTP:Alias@baldguys.com"}

The image below is the result that we are trying to achieve:


















How to Achieve this:
Now that we have the alias as the only column (Column A) in our Excel sheet, we are going to apply a
formula that will result in the Exchange command for each alias.This is what we need to know in Excel in order to create the Exchange PowerShell command via a Excel Formula.  We will build out a string of text and pull in the alias from each row in Column A.
  • Start in cell B2 (the first alias I want to pull in is in Cell A2).
  • The formula command I am going to enter is:  ="Set-Mailbox "& A2 &" -emailaddresses @{Add=""SMTP:"& A2 &"@baldguys.com""}"
  • This formula will need to be copied down within that column for the amount of data in column A.


Tips:  Color coded to show you you the examples below.
  • When a formula uses a string (static text such as Set-Mailbox or -EmailAddresses) it needs to start and end in quotations.
  • When a formula pulls in a value (dynamic text such as the values in the alias columns), the string needs to be broken by closing the quotations and using & to append the cell value.  When rejoining the cell value to more text, using & and quotations will continue on the string.
  • If your static string requires the use of quotations for the command, meaning the string you would type out would use quotations and not using quotations to break the string, use two quotations together "".
  • Spaces Count. If your static string requires a space, make sure to include it.
Example (with get-mailbox command): ="get-mailbox "& A2  produces the resulting command  get-mailbox jdoe

Example (with our scenario): ="Set-Mailbox "& A2 &" -emailaddresses @{Add=""SMTP:"& A2 &"@baldguys.com""}"  produces the resulting command:   Set-mailbox jdoe -emailaddresses @{Add="SMTP:jdoe@baldguys.com"}


Notice in the image shown above,  the resulting text in column B looks like Exchange PowerShell command-lets that would be ran, but the formula shown in the editing box shows the code that produces the resulting text that forms that Exchange PowerShell Command.


3.  Copy the Exchange PowerShell Command-lets and paste into a .PS1 file.

Only copy the column with the formulas that produces the resulting Exchange PowerShell Command-lets and paste it into a notepad file. Save the notepad file with a .ps1 extension.  In the image below you see the Excel Document, After I pasted the Exchange commands into notepad, and saving it as Script.ps1.




4.  Execute the PowerShell Script (.PS1) in Exchange Management Shell.


Navigate to your script and execute it.  To execute the script, it needs a .\ in front of the script name.




There you have it.   You have now created a PowerShell Script formed out of Excel.  To expand this script a little further, if you want it to display which item the script is executing, you can include the Write-Host PowerShell command.   Basically you would append whats in blue to the current formula. It will display each alias that is running.

"Write-Host "& A2 &"; Set-Mailbox "& A2 &" -emailaddresses @{Add=""SMTP:"& A2 &"@baldguys.com""}"
Email Post