Query SQL Database and send email for each record - PowerShell

Question

I am trying to query a SQL database and then loop through each of the data sets to send an email with data.

Below is a sample data set - so for record 1, my goal is to send an email to [email protected] with the policy information for that specific record and so on for the remaining records.

PolicyNumber            PSN       TransactionPremium    EmailAddress              Record
ABC DE3 0000012183 00   8636692   14109                 [email protected]     1
FGH JI3 0000012183 00   8636693  -14199                 [email protected]     2
KLM NO3 0000000774 03   8556541   -1664                 [email protected]     3
PRS TU3 0000000943 03   8579971   0                     [email protected]     4
HCA HO3 0000000969 03   8603944   -1425                 [email protected]     5

But after returning the data from the query, I cannot figure out how to loop through each record and send the email.

$Instance = "sqlinstancename"
$Database = "databasename"
$SQL = @'
SELECT TOP (5) PolicyNumber, PSN, CAST(TransactionPremium AS DECIMAL(19,4)) AS TransactionPremium, EmailAddress
FROM dbo.TableName ORDER BY BatchDate DESC
'@

Invoke-DbaQuery -SqlInstance $Instance -Database $Database -Query $SQL

I assume it will be something along the lines of:

ForEach ($i in i)
{
Send-MailMessage -From "[email protected]" -To $EmailAddress -Subject "Test" -BodyAsHTML $PolicyNumber"
}

Could I get some assistance on how to accomplish this task?

Steven · Accepted Answer · 2021-05-03 21:47:11Z

1

Assuming all is well with the query, a loop to send the emails may look something like this:

$Rows = Query-DataBase Query <Query> -Instance <Server\Instance> -Database <DBName>

ForEach( $Row in $Rows)
{
    $EmailParams = @{
        $From       = '[email protected]'
        $To         = $Row.EmailAddress
        $Subject    = 'test'
        $BodyAsHtml = $true
        $Body       = $Row.PolicyNumber
        $SMTPServer = 'YourSMTPServerFQDN'
    }
    Send-EmailMessage @EmailParams
}

Note: I'm using splatting which you can read about in about_Splatting. Splatting helps a lot with formatting and readability. That said, this is really just referencing the desired properties in the loop and therefore per each item then sending the email. As such it can easily be expanded so you can get the body, subject, or whatnot correct.

edited May 3, 2021 at 21:47

answered May 3, 2021 at 20:55

Steven

7,1271 gold badge17 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MISNole Over a year ago

Hey - I appreciate the response but I'm not sure that answers my question. I don't have an issue connecting to the SQL database - my issue lies in taking the data set that is returned and looping through each email address to send out an email to the recipient.

Steven Over a year ago

I'm sorry I must have had a brain fart. That's actually quite easy. If you give me a minute I can cook up an example.

Collectives™ on Stack Overflow

Query SQL Database and send email for each record - PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related