We have a process by which notifications of new users in another system, that need to get created in ours are sent via Email to a standalone inbox. The new user emails have to be read and entered into the system based on the information provided (Name, and Email). We get 10-20 a week, and the UI for which we enter these users into is very slow – so its a big time waster. I decided to waste even more time automating this so I NEVER HAVE TO DO IT AGAIN!
I decided PowerShell would be the natural choice, there appear to be tools specifically for Exchange/Office365 called Exchange Management Tools/Exchange Management Console which have to be installed – so my goal was to not use any cmdlets that appear to make things really easy.
The steps that need to happen are:
- Check inbox for un-read messages matching a particular subject
- Verify the email message is a new created user (there are updates and removals – purely notification) by checking the message body.
- Parse out the email address and name from the email message body
- Add the user to our system (using an in-house Python REST utitlity)
- Archive the email into a specific folder for these requests.
Connect to the Inbox
In the below, $s becomes your Exchange “Service” object. The one dependency is having the Microsoft.Exchange.WebServices dll available – this you can download http://www.microsoft.com/en-us/download/details.aspx?id=35371, then it can be loaded locally as a .NET assembly.
The other trick is the Exchange web-service endpoint for Office365 https://outlook.office365.com/EWS/Exchange.asmx.
$inbox becomes the Inbox object itself.
[Reflection.Assembly]::LoadFile("C:Program FilesMicrosoftExchangeWeb Services1.2Microsoft.Exchange.WebServices.dll") $s = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1) $s.Credentials = New-Object Net.NetworkCredential('inboxusername@xyzcorp.com', 'P@$$Word', 'xyzcorp.com') $s.Url = new-object Uri("https://outlook.office365.com/EWS/Exchange.asmx"); $inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($s,[Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox)
Find Folder for Completed Requests
Doing this now to set up the source and destination folders before we go play with messages, here we create a folder view, and SearchFilter object to find the specific folder by name. I’m assuming its returned and its the first item in the FolderView collection.
$fv = new-object Microsoft.Exchange.WebServices.Data.FolderView(20) $fv.Traversal = "Deep" $ffname = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+ContainsSubstring([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,"Completed Items") $folders = $s.findFolders([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$ffname, $fv) $completedfolder = $folders.Folders[0]
Search Inbox for Un-read request Messages
Next we’ll search the Inbox for unread messages matching the subject line. I also need to check for text in the message body to confirm its an add and not an update user request – but I found it difficult to do so at this point, instead I’ll do it as I process each message. I’m using a SearchFilterCollection, which for both SearchFilter and SearchFilterCollection have interesting syntax within Powershell http://blogs.msdn.com/b/akashb/archive/2011/12/01/searching-contacts-using-ews-managed-api-1-1-from-powershell-impersonation-searchfiltercollection-containssubstring.aspx and http://gsexdev.blogspot.com/2009/06/using-searchfilter-and-other-nested.html
$iv = new-object Microsoft.Exchange.WebServices.Data.ItemView(50) $inboxfilter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And) $ifisread = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::IsRead,$false) $ifsub = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+ContainsSubstring([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject,"User Profile Sync") $inboxfilter.add($ifisread) $inboxfilter.add($ifsub) $msgs = $s.FindItems($inbox.Id, $inboxfilter, $iv)
Read and process emails
$msgs now contains all the unread messages matching our subject line. Now we have to process them all, there is a trick to getting certain properties of the message – for which we apply a PropertySet to the ItemView of messages. I also include a user-defined function that parses out text from the email in a simple “Name: Value” format. Based on the values parsed out of the email I will call a python script that makes the proper API call. I’m also writing out each user to a csv just for records.
$psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties) $psPropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text; $s.LoadPropertiesForItems($msgs,$psPropertySet) function getEmailField($msg, $field){ $value = "" $pattern = "(?<=" + $field + ":s)[^s]+" return ([regex]::matches($msg, $pattern) | %{$_.value}) } foreach ($msg in $msgs.Items) { If ($msg.Subject -eq "User Profile Sync" -and $msg.Body.Text -match "is created"){ # double-check the subject (left over), check message body for text indicating new user $body = $msg.Body.Text $name = (getEmailField -msg $body -field "FirstName") + " " + (getEmailField -msg $body -field "LastName" ) $email = getEmailField -msg $body -field "EMailID" ($name + "," + $email ) >> ".newusers.csv"
#call ptyhon script, wait for it to complete $adduser = Start-Process 'python' -ArgumentList "adduser.py $email ""$name"" $email" -wait -passthru do {start-sleep -Milliseconds 500} until ($adduser.HasExited) # move message to previously located destination folder, then mark as read. $msg.Move($completedfolder.Id) $msg.IsRead = $true write-host "Added $email to system, archived email" } }
I can now schedule this locally, or set it up on a windows machine somewhere as long as I package it with our Python utility, and the Exchange Web Services dll.
Hi Garrett, I know this post is a bit old, but I am trying to find out how to search mailboxes that are in a list and I can put that list in a csv or text file. I only want to find out if an email was read and search by subject and sender, or by sender or subject, depending on the situation. Can you help me at all with how to search using a list and then I think I can figure out how to get the status.
You rocked my powershell, man!!
Perfectly written. Gave me the head start I needed to get my script up and running without having to re-invent the wheel.
When you change a message property, like “$msg.IsRead = $true”, you have to Update also:
$msg.Update([Microsoft.Exchange.WebServices.Data.ConflictResolutionMode]::AutoResolve);