question

JohanWeber-6710 avatar image
0 Votes"
JohanWeber-6710 asked JohanWeber-6710 commented

Group values and send email based on excel data.

Hello,

I have the following Powershell script and I need a bit of help. The script takes the data from a .csv document and sends email to users that are the owners of each group.

 [powershell]
 # Function to create report email
 function SendNotification{
 $Msg = New-Object Net.Mail.MailMessage
 $Smtp = New-Object Net.Mail.SmtpClient($ExchangeServer)
 $Msg.From = $FromAddress
 $Msg.Bcc.Add($ToAddress)
 $Msg.Subject = "$GroupName"
 $Msg.Body = $EmailBody
 $Msg.IsBodyHTML = $true
 $Msg.Priority = [System.Net.Mail.MailPriority]::High
 $Smtp.Send($Msg)
 }
    
 # Define local Exchange server info for message relay. Ensure that any servers running this script have permission to relay.
 $ExchangeServer = "SMTPServer"
 $FromAddress = New-Object System.Net.Mail.MailAddress ("test@test.com", "IMPORTANT: Team Channel Review")
    
 # Import user list and information from .CSV file
 $Groups = Import-Csv 'C:\Temp\DummyData1.csv'
    
 # Send notification to each user in the list
 Foreach ($Group in $Groups) {
 $ToAddress = $Group.Email
 $FirstName = $Group.FirstName
 $Owner = $Group.Owner
 $ManagedBy = $Group.ManagedByDetails
 $GroupName = $Group.GroupName
 $Alias = $Group.Alias
 $Description = $Group.Description
 $Members = $Group.Members
 $LastChat = $Group.LastChat
 $LastConversation = $Group.LastConversation
 $WhenCreated = $Group.WhenCreated
    
 $EmailBody = @"
 <p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'>Hello $FirstName,</p>
 <p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'>&nbsp;</p>
 <p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'>Could you please review the below Teams Channel created by yourself and let me know if this channel is active or if it is no longer required?</p>
 <p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><br></p>
 <table style="border: none;border-collapse: collapse;width:1088pt;empty-cells: show;max-width:100%;font-variant-ligatures: normal;font-variant-caps: normal;orphans: 2;text-align:start;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness: initial;text-decoration-style: initial;text-decoration-color: initial;">
     <tbody>
         <tr>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;height:29.25pt;width:136pt;">Owner</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Group</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Alias</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Description</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">ManagedByDetails</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Creation Date</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Last Conversation</td>
             <td style="color:black;font-size:15px;font-weight:700;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">Members</td>
         </tr>
         <tr>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;height:29.25pt;border-top:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$Owner</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$GroupName</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$Alias</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$Description</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$ManagedBy</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$WhenCreated</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$LastConversation</td>
             <td style="color:black;font-size:15px;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri, sans-serif;text-align:center;vertical-align:middle;border:.5pt solid windowtext;border-top:none;border-left:none;width:136pt;min-width: 5px;border-image: initial;user-select: text;">$Members</td>
         </tr>
     </tbody>
 </table>
 <p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><span style="text-align: inherit;"><br>Please do not ignore this email. I&apos;ll be awaiting your reply.</span>&nbsp;</p>
 "@
 Write-Host "Sending notification to $ToAddress ($GroupName)" -ForegroundColor Yellow
 SendNotification
 }


The current output of the script is the following:
108580-test.png


My requirement is to group all the groups that User 1 (Adam) is responsible for and send only one email rather than sending one email per group he owns. So the final email output would be something like this:

108642-test2.png


The same needs to apply for all the users named in the spreadsheet.

I have attached a txt file containing dummy data that this script runs on. Cannot upload .csv or .xlsx unfortunately.
108536-dummydata.txt

Any help would be greatly appreciated.

Thanks in advance!


windows-server-powershelloffice-excel-itprooffice-itpro
test.png (14.7 KiB)
test2.png (26.1 KiB)
dummydata.txt (2.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MotoX80 avatar image
0 Votes"
MotoX80 answered JohanWeber-6710 commented

Here's one way.

I scaled back on the content, but you should be able to see what I did and fix it so that it looks like you want and contains all of the data.


  $Groups = Import-Csv 'C:\Temp\DummyData1.csv'
  $UniqueOwners = ($Groups | Select-Object -Property Owner -Unique).Owner
  "Here are the owners"
  $UniqueOwners
  foreach ($Owner in $UniqueOwners) {
     ""
     "Processing $Owner"
     $ThisOwnersGroups = $Groups | Where-Object -Property Owner -EQ $Owner            # get all of the groups that this guy owns.
        
     # Use the first entry in the $ThisOwnersGroups to reference common fields like firstname and email
     # build the first part of the email, include the table header for the group table  
     $EmailBody = @"
 <p>Hello $($ThisOwnersGroups[0].FirstName),</p>                
 <p>This email is being sent to $($ThisOwnersGroups[0].Email),</p>         
 <Table><tr><td>Group Name</td><td>Description</td></tr>
 "@
     foreach ($Group in $ThisOwnersGroups) {
         "   $($Group.GroupName)"
         $EmailBody += "`n  <tr><td>$($Group.GroupName)</td><td>$($Group.Description)</td></tr>"
     }
     $EmailBody += "`n</table>"
     ""
     # send this email 
     $EmailBody 
  }    
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TThank you very much!

By expanding the script to my needs I was able to get what I needed. Brilliant.

Thanks again!

0 Votes 0 ·