Send-MailMessage based on Conditions

Question

I am trying to sending email to submittedby users based on below conditions :

1. UserCountry = "Yes" and UserTeamInTheList = "No" → send MailMessage1
2. UserCountry = "No" and UserTeamInTheList = "No" → send MailMessage2

NOTE : Also my body of email is in Chinese that doesn't reflect in email. Please help. I get only ?????? in email instead of original message.

Below is the output of the query I am using:

$Q  = "Select * from Query"
$cr = Invoke-Sqlcmd -ServerInstance Server1 -Database DB1 -Query $Q |
      select FileId,SubmittedBy,UserCountry,UserTeamIsInTheList |
      ft -AutoSize
$cr

Now based on the above output and above conditions, I am using below code (I am trying it and it is wrong code of course):

$Q  = "Select * from Query"
$cr = Invoke-Sqlcmd -ServerInstance Server1 -Database DB1 -Query $Q |
      select FileId,SubmittedBy,UserCountry,UserTeamIsInTheList |
      ft -AutoSize
$cr

foreach ($i in $cr) {
    if (UserCountry -eq "Yes" -and UserTeamIsInTheList -eq "No") {
        Send-MailMessage -To ("SubmittedBy" (*User In the Output above who submitted this file*) -CC “[email protected]” -From “[email protected]" -SMTPServer acb.newbie.co  -Subject “Testing” -Body “MailMessage1”
    } elseif {
        if (UserCountry -eq "No" -and UserTeamIsInTheList -eq "No") {
            Send-MailMessage -To ("SubmittedBy" (*User In the Output above who submitted this file*) -CC “[email protected]” -From “[email protected]" -SMTPServer acb.newbie.co  -Subject “Testing” -Body “MailMessage2”
        } else {
            'Exit'
        }
    }

Answer 1

There's a few issues with your code, but most significantly is where you populate the $CR variable at the top. You've put | FT on the end of this which changes the object type to something that then can't be reused further down in the script. You can still output this variable with Format-Table you just need to do so separately.

Overall, I suggest you do something like this:

$Q = "Select * from Query"
$CR = Invoke-Sqlcmd -ServerInstance Server1 -Database DB1 -Query $Q | Select FileId,SubmittedBy,UserCountry,UserTeamIsInTheList
$CR | FT -AutoSize

$MailSettings = @{
    CC = '[email protected]'
    From = '[email protected]'
    SMTPServer = 'acb.newbie.co'
    Subject = 'Testing'
}

$Group1 = $CR | Where ($_.UserCountry -eq 'Yes' -and $_.UserTeamIsInTheList -eq 'No') | Select -ExpandProperty SubmittedBy
Send-MailMessage @MailSettings -To $Group1 -Body 'MailMessage1'

$Group2 = $CR | Where ($_.UserCountry -eq 'No' -and $_.UserTeamIsInTheList -eq 'No') | Select -ExpandProperty SubmittedBy
Send-MailMessage @MailSettings -To $Group2 -Body 'MailMessage2'

You shouldn't need the ForEach loop because instead you can use Where-Object cmdlet to filter the results based on your criteria and then return just the SubmittedBy property. Then because Send-MailMessage accepts array input for the -To parameter you can just feed this list in to that property. If you don't want each recipient to see the others, you could use -BCC instead, or do a ForEach to do them individually (but beware you can hit limits when trying to send large numbers of individual emails).

Further I've used Splatting and a hashtable of the settings that are the same for both Send-MailMessage commands so we can reuse them without having to duplicate.