Document Expiration Monitor – Automation with PowerShell Script

Requirement: There are bunch of document libraries with several documents loaded on various site collections. We’ve to monitor their expiry date based on a column and send e-mail notification to all members of  a  SharePoint Group. Also all E-mails sent should be logged in a tracking list.

Solution: Lets write a PowerShell script to scan required document libraries and send E-mail. BTW – Its for MOSS 2007 (But the script works on any SharePoint versions, of course!)

PowerShell Script to monitor Document Expiration based on a Date column:

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

##################################################################################
##  VARIABLES SECTION ##
##################################################################################

$LibraryURLs = @("https://intranet.Crescent.com/sites/IT/docs/", "https://intranet.Crescent.com/sites/operations/inventory/", "https://intranet.Crescent.com/sites/Sales/documents")

### Variables for Email setup
#URL of the Site where the SharePoint group exists
$EmailGroupSite = "https://intranet.Crescent.com/sites/IT/"
#Name of the SharePoint Group to which emails to be sent
$EmailGroupName= "Report Group"

#Internal Name of the Column which stores the expiration date of documents
$ExpiryDateColumnInternalName = "Expiration_x0020_Date0"
#How to get the Internal name of a SharePoint Column? 
#Refer https://www.sharepointdiary.com/2011/06/sharepoint-field-display-name-vs-internal-name.html

#Site URL where the tracking list "Document Expiration Monitor - Tracking" to be maintained 
$TrackingSiteURL = "https://intranet.Crescent.com/sites/IT/"

$ExpiryDays = 7  

$SMTPServer = "smtp.Crescent.com"

$EmailFrom = "DocumentExpiryMonitor@YOURCOMPANY.com"
$EmailSubject = "Document Expiration Monitor Report as on $(Get-Date)"

#Region Functions
###############################################################################
##  FUNCTIONS ##
###############################################################################

#Functions for Get-SPSite & Get-Web in MOSS 2007
function global:Get-SPSite($url) {
    return new-Object Microsoft.SharePoint.SPSite($url)
 } 
 
function global:Get-SPWeb($url) {
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null) {
               $web=$site.OpenWeb();        
            }
    return $web
} 

#Validate the Web at given URL
function Validate-Web($WebURL)
 { 
  try {
    $web = Get-SPWeb $WebURL
    return $web
      }
  catch {
    $Message = "Error in Site URL:$WebURL. Please provide valid Site URL!"
    Write-Host $Message -ForegroundColor red
    Append-LogMessage $Message $error[0]
    
    #Continue executing code
    continue
    
    return $null
   }
 }

#Validate the Web at given URL
function Validate-SPSite($SiteURL)
 { 
  try {
    $Site = Get-SPSite $SiteURL
    return $Site
      }
  catch {
    $Message = "Error in Site Collection URL:$SiteURL."
    Write-Host $Message -ForegroundColor red
    Append-LogMessage $Message $error[0]
    
    #Continue executing code
    continue
    
    return $null
   }

 }
#Function to validate given List URL 
 function Validate-List($ListURL)
 { 
  try {
    $list = $web.GetList($ListURL)
    return $list
      }
  catch {
    $Message = "Error in List URL: $ListURL! Please provide valid List URL!"
    Write-Host $Message -ForegroundColor red
    Append-LogMessage $Message $error[0]
    
    #Continue executing code
    continue
    
    return $null
   }
 }
 
function Send-Email ($EMailBody, $EmailTo) {
 try {
    #Send the Mail
    $MailMessageParameters = @{
   SmtpServer = $SMTPServer
      Subject = $EmailSubject 
      Body = $EmailBody 
      From = $EmailFrom 
      To =  $EmailTo 
      }
    Send-MailMessage @MailMessageParameters -BodyAsHtml
   Append-LogMessage "Email has been sent to :"$EmailTo
 }
 catch {
  Write-Host "Error sending email, Please check log file!" -ForegroundColor Red
  Append-LogMessage $error[0]
 }
}

#Function to Get the Location where script is located
function GetScriptDir
{
 $Invocation = (Get-Variable MyInvocation -Scope 1).Value
 return Split-Path $Invocation.MyCommand.Path
}

#Extract Emails from each user in a Group
function Get-GroupEmails()
{
 try {
  $Emails=@()
  $Site = Validate-SPSite $EmailGroupSite
   if($site -ne $null)
   {
    $Group = $site.RootWeb.sitegroups | Where {$_.Name -match $EmailGroupName}
    #$Group= $web.SiteGroups[$GroupName]   
    if($Group -ne $null)
    {     
     $Users = $Group.Users
        foreach ($User in $Users)
                 {
         if( ($user.Email -ne [string]::IsNullOrEmpty()) -and ($user.Email -ne $null) )
         { 
          $Emails+=$user.Email
         }
        }
      $Emails =  $Emails -split ","   
      return $Emails
    }
    else
    {
     $Message = "E-mail Group Name:$EmailGroupName Not found!"
     Write-Host $Message -ForegroundColor red
     Append-LogMessage $Message $error[0]
     return $null
    }
   }
  }
  catch {
    $Message = "Error in Get-GroupEmails Function!"
    Write-Host $Message -ForegroundColor red
    Append-LogMessage $Message $error[0]
    
    #Continue executing code
    continue
    
    return $null
   }  
  }

#Function to Log details to Tracking List
function LogTo-TrackingList($EmailBody)
{
 $web = Validate-Web $TrackingSiteURL
 if($web -eq $null) 
 {
  # Creating base file to append to, adding a new line for readability
  $Message = "Tracking Site URL is not valid!"
  Write-Host $Message -ForegroundColor Red
  Append-LogMessage $Message
  return
 } 
 
 #Try to Get the "Tracking List"
 $TrackingList = $web.Lists | Where { $_.Title -match "Document Expiration Monitor - Tracking"}
  
 #If Tracking list doesnt exist, Create it
 if($TrackingList -eq $null)
 {
  #Create New Tracking List
  $TrackingListID = $Web.Lists.Add("DEMTracking","List to Track Document Expiration Monitor Mails",[Microsoft.SharePoint.SPListTemplateType]::GenericList)
  #Set Name for the List
  $TrackingList = $web.Lists[$TrackingListID]
  $TrackingList.Title="Document Expiration Monitor - Tracking"
  $TrackingList.update()

  #Rename the "Title Field" to Tracking ID
  $TitleField = $TrackingList.Fields.GetFieldByInternalName("Title")
  #Set Field Display Name and Update
  $TitleField.Title ="Tracking Date"
  $TitleField.Update()

  #Add Email Body Field 
  $EmailBodyField = $TrackingList.Fields.Add("Email Body","Note",$true)
  $trackingList.Fields["Email Body"].RichText = $True
  $trackingList.Fields["Email Body"].RichTextMode = "FullHtml"
  $trackingList.Fields["Email Body"].update()

 }
    #Add New Item to Tracking List
    $item = $TrackingList.Items.Add()
    #Set the Tracking Date & Email Body field values
    $item["Email Body"] = $EmailBody 
     
    #Set the Tracking Date field
    $item["Tracking Date"] = $TodaysDate
    $item.update()
  
   #Log details to Tracking List
   $web.dispose()
 
}

# Appends text to the log message
function Append-LogMessage($text) 
{
 Add-Content $LogTextFileName $text"`r" -Encoding UTF8 
}

#EndRegion

#Variables for Internal processing
$ScriptPath = GetScriptdir

#Get the Template HTM File with CSS
$MailContent = Get-Content -Path "$ScriptPath\template.htm" -Encoding UTF7 | Out-String

$CAMLDateFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
$DateFormat = "MM/dd/yyyy"

$TodaysDate = Get-date
$TodaysDateFormatted = (Get-date).ToString($CAMLDateFormat) 
$ExpirationDate = (Get-Date).AddDays($ExpiryDays)
$ExpirationDateFormatted = (Get-Date).AddDays($ExpiryDays).ToString($CAMLDateFormat) 
$LogTextFileName = "$ScriptPath\DEM_LOG.txt"
$Message =""

# Creating base file to append to, adding a new line for readability
$Message = "Script Execution Started at $TodaysDate." >> $LogTextFileName
Write-Host $Message -ForegroundColor DarkGreen
Append-LogMessage $Message

###############################################################################
##  PROCESS EACH DOCUMENT LIBRARY AND SEND EMAIL ##
###############################################################################

 ########## Process "Expiring Documents" ###########
 $MailContent+= "<h2> Document Expiry Monitor - Report as on : $(Get-Date) </h2>"
 $ExpiringContentHeader =  "<h3 style='color: #333399;'> List of Expiring Documents: </h3> <table class='altrowstable' cellpadding='5px' cellspacing='5'><tr><th>Document Name </th><th>Created By</th><th>Created on</th><th> Expiry Date </th><th> URL </th></tr>"
 $MailContent += $ExpiringContentHeader 
 $ExpiringContent=""
 $ExpiredContent=""
 
# Loop through items and Get all Expiring Documents
 foreach ($ListURL in $LibraryURLs) 
 { 
  #Get the web 
  $web = Validate-Web $ListURL
  
  if($web -eq $null) {
   #Skip the current list and proceed with the next from array
   continue
  }
  
  #Get the List
  $list = Validate-List $ListURL
  if($list -eq $null) {
   #Skip the current list and proceed with the next from array
   continue
  }
  
 #Check if the list has the "Expiration Date" Column
 if ($list.Fields.GetFieldByInternalName($ExpiryDateColumnInternalName) -eq $false)
 {
  $Message = "$ListURL does not contains Expiration Date Field specified!"
  Write-Host $Message -ForegroundColor red
  Append-LogMessage $Message
  
  #Skip the current list and proceed with the next from array
  continue
 }

 # Write out the dates we're looking for
 $Message = "Searching $ListURL for Expiring documents with Expiration Date between " + $TodaysDate + " - " + $ExpirationDate
 Write-Host $Message -ForegroundColor DarkGreen
 Append-LogMessage $Message

 # Get all Expiring Documents
 $ExpiringQuery = '<Where><And><Geq><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $TodaysDateFormatted + 

'</Value></Geq><Leq><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $ExpirationDateFormatted + '</Value></Leq></And></Where>'
 $SPExpiringQuery = new-object Microsoft.SharePoint.SPQuery
    $SPExpiringQuery.ViewAttributes = "Scope='Recursive'"
 $SPExpiringQuery.Query = $ExpiringQuery
 $ExpiringListItems = $List.GetItems($SPExpiringQuery)
 $Message = "Found " + $ExpiringListItems.Count + " Expired document(s)."
 Write-Host $Message -ForegroundColor DarkGreen
 Append-LogMessage $Message

 #If There are some expiring documents found
  if($ExpiringListItems.Count -gt 0)
  {    
   foreach($Item in $ExpiringListItems)
   {
    $ExpiringContent+= "<tr> <td> $($item.Name)  </td> <td> $($item.File.Author.Name)  </td> <td> $($item.File.TimeCreated.ToString($DateFormat)) </td> <td> $($Item

[$ExpiryDateColumnInternalName].ToString($DateFormat)) </td>  <td> <a href='$($web.URL)/$($item.URL)'>$($web.URL)/$($item.URL)</a> </td> </tr>"
   }
  }
 }
 if($ExpiringContent.Length -eq 0)
 {
  $ExpiringContent+= "<tr> <td colspan=5> No Expiring Documents Found! </td> </tr>"
 }
 $ExpiringContent+= "</table><br/>" 
  
 $MailContent += $ExpiringContent
 
 ########## Process "Expired Documents " ###########
 $ExpiredContentHeader =  "<h3 style='color: #660033;'> List of Expired Documents: </h3> <table class='altrowstable' cellpadding='5px' cellspacing='5'><tr><th>Document Name </th><th>Created 

By</th><th>Created on</th><th> Expiry Date </th><th> URL </th></tr>"
  $MailContent += $ExpiredContentHeader 
 
 $ExpiredContent=""
# Loop through items and Get all Expired Documents
 foreach ($ListURL in $LibraryURLs) 
 { 
  if($web -eq $null) {
   #Skip the current list and proceed with the next from array
   continue
  }
  
  #Get the List
  $list = Validate-List $ListURL
  if($list -eq $null) {
   #Skip the current list and proceed with the next from array
   continue
  }
 
 #Check if the list has the "Expiration Date" Column
 if ($list.Fields.GetFieldByInternalName($ExpiryDateColumnInternalName) -eq $false) 
 {
  $Message = "$ListURL does not contains Expiration Date Field specified!"
  Write-Host $Message -ForegroundColor red
  Append-LogMessage $Message
  
  #Skip the current list and proceed with the next from array
  continue
 }

 # Write out the dates we're looking for
 $Message = "Searching $ListURL for Expired documents with Expiration Date between " + $TodaysDate + " - " + $ExpirationDate
 Write-Host $Message -ForegroundColor DarkGreen
 Append-LogMessage $Message

 # Get all Expired Documents
 $ExpiredQuery = '<Where><Lt><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $TodaysDateFormatted + '</Value></Lt></Where>'
 $SPExpiredQuery = new-object Microsoft.SharePoint.SPQuery
    $SPExpiredQuery.ViewAttributes = "Scope='Recursive'"
 $SPExpiredQuery.Query = $ExpiredQuery
 $ExpiredListItems = $List.GetItems($SPExpiredQuery)
 
 $Message = "Found " + $ExpiredListItems.Count + " Expired document(s)."
 Write-Host $Message -ForegroundColor DarkGreen
 Append-LogMessage $Message 

 #If There are some expired documents found
  if($ExpiredListItems.Count -gt 0)
  {    
   foreach($Item in $ExpiredListItems)
   {
    $ExpiredContent+= "<tr> <td> $($item.Name) </td> <td> $($item.File.Author.Name) </td> <td> $($item.File.TimeCreated.ToString($DateFormat)) </td> <td> $($Item

[$ExpiryDateColumnInternalName].ToString($DateFormat)) </td>  <td> <a href='$($web.URL)/$($item.URL)'>$($web.URL)/$($item.URL)</a> </td> </tr>"
   }
  }
 }
 if($ExpiredContent.Length -eq 0)
 {
  $ExpiredContent+= "<tr> <td colspan=5> No Expired Documents Found! </td> </tr>"
 }
 $ExpiredContent+= "</table><br/>" 
 
 
 $MailContent += $ExpiredContent
 $MailContent += "</body></html>"

 #Email the Body 
 $EmailAddresses = Get-GroupEmails
 if($EmailAddresses -ne $null)
 {
  Send-Email $MailContent $EmailAddresses 
 }
 
 #Log to Tracking table
 LogTo-TrackingList $MailContent

###############################################################################
##  CLEAN UP ##
###############################################################################

# Log end of processing
$Message = "Finished Processing Document Expiration Monitor Script by $(Get-Date)."
Write-Host $Message -ForegroundColor DarkGreen
Append-LogMessage $Message

# Dispose of the web object
$web.Dispose()

Template.htm File content:

<html>
<head> 
<!-- CSS goes in the document HEAD or added to your external stylesheet -->
<style type="text/css">
body{
font-family: Calibri;
 height: 12pt;
}
table.altrowstable {
 border-collapse: collapse;
 font-family: verdana,arial,sans-serif;
 font-size:11px;
 color:#333333;
 border-width: 1px;
 border-color: #a9c6c9;
 border: b1a0c7 0.5pt solid;  
}
table.altrowstable th {
 border-width: 1px;
 padding: 5px;
 background-color:#8064a2;
 border: #b1a0c7 0.5pt solid;
 font-family: Calibri;
 height: 15pt;
 color: white;
 font-size: 11pt;
 font-weight: 700;
 text-decoration: none;
}
table.altrowstable td {
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 400; text-decoration: none;
}
</style>
</head>
<body>

PowerShell Script in Action:

Document Expiration Monitor - Automation with PowerShell Script

Tracking List:

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *