PowerShell and Exchange Web Services (EWS) script to parse bounced emails

In my previous post, I had shared how to run PowerShell scripts on a locally configured Outlook profile, with Windows 10 powershell.

In this post, we will be making use of EWS to connect to an exchange server, impersonate a system maibox, download the emails and parsing the email body to extract the bounced email recipient.

References:
https://goodworkaround.com/2015/01/29/powershell-and-ews-managed-api/
https://msdn.microsoft.com/en-us/library/office/jj900168(v=exchg.150).aspx
https://www.linkedin.com/pulse/how-use-date-range-search-query-ews-managed-api-using-sunil-chauhan

What you need:
Microsoft Exchange Web Services API (download from nuget)

If you do not know which version of Exchange server your outlook profile is hosted on, refer to this article:

https://support.office.com/en-us/article/Determine-the-version-of-Microsoft-Exchange-Server-my-account-connects-to-d427465a-ce3b-42bd-9d83-c7d893d5d334

The script will attempt to detect a pre-embedded header. I find that this is the easiest way of detection since I have control over the email sending mechanism as well.
Example: in my email sending script (vbs sample here), I embed a header field
With Flds
' Set custom header so that the email can be easily read from bounce back addresses
.Item("urn:schemas:mailheader:x-CustomHdr") = strRecipientEmail
.Update
End With

Full source code:

PowerShell script (client machine) to parse bounced emails from Outlook

PowerShell script tested on Windows 10, with Outlook 2016 installed.
This is only meant for machines with Outlook connected to at least one email.

The script will attempt to parse bounced emails based on certain text patterns and attempt to extract the recipient email from a custom header. Assuming the custom header is set during send time.
Emails are extracted and saved to a SQL server database by invoking a stored procedure.

 Clear-host  
 Add-Type -Assembly "Microsoft.Office.Interop.Outlook"  
 $Outlook = New-Object -ComObject Outlook.Application  
 $Namespace = $Outlook.GetNameSpace("MAPI")  
 $mb = $Namespace.Folders | ?{$_.name -match "testabc@test.com"}    
 $Inbox = $mb.Folders | ?{$_.Name -match "Inbox"}    
 

# Clear deleted items > 30 days  
 $cutOff = (get-date).AddDays(-30)  
 $DeletedItems = $mb.Folders | ? { $_.Name -match 'Deleted Items' };  
 Foreach($e in $DeletedItems.Items){  
   if ($item.SentOn -gt $cutOff) {  
     $e.delete()  
   }  
 }  
 

# A bug in Outlook 2013 and 2016 will not display bounced emails properly  
 $uEnc = New-Object System.Text.UnicodeEncoding  
 $enc = [system.Text.Encoding]::UTF8  
 $dataSource = "dbservername"  
 $database = "dbname"  
 $dbUser = "user"  
 $pwd = "pwd"  
 $connectionString = "Data Source=$dataSource; User Id=$dbUser; Password=$pwd; Initial Catalog=$database"  
 $connection = new-object system.data.SqlClient.SQLConnection($connectionString)  
 $connection.Open()  
 #---------------------------------------------------------------------------------  
 # Function to pass in SQL query and return results as a table  
 #---------------------------------------------------------------------------------  
 function Invoke-SQL {  
   param(  
     [string] $sqlCommand = $(throw "Please specify a query.")  
    )  
   $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)  
      if ($command.ExecuteNonQuery() -ne 1)  
      {  
           Write-Host "Failed to execute SQL: $sqlCommand";  
      }  
 }  
 function Update-InvalidEmail  
 {  
   param(  
     [string] $email,  
     [string] $subject  
   )  
   If ($email.length -gt 100)  
   {  
     $email = $email.Substring(0,100)  
   }   
   If ($subject.length -gt 500)  
   {  
     $subject = $subject.Substring(0,500)  
   }   
      $email = $email.Replace("'", "''")  
   $subject = $subject.Replace("'", "''")  
      $strSQL = "sp_UpdateInvalidEmails '$email', '$subject'"  
   $dt = Invoke-SQL -sqlCommand $strSQL -returnResults $false  
   $dt  
 }  
 #checks newest messages  
 $Inbox.items | select -first 100 | foreach {  
   $subject = $_.Subject  
   $body = $enc.GetString($uEnc.GetBytes($_.Body))  
   if ($body -like "*Delivery has failed to these recipients*") {  
     write-host "failed delivery detected" -ForegroundColor Cyan  
     # look for lines like this: x-CustomHdr: email@test.com  
     # x-CustomHdr is a custom header 
     $regex = '\sx-CustomHdr:\s*([\w_.+-]+@[\w-]+\.[\w-.]+)\s'  
     $found = $body -match $regex  
     if ($found) {  
       $emailAddr = $matches[1]  
       write-host $emailAddr  
       Update-InvalidEmail -email $emailAddr -subject $subject  
     }  
   }  
   $_.Delete()  
 }  
 $connection.Close()  
 $connection.Dispose()  

SQL - Remove duplicate rows

Neat query to remove duplicate rows (MSSQL)

Assuming I have a table with auto-increment id

DELETE FROM MyTable
WHERE id NOT IN (SELECT MIN(id) FROM MyTable GROUP BY UniqueCol1, UniqueCol2);

PowerShell SharePoint to cancel workflows

$ver = $host | select version
if ($ver.Version.Major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"}
Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$TargetWeb = "http://sharepoint/"
$TargetLibrary = "Library"
$Filename = 133 # the item ID
 
$MyWeb = Get-SPWeb $TargetWeb
$list = $MyWeb.Lists[$TargetLibrary]
$item = $list.GetItemById($Filename)

foreach ($wf in $item.Workflows) {
#Cancel Workflows      
[Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wf);    
}

WRITE-HOST "Discarded workflows"

SharePoint and PowerShell: Updating an individual user profile properties

When a user cannot be found from SharePoint central administration by going to Service Applications - User Profile Service - Manage User Profiles, it is likely that this user had some synchronization issues that is preventing detection from the UI.

In my situation the user was originally sync'd from Active Directory, but we had some OU changes and broke the hierarchy.

It's PowerShell to the rescue!

This example will retrieve a user profile from the web application, and updates the preferred name and user manager.

#Set up default variables

$mySiteUrl = "http://mysites"

$adAccount = "DOMAIN\username"

$upAttribute = "PreferredName"

$upAttributeValue = "My New Name"

#Get site objects and connect to User Profile Manager service

$site = Get-SPSite $mySiteUrl

$context = Get-SPServiceContext $site

$profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

#Check to see if user profile exists

if ($profileManager.UserExists($adAccount))

{

    #Get user profile and change the value

    $up = $profileManager.GetUserProfile($adAccount)

    $up["PreferredName"]

    $up["Manager"]

    

    #$up["PreferredName"].Value = $upAttributeValue

    #$up.Commit()

    $up["Manager"].Value = "DOMAIN\manager"

    $up.Commit()

}

else

{

    write-host "Profile for user"$adAccount "cannot be found"

}

#Dispose of site object

$site.Dispose()