Use PowerShell to send mail via Outlook along with SQL result csv as attachment

Introduction

In this post we will create a simple utility to perform these tasks: 

1. Execute a SQL script stored as .sql file in a local directory.
2. Save the result set as .csv.
3. Use outlook application configured on the local machine to send the .csv as an attachment to email recipients. 

We will be using PowerShell script.

Lets prepare the .sql script. For this utility purpose we will make use of a very simple query.


Below is the code for the .ps1 which will perform the tasks we mentioned above.

$global:toMailList       = "toEmail@domain.com";

$global:ccMailList        = "ccEmail1@domain.com;ccEmail2@domain.com";

$global:MailSubject     = "PS outlook Test Email";

$global:MailBody         = "Sent using Powershell via Outlook Client";

$global:SQLScriptPath = "C:\...\SQLInputFile.sql";

$global:Attachment     = "C:\...\TestOutput.csv";

$global:server               = "localhost"

# Below function with execute the script at the location pointed by variable $global:SQLScriptPath. Additionally, it will export the result as .csv at location pointed by $global:Attachment 

# Make note of the server details. We can modify it as per our needs and pass appropriate credentials to execute the required script on the given database of the server.

function runsql(){

import-module "sqlps" -DisableNameChecking

invoke-sqlcmd -ServerInstance $global:server -inputFile $global:SQLScriptPath |export-csv $global:Attachment -notypeinformation

}

# Below script will open an instance of outlook and send the mail to the specified recipients along with the attachment.

function sendMail(){

$OL = New-Object -ComObject outlook.application

$mItem = $OL.CreateItem("olMailItem")

$mItem.To = $global:toMailList;

$mItem.CC = $global:ccMailList;

$mItem.Subject =$global:MailSubject 

$mItem.Body = $global:MailBody

$mItem.Attachments.Add($global:Attachment)

$mItem.Send();

Start-Sleep 5

}

# try block

# we will find in case an instance of outlook is already open on the machine where .ps1 is being executed and if yes, close it and subsequently execute function "runsql" and then "sendmail". Reason being, we cannot run two instances of outlook simultaneously.

# if outlook is not open then we will directly execute function "runsql" and then "sendmail"

# once mail is sent we will close the instance opened by "sendMail" function and then start the process outlook.

# also, note the sleep of 5 seconds provided to allow app to be opened and closed.

try {

if( Get-Process outlook -ErrorAction SilentlyContinue ) {

 Write-output "outlook is open . we will close the instance and re-open once the powershell has sent the email"

 Get-Process outlook | Foreach-Object { $_.CloseMainWindow() | Out-Null } | stop-process –force

 Start-Sleep 5

 runsql;

 Start-Sleep 5

 sendMail;

 Get-Process outlook | Foreach-Object { $_.CloseMainWindow() | Out-Null } | stop-process –force

 Start-Sleep 5

}

else

 {

  Write-output "outlook is closed. We will send the mail and open the app. "

  runsql;

  Start-Sleep 5

  sendMail;

 

  Get-Process outlook | Foreach-Object { $_.CloseMainWindow() | Out-Null } | stop-process –force

 

 }

Start-Process outlook;

 }

 #catch block

catch

{

write-output $Error[0]

}

Save the above code as a powershell script. We can automate the process by probably scheduling the execution the using a windows task scheduler. 

P.S. Please note that before executing the script you should be aware that outlook is closed in case it's open.


Thank You !