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 = "from@example.com"
Subject = $MailSubject
Body = $CoryReportHtml
BodyAsHtml = $true
}
Send-MailMessage @mailParams
Advertisement
Answer
$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 = "from@example.com"
Subject = $MailSubject
Body = $body_valu
BodyAsHtml = $true
}
Send-MailMessage @mailParams
Write-Host "email sent to $row.name"
}