Post
Ask Question

Send Consolidated List Item To The Recipient Using PnP PowerShell

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  
  2.   
  3. foreach($name in $Manager)  
  4. {  
  5.    $managername = $name["MName"]  
  6.  
  7. # remaining code will be added  
  8.   
  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  
  3.  
  4.  
  5.                #Create DataTable Column Header  
  6.                $database = New-Object System.Data.DataTable("listitems")  
  7.                $Column = @("Employee ID","Employee Name","Department","Manager Name")  
  8.  
  9.                        #Adding column  
  10.                        foreach($header in $Column)  
  11.                        {  
  12.                           $database.columns.Add($header) | Out-Null  
  13.                        }  
  14.  
  15.                                    #Adding Row  
  16.                                   foreach($item in $data)  
  17.   
  18.                                    {  
  19.    
  20.                                       $value = $database.NewRow()  
  21.  
  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.                                      # }  
  29.   
  30.                                       $database.Rows.Add($value) | Out-Null  
  31.   
  32.                                       $listid = @($item.ID)  
  33.                                    }  
  34.   
  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>"  
  6.   
  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>"  
  10.                   
  11.         }  
  12.   
  13.         $body += "</table><br>"  
  14.   
  15.         $emailto = $requestor  
  16.         $emailsubject ="Your Employee List"  
  17.         $emailbody  = $body  
  18.         $emailsign = " Thanks <br> Thivagar Segar"  
  19.         $emaicontent = $emailbody+$emailsign  
  20.   
  21.   
  22.         $emailusername ="sender emailID"  
  23.         $emailpassword ="Sender Password"  
  24.   
  25.         Send-PnPMail -to $requestor -From $emailusername -Subject $emailsubject -Body $emaicontent -Password $emailpassword  
  26.         Write-Host "Email sent to $requestor" -ForegroundColor Green   
  27.         
  28.     }  
  29.     catch  
  30.     {  
  31.         Write-Host "Email Failed due to:"$_.Exception.Message.ToString()  
  32.   
  33.     }  
  34.       
  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  
  2.   
  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. }  
  13.  
  14.  
  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>"  
  21.   
  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>"  
  25.                   
  26.         }  
  27.   
  28.         $body += "</table><br>"  
  29.   
  30.         $emailto = $requestor  
  31.         $emailsubject ="Your Employee List"  
  32.         $emailbody  = $body  
  33.         $emailsign = " Thanks <br> Thivagar Segar"  
  34.         $emaicontent = $emailbody+$emailsign  
  35.   
  36.   
  37.         $emailusername ="sender emailID"  
  38.         $emailpassword ="Sender Password"  
  39.   
  40.         Send-PnPMail -to $requestor -From $emailusername -Subject $emailsubject -Body $emaicontent -Password $emailpassword  
  41.         Write-Host "Email sent to $requestor" -ForegroundColor Green   
  42.          
  43.     }  
  44.     catch  
  45.     {  
  46.         Write-Host "Email Failed due to:"$_.Exception.Message.ToString()  
  47.   
  48.     }  
  49.       
  50.  }   
  51.  
  52.  
  53. #Get Managers name  
  54. $Manager = (Get-PnPListItem -List "Employee" -Fields "MName").FieldValues  
  55.   
  56. foreach($name in $Manager)  
  57. {  
  58.    $managername = $name["MName"]    
  59.  
  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  
  63.   
  64.   
  65.                  #Create DataTable Column Header  
  66.                  $database = New-Object System.Data.DataTable("listitems")  
  67.                  $Column = @("Employee ID","Employee Name","Department","Manager Name")  
  68.  
  69.                          #Adding column  
  70.                          foreach($header in $Column)  
  71.                          {  
  72.                             $database.columns.Add($header) | Out-Null  
  73.                          }  
  74.  
  75.                                      #Adding Row  
  76.                                     foreach($item in $data)  
  77.   
  78.                                      {  
  79.      
  80.                                         $value = $database.NewRow()  
  81.                                         
  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  
  86.                                         
  87.   
  88.                                         $database.Rows.Add($value) | Out-Null  
  89.   
  90.                                         $listid = @($item.ID)  
  91.                                      }  
  92.   
  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()  
  99.      
  100.                                      }  
  101.                                      else  
  102.                                      {  
  103.                                         Write-Host "No Data Found" -ForegroundColor Red  
  104.                                          Write-Host "Exit" -ForegroundColor Yellow  
  105.       
  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


Similar Articles