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()
