I get a lot of noise/spam emails at work that based on rules get sent into folders, this works great but its not keeping a nice trim mailbox, so i thought i would start deleting the emails in these folders if they were older than 30 days. Now the normal way you would do this is via Auto Archive settings on the folder. But at my work and im sure a lot of others. the Exchange team have locked down the AutoArchive settings so you can’t play with them. This lead me to look for an alternative and as in most situations PowerShell came to the rescue.

The below code will delete any emails in the folder “SPAM Mail” that is older than 30 days. Note you will need to check the path for the session folder is correct for you, in most examples I found online the session went straight to the inbox, for me i had to dig a little deeper.

# SQL connection
$conn = new-object "System.data.sqlclient.SQLconnection"
$con.ConnectionString =(“Data Source=localhost;Initial Catalog=EmailDW;Integrated Security=SSPI”)
$conn.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $conn
$sqlcmd.CommandTimeout = 600000

# Outlook Connection
$Outlook = New-Object -ComObject Outlook.Application
# Outlook folder to clean up and load into SQL
$EmailsInFolder = $Outlook.Session.Folders.Item(2).Folders.Item("Inbox").Folders.Item("SPAM Mail").Items

# Get todays date
$Today = get-date

foreach ($email in $EmailsInFolder)
    {
        
          IF ($now-$Email.SentOn -ge 30.) 
              {
                ## build results
                $sent = $email.SentOn
                $sub = $email.Subject
                $sender = $email.SenderName
                $to = $email.To
                $sen = $email.Sensitivity

                $query = "INSERT INTO dbo.EmailLog ( emailSentOn , emailSubject , emailSenderName , emailTo , emailSensitivity ) VALUES  ( '$sent', '$sub', '$sender', '$to' , '$sen') ”
                #write-host $query
                $sqlcmd.CommandText = $query
                $rowsAffected = $sqlcmd.ExecuteNonQuery()
                $email.Delete()

              }
        

    }
Advertisements