Powershell mail every user from SQL query-CodePudding

Unfortunately, I could not find my answer from all the examples I came across.

I have a SQL query that contains the following values First name Suffix Last Name Email address

Now I want to send a mail per record and in the mail mention the name.

Below is the code that doesn't do the loop right now and puts the name in the mail.

<# Variabelen #>
$PlaceDate = "Amsterdam, "   (Get-Date -f dd-MM-yyyy)
$MailSubject = "My subject"
$sqlinstance = "MSSQLSRV1"


<# SQL data #>
$query = "SELECT [Firstname]
      ,[Suffix]
      ,[Lastname]
      ,[Emailaddress]
      ,[DateVisit]
  FROM [DBname].[dbo].[tbl_EventVisitors]
  where CONVERT(DATE, [DateVisit]) = CAST( GETDATE()-1 AS Date )"
$results = Invoke-Sqlcmd -Query $query -ServerInstance $sqlinstance

<# Create mail with SQL fields #>
$CoryReportHtml = ConvertTo-Html -PreContent @"
<body>
<br />
$PlaceDate<br /><br />
Dear $Firstname $Suffix $Lastname,<br />
MyMessage
</body>
"@ | Out-String


<# Send the mail #>
$mailParams = @{
    SmtpServer = 'localhost'
    to         = $Emailaddress
    from       = "[email protected]"
    Subject    = $MailSubject
    Body       = $CoryReportHtml
    BodyAsHtml = $true
}

Send-MailMessage @mailParams

CodePudding user response：

$results = Invoke-Sqlcmd -Query $query -ServerInstance $sqlinstance
foreach ($row in $results) {
    $body_value = $null
    $body_value = "Dear $($row.Firstname) $($row.$Lastname)"
    $body_value  = "my message"
    $Emailaddress = $row.Name
    $MailSubject = "Subject here"
    $mailParams = @{
        SmtpServer = 'localhost'
        to         = $Emailaddress
        from       = "[email protected]"
        Subject    = $MailSubject
        Body       = $body_valu
        BodyAsHtml = $true
    }

    Send-MailMessage @mailParams


    Write-Host "email sent to $row.name"
}