PowerShell: Send mails with attachment via Outlook

Let’s imagine that you should send work report every single workday like I did. 🙂 Your report is Excel file and you store it in a folder on your Desktop (or somewhere else). I automated this routine work with the 2 PowerShell scripts and executable bat file.

Script checks the folder for the last modified Excel file and send it to recipients.

settings.ps1

##########################################
# settings part
##########################################

#define the directory Excel reports (ex: Desktop\Reports)
$dir = $env:USERPROFILE + "\Desktop\Reports"

# Mails will be sent From your default account in Outlook
# If you want to change it, please delete # sign.
#$mailFrom = "sender_mail@example.com"

#define To, for multiple email, use semi-colon ; to separate
$mailTo = “receiver_mail@example.com“

#define Cc, for multiple email, use semi-colon ; to separate
#$mailCc = "receiver_mail_Cc@example.com"

#End

sendreports.ps1

#load settings
. .\settings.ps1

#Function for create and send mail
function sendReports
{

#Get the today’s date for mail
$a = Get-Date -format dd-MMM-yyyy

#Get the path to last modified file
$latest = Get-Childitem $dir -Recurse -Include “*.xlsx” | where-object {!($_.psiscontainer)} | Sort-Object LastAccessTime -Descending | Select-Object -First 1

# create instance of Oulook object
$o = New-Object -com Outlook.Application

$mail = $o.CreateItem(0)

#$mail.importance = 2
if ($mailFrom -ne $null) {
#set account from which mail should be send. See settings file
$mail.SendUsingAccount = $o.Session.Accounts | where {$_.SmtpAddress -eq $mailFrom}
}

#see settings file for $mailTo and $mailCc variables
$mail.To = $mailTo 
$mail.Cc = $mailCc

# Check if do you have a last created xlsx for today, if YES – send Report, else – send another message.
if ($latest.LastWriteTime.Date -eq $a) {

# mail Subject
$mail.subject = “Report for “ + $a

# mail body
$mail.HTMLbody = $mail.subject + ” in attachment ” + ”

Best Regards, ” + [Environment]::UserName + ”

This mail was sent automatically by Script”

# mail attachment
$mail.Attachments.Add($latest.Fullname)
} else {

# if there is no new Excel file created today
$mail.subject = “No Reports for today“

$mail.HTMLbody = “

No Reports for today” + ”

Best Regards, ” + [Environment]::UserName + ”

This mail was sent automatically by Script”
}

$mail.Send()
}

# Check Outlook process.
$ProcessActive = Get-Process outlook -ErrorAction SilentlyContinue

if($ProcessActive)
{
# send mail if Outlook is Running
sendReports 
}
else
{
# wait for 30 seconds before load Profile
Start-Process Outlook #start Outlook
Sleep 30
sendReports
}

#End

sendreport.bat

Powershell.exe -executionpolicy remotesigned -File sendReports.ps1

Additionally, you can add executable bat file to Windows Task Scheduler.

Leave a Reply