Powershell script for reading Excel Table and send e-mails

Question

Hello community,

Basically I would like to have a powershell code that reads a Table inside an Excel WorkBook and send e-mails to some recipients based on a specific column.

That table contains some user's information and the e-mail must be sent to the manager's address so he can evaluate their employees access permissions.

Source table:

My pain is to read the table and send only one e-mail to each manager.

Examples:

E-mail to the first manager:

E-Mail to the second manager:

E-mail to the third manager:

Any help would be much appreciated.

Answer 1

To read the Excel spreadsheet, use the ImportExcel PowerShell module. Installing it should be as easy as Install-Module ImportExcel.

Here's an example of grouping the users by their managers and sending just on mail to each manager:

Import-Excel C:\junk\group.xlsx |				# this usage expects one worksheetm but you have multiple sheets, You just have to name the sheet.
    Group-Object -Property 'Manager E-mail' |
        ForEach-Object {
            $to = $_.Name       # manager email address
            $MyPeeps = @()
            $_.Group|
                ForEach-Object {
                    $MyPeeps += $_ | Select-Object Name,E-Mail,Info
            }
            $emailbody = "Below are the following blah blah blah`n`n"
            # format the users in $mypeeps in the way you want them to appear
            # you can use HTML if you like.

            #
            # add whatever other properties you need to the Send-MailMessage
            Send-MailMessage -To $to -Body $emailbody ....      # send the message
        }