Sending email from powershell based on values of an excel spreadsheet.

Question

Hi,

I was wondering if someone could help me with what I'm looking to do. It is quite complicating and I hope it is possible to do so.

So, I would like an email to be sent to recipients that are named within an excel spreadsheet. Now, every name in there has values on the same row that need to be copied in the body of the email. The values that need to be in the body should only be the values that are assigned to the user and not other users. I have the following so far.

$email = "test@tiedtlaw email .com"
$smtpServer = "SMTPServer"
$ForEmail = @()

Create and get my Excel Obj

$x1 = New-Object -ComObject Excel.Application
$UserWorkBook = $x1.Workbooks.Open("C:\temp\Test.csv")

Select first Sheet

$UserWorksheet = $UserWorkBook.Worksheets.Item(1)
$UserWorksheet.activate()

Copy the part of the sheet I want in the Email

$rgeSource=$UserWorksheet.range("A1","C8")
$rgeSource.Copy() | out-null

$Results = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String
foreach ($Result in $Results)
{
$ForEmail += "$Result<br>"
}

$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

$msg.From = "Test@test .com"
$msg.To.Add($email)
$msg.Subject = "Testing"
$msg.IsBodyHtml = $True

$msg.Body = "This is a test<br>
<br>
$ForEmail
<br>
"
$smtp.Send($msg)

Obviously the above selects a certain range, but I'd like certain rows based on the name of the recipient always.
Example of the spreadsheet:

ManagedBy GroupName Alias Description Members LastChat LastConversation WhenCreated
User1 Group1 Group1 Test1 2 02/03/2021 14:18 20/01/2021 14:45 20/01/2021 14:45
User2 Group2 Group2 Test2 4 02/03/2021 14:18 20/01/2021 14:45 20/01/2021 14:45
User3 Group3 Group3 Test3 6 02/03/2021 14:18 20/01/2021 14:45 20/01/2021 14:45

I always want the first row to be displayed in the email body, along with the values for each individual user. So for example, for each user in "ManagedBy" column, top row should always be visible in the body along with the values following that user and an email to be sent to that user.

So it should function something like this.
For each user in ManagedBy column, send email to the user named in the ManagedBy column, with body of row1 + row of the named user.
Example: User3
$email = User3
If user is user3, add row 1 + row 4 on email body"

This might a bit complicating to do, but any help would be greatly appreciated.

Answer 1

Hi,

There's no need for Excel. As it's a CSV file it can be imported to PowerShell using Import-Csv.

$file = "C:\temp\Test.csv"  
$smtpServer = "SMTPServer"  
$msg = New-Object Net.Mail.MailMessage  
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)  
$msg.From = "******@test.com"  
$msg.Subject = "Testing"  
$msg.IsBodyHtml = $True  
Import-Csv -Path $file | ForEach-Object{  
    $headers = ($_.psobject.Members | Where-Object MemberType -EQ NoteProperty).name  
    $values =  ($_.psobject.Members | Where-Object MemberType -EQ NoteProperty).value  
    $msg.Body = "This is a test<br>  
    $headers<br>  
    $values<br>"        
    $email = "$($_.Managedby)@email.com"      
    $msg.To.Clear()  
    $msg.To.Add($email)  
    $smtp.Send($msg)  
}  

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Johan,

instead of Mail Object you can use or try Powershell Mail CMDLet

Send-MailMessage -encoding "UTF8" -To $Recipient -From xyz@mathieu.company .com -Subject "ABC" -Body $body -SmtpServer 10.10.10.2

Answer 3

Hi,

I'm not sure what you expect the email to be like. As IsBodyHtml is set to true, you could just edit the html code. Maybe something like this

$file = "C:\temp\Test.csv"  
$smtpServer = "SMTPServer"  
$msg = New-Object Net.Mail.MailMessage  
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)  
$msg.From = "******@test.com"  
$msg.Subject = "Testing"  
$msg.IsBodyHtml = $True  
$mails = @()  
Import-Csv -Path $file | ForEach-Object{  
    $to = $_.ManagedBy  
    $row = @"  

"@  
    if($_.ManagedBy -notin $mails.to){  
        $mails +=[pscustomobject]@{  
            "To" = $to  
            "row" = $row  
        }  
    }  
    else{  
        $mails | Where-Object {$_.to -eq $to} | ForEach-Object {  
            $_.row +=$row  
        }   
    }     
}  
$mails | ForEach-Object{  
    $msg.Body = @"  

"@  
    $msg.To.Clear()  
    $msg.To.Add("$($_.to)@gmail.com")  
    $smtp.Send($msg)  
}  

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.