powershell - sending bulk mail with HTML formatted

Question

I have a script that will send an email to an employee's manager if thier account is going to expire in the next 30 days. I am pulling each account one by one and I have been sending them an HTML formatted email with the employee's name and expiration date on behalf of Support to thier manager.

But I don't want to send notification mail one by one to thier manager. for example ,sometimes there may be 30 - 50 users of direct and indirect reports to a manager from Active Directory. So my script will send mail to manager for each expired user. They don't want this.

My question are : 1- How can I send notification mail both accounts is going to expire in the next 30 days and to their manager bulk instead of one by one ?

e.g (John, Michael, Andy, Aaron direct reports to [email protected] )

To: John, Michael, Andy, Aaron
Cc: [email protected]

2- I want to send HTML formatted mail instead of text message.

message :

"***The network account for $($name.name) will be expiring on $(($name.AccountExpirationDate).ToShortDateString()), as their manager, could you please confirm they will still be continuing with their role in order to extend their account.*** "

my desired output :

e.g HTML table formatted

name    SamAccountName  AccountExpirationDate   EmployeeID
John    PRD192888     1/31/2021 12:00:00 AM     102928
Michael PRD192876     2/14/2021 12:00:00 AM     102924
Andy    PRD192834     1/12/2021 12:00:00 AM     102934
Aaron   PRD192234     1/31/2021 12:00:00 AM     102679

Script :

$user = Search-ADAccount -AccountExpiring -TimeSpan 30.00:00:00 | Where-Object { ($_.Enabled -eq $true) -and  ($_.samAccountName -notlike "SCN*") -and ($_.samAccountName -notlike "CVB*") -and  ($_.samAccountName -notlike "PCN*") }

#pulling each account one by one and check to make sure they have a manager assigned. If not those names are piped into a file
foreach ($name in $user){
    If (( Get-ADUser -Identity $name.SamAccountName -Properties *).Manager -eq $null) {
        Write-Host "no manager listed for" $name.name ;
        Add-Content -Path \pathFolderNo_manager_listed.txt `
        -Value "$(($name.AccountExpirationDate).ToShortDateString())`t $($name.name)"
        }
#accounts with manager are taken one at a time by username to pull out their managers email address.
#this will also send them an email with the employee's name and expiration date on behalf of desktop Support to thier manager
    else { 
    $manager = ( Get-ADUser -Identity $name.SamAccountName -Properties * ).Manager
    $manager_email = (Get-ADUser -Identity $manager -Properties *).mail
    Write-Host "sending notification email to" $manager_email;
    Send-MailMessage -From [email protected] `
    -Subject "Team member account will expire soon" `
    -To $manager_email `
    -Body "***The network account for $($name.name) will be expiring on $(($name.AccountExpirationDate).ToShortDateString()), as their manager, could you please confirm they will still be continuing with their role in order to extend their account.  If they will need an extension, please reply to this email with your approval and we can extend their accounts another 90 days.  If the listed employee is to be transitioned over to a permanent FTE with urdomain, please forward your response to [email protected] in order to begin the process.  Thank you.***  " `
    -BodyAsHtml -Priority High -SmtpServer postmaster.urdomain.com
            }
}
#this section will email the file that was created from the employees with no manager and email it to desktop support
if (Test-Path \pathFolderNo_manager_listed.txt){
    Send-MailMessage -From [email protected] `
    -Subject "Expiring accounts with no manager listed report " `
    -To [email protected] `
    -Attachments \pathFolderNo_manager_listed.txt `
    -Body "****This an automated message. Please review the attachment.***" `
    -BodyAsHtml -Priority High -SmtpServer postmaster.urdomain.com ;
    Start-Sleep -Seconds "10"
    Remove-Item -Path \pathFolder\No_manager_listed.txt 
}

question from:https://stackoverflow.com/questions/65950659/sending-bulk-mail-with-html-formatted

Answer 1

First of all I think users should not know about other users, only managers should see them all.
Because apparently it is important to use the same style (font and font color) with every email you send, I would change your code to something like this:

Step 1:

Define a style for the HTML emails so they will all have the same font and colors

$style = @"
<style>
    body, table {font-family: sans-serif; font-size: 11pt; color: #1F497D;}
    table {border: 1px solid black; border-collapse: collapse; color: #000000;}
    th {border: 1px solid black; background: #dddddd; padding: 3px;}
    td {border: 1px solid black; padding: 3px;}
</style>
"@

Step 2:

Find users with an account expiration date within the next 30 days

# find expiring users and store the AD properties in variable $users
$users = Search-ADAccount -AccountExpiring -TimeSpan 30.00:00:00 -UsersOnly | 
         Where-Object { ($_.Enabled -eq $true) -and  ($_.SamAccountName -notmatch '^(SCN|CVB|PCN)') } |
         ForEach-Object {
            Get-ADUser -Identity $_.SamAccountName -Properties EmailAddress, AccountExpirationDate, Manager
         }

A probably faster (depends on the number of users) alternative to the above is to skip the Search-ADAccount cmdlet and do:

$refDate = (Get-Date).AddDays(30).Date
$users = Get-ADUser -Filter "Enabled -eq 'True'" -Properties EmailAddress, AccountExpirationDate, Manager | 
         Where-Object { ($_.AccountExpirationDate -le $refDate) -and  ($_.SamAccountName -notmatch '^(SCN|CVB|PCN)') }

Step 3:

Now that we have the users whose accounts will expire, we can split these up into users without and users with the Manager attribute filled in

# create a template to use for mailing the desk support people
# this uses only 1 placeholder to fill in (style)
$mailTemplate = @"
<html><head>{0}</head><body>
This an automated message.<br />Please review the attachment.<br /><br />Thank you.
</body></html>
"@

$noManagerFile = '\pathFolderNo_manager_listed.csv'
# filter the users that have no manager listed
$unmanagedUsers = @($users | Where-Object { [string]::IsNullOrEmpty($_.Manager) })
if ($unmanagedUsers.Count) {
    Write-Host "There are $($unmanagedUsers.Count) users without manager."
    # output a summary of these users as CSV file to send as attachment to desktop support
    $unmanagedUsers | Select-Object @{Name = 'AccountExpirationDate'; Expression = {$_.AccountExpirationDate.ToShortDateString()}}, Name | 
                      Export-Csv -Path $noManagerFile -NoTypeInformation

    # send an email with attachment for unmanaged users
    $mailParams = @{
        To         = '[email protected]'
        From       = '[email protected]'
        Subject    = 'Expiring accounts with no manager listed report'
        Body       = $mailTemplate -f $style
        BodyAsHtml = $true
        Priority   = 'High'
        SmtpServer = 'postmaster.urdomain.com'
        Attachments = $noManagerFile
        # more parameters go here
    }
    Send-MailMessage @mailParams
}

Step 4:

Next, send an email to every manager with all expiring accounts listed

# create a template to use for mailing the managers
# this uses 3 placeholders to fill in (style, manager name, and the table of expiring user accounts)
$mailTemplate = @"
<html><head>{0}</head><body>
Dear {1},<br /><br />

The network accounts for the users below will be expiring within the next 30 days.<br />
{2}
<br />
As their manager, could you please confirm they will still be continuing with their role in order to extend their account.<br />
If they will need an extension, please reply to this email with your approval and we can extend their accounts another 90 days.<br /><br />
If the listed employee is to be transitioned over to a permanent FTE with urdomain, please forward your response to 
<a href="mailto:[email protected]">Human Resources</a> in order to begin the process.  
<br /><br />
Thank you.
</body></html>
"@

# filter out the users that have a Manager listed and group them using the Manager property
# by grouping first, you only need to do Get-ADUser for each manager once.
$managedUsers = $users | Where-Object { ![string]::IsNullOrEmpty($_.Manager) } | Group-Object Manager

foreach ($mgrGroup in $managedUsers) {
    # get the manager AD object using the DN from the users Manager attribute to get properties we need from that
    $manager  = Get-ADUser -Identity $mgrGroup.Name -Properties EmailAddress
    $mgrName  = $manager.Name
    $mgrEmail = $manager.EmailAddress

    # if the manager has no email address, report it on screen
    if ([string]::IsNullOrWhiteSpace($mgrEmail)) {
        Write-Warning "Could not get email address for manager $($mgrGroup.Name)"
    }
    else {
        # create a table from the user data in the group
        $table = ($mgrGroup.Group | 
                  Select-Object Name, EmailAddress, @{Name = 'ExpiryDate'; Expression = {$_.AccountExpirationDate.ToShortDateString()}} | 
                  ConvertTo-Html -As Table -Fragment) -join [environment]::NewLine

        $mailParams = @{
            To         = $mgrEmail
            From       = '[email protected]'
            Subject    = 'Expiring Team member accounts'
            Body       = $mailTemplate -f $style, $mgrName, $table  # fill in the placeholders of the mail template
            BodyAsHtml = $true
            Priority   = 'High'
            SmtpServer = 'postmaster.urdomain.com'
            # more parameters go here
        }
        # send this manager an email with a table of all expiring users that report to him/her
        Send-MailMessage @mailParams
    }
}

Step 5:

Next (if you want) email each user individually about their accounts getting expired. No need for them to know about the other users

# create a template to use for mailing the users
# this uses 3 placeholders to fill in (style, first name and expiration date)
$mailTemplate = @"
<html><head>{0}</head><body>
Dear {1},<br /><br />
This is to inform you that your user account will expire on <strong>{2}</strong><br /><br />
DesktopSupport
</body></html>
"@

# send each expiring user that has an email address an email individually
$users | Where-Object { ![string]::IsNullOrWhiteSpace($_.EmailAddress) } | ForEach-Object {
        $mailParams = @{
            To         = $_.EmailAddress
            From       = '[email protected]'
            Subject    = 'Your user account will expire soon!'
            Body       = $mailTemplate -f $style, $_.GivenName, ('{0:MMMM d, yyyy}' -f $_.AccountExpirationDate)
            BodyAsHtml = $true
            Priority   = 'High'
            SmtpServer = 'postmaster.urdomain.com'
            # more parameters go here
        }
        #Send-MailMessage @mailParams
}

Step 6:

Look for any 'leftover users' that didn't have an email address.
Just output this as warning onto the console

# test if there were users without email address
$noEmail = @($users | Where-Object { [string]::IsNullOrWhiteSpace($_.EmailAddress) })
if ($noEmail.Count) {
    Write-Warning "These user do not have an email address:"
    $noEmail | Format-Table -Property Name, SamAccountName, AccountExpirationDate
}