In my previous article, I explained how to iterate the list item and send a consolidated email to respective recipients using SharePoint designer workflow. Now, I am going to do the same process using PnP PowerShell module.

In this experiment, I am going to read the list of manager names from the Employee Database List and will send a consolidated employees information to the respective managers. This script may help you to automate any business process in your organization.
So, for test purposes, I have created one custom list called “Employee Database”. Then, I created some columns like Employee ID, Employee Name, Department, and finally, Manager Name. I have created all columns as single-line-of-text for demo purposes and added a few rows. So, while you are creating the list, you can use the respective data type like people picker for Employee name and Manager Name, etc.
Send consolidated list item to the recipient using PnP PowerShell
Let’s get started with the script. Here, I used the below structure to construct this script.
  • Connect SharePoint Online site collection using the PnP module.
  • Get the list of manager names and put in a foreach loop.
  • Then, get the list items using the CAML Query with the respective manager name and copy the information in datatable.
  • Finally, send an email to the respective managers with their reportee person's information

So as my first step, I connect SharePoint Online using PnP module. You can use multiple ways to connect the PnP Online. Here, I am getting the credentials using Get-Credential cmdlets.

  1. Connect-PnPOnline -Url https://tenant.sharepoint.com -Credentials(Get-Credential)

Then, collect the Manager Names using Get-PnPListitem. Here, one more thing I have noticed while doing the test that PnP module is not recognizing the space between column names. For example, if I put a column name as Manager Name, then it’s not giving the result whereas if I use ManagerName, then it's showing the result for me.

  1. $Manager = (Get-PnPListItem -List "Employee" -Fields "MName").FieldValues

Then, I put a foreach loop to repeat the action for all manager names. So, as the next step, I put Get-PnPListIem with CAML Query and I added the respective manager name in the CAML Query. Then only it will filter the list item based on the query.

  1. $Manager = (Get-PnPListItem -List "Employee" -Fields "MName").FieldValues
  3. foreach($name in $Manager)
  4. {
  5. $managername = $name["MName"]
  7. # remaining code will be added
  9. }

Now, I am iterating the list item using Get-PnPlistitem cmdlets with CAML Query and put CAML query like ManagerName column="ManagerName", then only it will filter the respective manager items.

  1. <view><Query><Where><Eq><FieldRef Name='MName' /><Value Type='Text'>managername</Value></Eq></Where></Query></view>
Now, let us copy all the list items in datatable called "listitems" and move that datatable to $database variable. Then, put the foreach loop to add the rows and fianlly, add that $database to dataview and copy to $report variable.
  1. $listitem = Get-PnPListItem -List "Employee" -Query "<view><Query><Where><Eq><FieldRef Name='MName' /><Value Type='Text'>$managername</Value></Eq></Where></Query></view>"
  2. $data = $listitem.FieldValues
  5. #Create DataTable Column Header
  6. $database = New-Object System.Data.DataTable("listitems")
  7. $Column = @("Employee ID","Employee Name","Department","Manager Name")
  9. #Adding column
  10. foreach($header in $Column)
  11. {
  12. $database.columns.Add($header) | Out-Null
  13. }
  15. #Adding Row
  16. foreach($item in $data)
  18. {
  20. $value = $database.NewRow()
  22. # foreach($header in $Column)
  23. # {
  24. $value["Employee ID"] = $item.Title
  25. $value["Employee Name"] =$item.Employee_x0020_Name
  26. $value["Department"] =$item.Department
  27. $value["Manager Name"] =$item.Manger_x0020_Name
  28. # }
  30. $database.Rows.Add($value) | Out-Null
  32. $listid = @($item.ID)
  33. }
  35. $report = New-Object System.Data.DataView($database)
And as the last step, we will be designing the email body. If you are using fully cloud-hosted Office 365, then there is no need to provide the sender email ID and password to authenticate through the Exchange Server. If you are using any hybrid infrastructure, then you need to provide a sender email ID with the password to authenticate with the Exchange Server.
  1. Function sendemail($requestor,$listid)
  2. {
  3. try
  4. {
  5. $body = "<Table border=1><tr><th>Employee ID</th><th>Employee Name</th><th>Department</th><th>Manager Name</th></tr>"
  7. foreach($content in $report)
  8. {
  9. $body += "<tr><td>" + $content[0] +"</td><td>" + $content[1] + "</td><td>" + $content[2] +"</td><td>" + $content[3] +"</td></tr>"
  11. }
  13. $body += "</table><br>"
  15. $emailto = $requestor
  16. $emailsubject ="Your Employee List"
  17. $emailbody = $body
  18. $emailsign = " Thanks <br> Thivagar Segar"
  19. $emaicontent = $emailbody+$emailsign
  22. $emailusername ="sender emailID"
  23. $emailpassword ="Sender Password"
  25. Send-PnPMail -to $requestor -From $emailusername -Subject $emailsubject -Body $emaicontent -Password $emailpassword
  26. Write-Host "Email sent to $requestor" -ForegroundColor Green
  28. }
  29. catch
  30. {
  31. Write-Host "Email Failed due to:"$_.Exception.Message.ToString()
  33. }
  35. }
Here is the full script which will get the list items based on the manager name and send an email to the respective reporting manager.
  1. Clear-Host
  3. try
  4. {
  5. #Connect SharePoint
  6. Connect-PnPOnline -Url https://tenant.sharepoint.com -Credentials(Get-Credential)
  7. Write-Host " **** SharePoint Connected ****" -ForegroundColor Green
  8. }
  9. catch
  10. {
  11. Write-Host "Failed to connect sharepoint" -ForegroundColor Red
  12. }
  15. #Send Email
  16. Function sendemail($requestor,$listid)
  17. {
  18. try
  19. {
  20. $body = "<Table border=1><tr><th>Employee ID</th><th>Employee Name</th><th>Department</th><th>Manager Name</th></tr>"
  22. foreach($content in $report)
  23. {
  24. $body += "<tr><td>" + $content[0] +"</td><td>" + $content[1] + "</td><td>" + $content[2] +"</td><td>" + $content[3] +"</td></tr>"
  26. }
  28. $body += "</table><br>"
  30. $emailto = $requestor
  31. $emailsubject ="Your Employee List"
  32. $emailbody = $body
  33. $emailsign = " Thanks <br> Thivagar Segar"
  34. $emaicontent = $emailbody+$emailsign
  37. $emailusername ="sender emailID"
  38. $emailpassword ="Sender Password"
  40. Send-PnPMail -to $requestor -From $emailusername -Subject $emailsubject -Body $emaicontent -Password $emailpassword
  41. Write-Host "Email sent to $requestor" -ForegroundColor Green
  43. }
  44. catch
  45. {
  46. Write-Host "Email Failed due to:"$_.Exception.Message.ToString()
  48. }
  50. }
  53. #Get Managers name
  54. $Manager = (Get-PnPListItem -List "Employee" -Fields "MName").FieldValues
  56. foreach($name in $Manager)
  57. {
  58. $managername = $name["MName"]
  60. #Iterate List item based on the Manager Namme
  61. $listitem = Get-PnPListItem -List "Employee" -Query "<view><Query><Where><Eq><FieldRef Name='MName' /><Value Type='Text'>$managername</Value></Eq></Where></Query></view>"
  62. $data = $listitem.FieldValues
  65. #Create DataTable Column Header
  66. $database = New-Object System.Data.DataTable("listitems")
  67. $Column = @("Employee ID","Employee Name","Department","Manager Name")
  69. #Adding column
  70. foreach($header in $Column)
  71. {
  72. $database.columns.Add($header) | Out-Null
  73. }
  75. #Adding Row
  76. foreach($item in $data)
  78. {
  80. $value = $database.NewRow()
  82. $value["Employee ID"] = $item.Title
  83. $value["Employee Name"] =$item.Employee_x0020_Name
  84. $value["Department"] =$item.Department
  85. $value["Manager Name"] =$item.Manger_x0020_Name
  88. $database.Rows.Add($value) | Out-Null
  90. $listid = @($item.ID)
  91. }
  93. $report = New-Object System.Data.DataView($database)
  94. if($report.Count -gt 0)
  95. {
  96. $report
  97. sendemail("Enter Manager email ID Here")
  98. $database.Clear()
  100. }
  101. else
  102. {
  103. Write-Host "No Data Found" -ForegroundColor Red
  104. Write-Host "Exit" -ForegroundColor Yellow
  106. }
  107. }
The final output would be like the below image.
Send consolidated list item to the recipient using PnP PowerShell
And, the email body will be like this.
Send consolidated list item to the recipient using PnP PowerShell