Powershell mail every user from SQL query

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

​x
 
<# 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​

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"
}

 
$results = Invoke-Sqlcmd -Query $query -ServerInstance $sqlinstanceforeach ($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"}​

Advertisement

Answer