Send just one email with accounts expired for their manager.

Hello experts!!

I would like to send just one email to the manager with all accounts that will expire in x days, I do not want to send one by one to the manager. Is anybody tried that?

Thanks.

Not for AD accounts but for many similar requirements, failed agent jobs, error log reports, etc.
Just a case of storing values from your loop to a variable then exporting to a csv, or Excel file, or building a table, and emailing it.
Here’s an example of one I do:

$FailedJobs = @()
$FailedJobs += Run-PAgentJobHistory $SqlServers | Select Server, JobName, StepName , RunDate, Message

# If there are no failed jobs stop the process here
if(!($FailedJobs)) { LogWrite "No Agent Log report" ; Break}

# Construct table for email
$Table = $null
$Table = New-Object System.Data.DataTable "FailedJobs"
$Col1 = New-Object System.Data.DataColumn ServerName,([string])
$Col2 = New-Object System.Data.DataColumn RunDate,([string])
$Col3 = New-Object System.Data.DataColumn JobName,([string])
$Col4 = New-Object System.Data.DataColumn StepName,([string])
$Col5 = New-Object System.Data.DataColumn Message,([string])
$Table.Columns.Add($Col1)
$Table.Columns.Add($Col2)
$Table.Columns.Add($Col3)
$Table.Columns.Add($Col4)
$Table.Columns.Add($Col5)

foreach($failedjob in $FailedJobs) 
{
    # Add rows to table for each failed job
    $Row = $Table.NewRow()
    $Row.ServerName = $FailedJob.server
    $Row.RunDate = $FailedJob.RunDate
    $Row.JobName = $FailedJob.JobName
    $Row.StepName = $FailedJob.StepName
    $Row.Message = $FailedJob.Message
    $Table.Rows.Add($row)
LogWrite "Job $($FailedJob.JobName) failed on $($FailedJob.Server)"
}   

# Format HTML report
$a = ""
$a = $a + "BODY{background-color:#FFFFFF;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + ""

$style = "BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px;}"
$style = $style + "TD{border: 1px solid black; padding: 5px;}"
$style = $style + ""

$MsgBody = "
Hello DBA team,
   
RE: Agent Job Failures                            
Please check the listed Agent jobs
 
$($Table | ConvertTo-Html -Property ServerName,RunDate,JobName,StepName,Message -Head $style | Out-String)
 
 
Thank You,
Enterprise Technical Services Team

"
# Construct & send email
$MailMessage = @{
        To = "ibarnetson@goempyrean.com"
        Cc ="ibarnetson@goempyrean.com"
        #To = "dl-dba@goempyrean.com"
        #Cc = "RMiller@goempyrean.com"
        From = "dl-dba@goempyrean.com"
        Subject = "SQL Agent Logs Report"
        Body = $MsgBody
        Smtpserver = "smarthost.goempyrean.com"
    }

Send-MailMessage @MailMessage -BodyAsHtml

You need to create an array that holds the accounts that are expiring in x days. Then send the data from that array using the

Send-MailMessage

cmdlet.

Post what you have and I’ll show you how to adjust it to your needs. Don’t want to do all the work for you or you’ll never learn :smiley:

Please see what I have… i just would like to send just one email to the manager.

thanks.

cls
Import-Module ActiveDirectory
$OU=“OU=x,OU=x,DC=x,DC=x”
$report = $null
$table = $null
#$date = Get-Date -format “dd/MM/yyyy” # Date format
$date = Get-Date -uformat “%d/%B/%Y” # Date format
$startDate = Get-Date
$endDate = $startDate.AddDays(100)
$file_html = “ad-contractors_expiration.html” # HTML file
$file_csv = “ad-contractors_expiration.csv” # CSV file
$total = (Get-ADUser -filter {AccountExpirationDate -gt $startDate -and AccountExpirationDate -lt $endDate} -SearchBase $OU).count # Total accounts on OU
$domain = (Get-ADDomain).Forest # Domain name
$team = “IT Team” # Team name
$company = “xxx” # Company name
$title_html = “Active Directory - Contractors expiration accounts”
$area = “xx”
#$path = "." # Reports path

#–USER LIST------------------------------------------------#
$table += “$total - USERS WITH EXPIRATION ACCOUNT ON: $OU”

$prop = @(‘SamAccountName’,‘Mail’,‘Department’,‘Company’,‘Title’,‘Enabled’,‘Created’,‘AccountExpirationDate’,‘Description’,‘CanonicalName’,‘Manager’)

$users = @(Get-ADUser -filter {AccountExpirationDate -gt $startDate -and AccountExpirationDate -lt $endDate -or AccountExpirationDate -lt $startdate } -SearchBase $OU -Properties $prop)

List AD user information

#PS C:\AD> Get-ADUser xx -Properties *

$result = @($users | Select-Object SamAccountName, Mail, Department, Company, Title, Enabled, Created, AccountExpirationDate, Description, CanonicalName, @{Name=‘Manager’;Expression={(Get-ADUser $_.Manager).sAMAccountName}})

Sort by user account A-Z

$result = $result | Sort “SamAccountName”

This line will not show script running.

#$result | ft -auto

$table += $result | ConvertTo-Html -Fragment

$format=
"

	BODY{font-family: Calibri; font-size: 12pt;}
	TABLE{border: 1px solid black; border-collapse: collapse; font-size: 12pt; text-align:center;margin-left:auto;margin-right:auto; width='1000px';}
	TH{border: 1px solid black; background: #F9F9F9; padding: 5px;}
	TD{border: 1px solid black; padding: 5px;}
	H3{font-family: Calibri; font-size: 16pt;}
	 
	"

$title=
"

	$title_html
	$company - Domain: $domain - Report Date: $date - Responsible: $team
	
	
	
	
	
	"

$message = “”
$message = $message + “BODY{font-family: Calibri;font-size:20;font-color: #000000}”
$message = $message + “TABLE{margin-left:auto;margin-right:auto;width: 800px;border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}”
$message = $message + “TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color: #F9F9F9;text-align:center;}”
$message = $message + “TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;text-align:center;}”
$message = $message + “”
$message = $message + “”
$message = $message + “ACTIVE DIRECTORY AUDIT”
$message = $message + “Active Directory users’s list: $OU”
$message = $message + “xxx”
$message = $message + “”

$report = $format + $title + $table

#–CREATE HTML-----------------------------------------------------#
#$report | Out-File “$path$file_html” -Encoding Utf8
$report | Out-File $file_html -Encoding Utf8

#–EXPOT CSV FILE
#$result | Sort Company | Export-Csv “$path$file_csv” -NoTypeInformation -Encoding Utf8

$result | Sort Company | Export-Csv $file_csv -NoTypeInformation -Encoding Utf8

#–SEND MAIL NOTIFICATION--------------------------------------#
$from = “xxxx.com
$to = @(“xxxx.com”)
$subject = “$area-$title_html - $date”
$PSEmailServer = “xxxx.com
#$smtp = “192.168.1.200”
#$porta = “25”

We do not use smpt and port

#Send-MailMessage -From $to -To $to -Subject $subject -Attachments $file_html,$file_csv -bodyashtml -Body $message -SmtpServer $smtp -Port $porta

Send-MailMessage -From $from -To $to -Subject $subject -Attachments “$file_html”,“$file_csv” -bodyashtml -Body $message