Powershell Send-MailMessage foreach different values in csv

Question

To all,

I have a csv where every row has a contractors samaccount, its account expiration date, the contractor's supervisor name and respective email address.

I have the following data in my csv:

Name,AccountExpirationDate,Submitter,SubmitterEmail
Username1,9/21/2019,Submitter1,[email protected]
Username2,9/22/2019,Submitter2,[email protected]
Username3,9/23/2019,Submitter3,[email protected]

When I execute the code below, it does send an email for each row but the message only states username3 data in each email. How can I correct this so that [email protected] receives username1 data, likewise for submitter2 receiving username2 data, etc?

$from    =  "Your Friends in IT <[email protected]>"
$subject =  "Your contractors's login account will expire soon!"
$body    =  "Hello <br>"
$body    +=  "Your contractor's login account for <b>$Name</b></font> is set to expire on<b> $AccountExpirationDate</b>.<br>"
$body    +=  "Kind Regards,<br><br>"
$body    +=  "Your friends in IT"       
$csv = Import-Csv -Path "C:\ps\SAC-Accounts-ExpiringSoon.csv"
$csv | foreach {
  $Name = $_.name
  $to = $_.SACSubmitterEmail
  $_.AccountExpirationDate = $_.AccountExpirationDate -as [datetime]
  $_
      }
ForEach ($user in $csv)
{
$mail = New-Object System.Net.Mail.Mailmessage $from, $to, $subject, $body
$mail.IsBodyHTML=$true
$server = "mail.b.com"
# $port   = 25
$Smtp = New-Object System.Net.Mail.SMTPClient $server,$port
$Smtp.Credentials = [system.Net.CredentialCache]::DefaultNetworkCredentials
$smtp.send($mail)
}

$csv | foreach {} is not needed if you already have a foreach loop after it that iterates over the same data. Combining your efforts into one loop will probably fix most of the problems. Since you aren't doing anything with the foreach-object except setting variables, when you reach the next loop you only have the last values set by the previous foreach-object. Your intention appears to be to loop twice but simultaneously, but it doesn't have that effect. — AdminOfThings
– AdminOfThings, Commented Sep 30, 2019 at 18:03
Also, you have a few issues with variable names: $_.SACSubmitterEmail is not mentioned in the script or CSV columns. You might fix the stated problem by changing this line: $mail = New-Object System.Net.Mail.Mailmessage $from, $to, $subject, $body to $mail = New-Object System.Net.Mail.Mailmessage $from, $user.SubmitterEmail, $subject, $body — Rich Moss
– Rich Moss, Commented Sep 30, 2019 at 18:23

AdminOfThings · Accepted Answer · 2019-09-30 18:38:48Z

The root cause of the issue is having one loop that sets variables required by the next loop on every iteration of the first loop, but the next loop never gets them until the first loop completes. In this example, it is best to just combine both loops' efforts into a single loop. You can use Foreach-Object or foreach.

$from    =  "Your Friends in IT <[email protected]>"
$subject =  "Your contractors's login account will expire soon!"      
$csv = Import-Csv -Path "C:\ps\SAC-Accounts-ExpiringSoon.csv"

ForEach ($user in $csv)
{
    $Name = $user.Name
    $to = $user.SubmitterEmail
    $AccountExpirationDate = $user.AccountExpirationDate -as [datetime]
    $body =  "Hello <br>"
    $body +=  "Your contractor's login account for <b>$Name</b></font> is set to expire on<b> $AccountExpirationDate</b>.<br>"
    $body +=  "Kind Regards,<br><br>"
    $body +=  "Your friends in IT"

    $mail = New-Object System.Net.Mail.Mailmessage $from, $to, $subject, $body
    $mail.IsBodyHTML=$true
    $server = "mail.b.com"
    # $port   = 25
    $Smtp = New-Object System.Net.Mail.SMTPClient $server,$port
    $Smtp.Credentials = [system.Net.CredentialCache]::DefaultNetworkCredentials
    $smtp.send($mail)
}

PowerShell will not read ahead in your code and determine what a variable value is. It must be assigned and defined before PowerShell needs to retrieve the value. Based on how your code was constructed, $body was expecting $name and $AccountExpirationDate to be already be assigned.

Collectives™ on Stack Overflow

Powershell Send-MailMessage foreach different values in csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related