SharePoint Reminder/Alert with PowerShell

Leave a comment

February 3, 2014 by Ravi C Khambhati

So here is another tool I always wanted to work on. I am not sure how useful this will be for you guys but this solution will definitely a quick solution,

As mentioned in title this is purely PowerShell solution which will take xml file as input with all required parameters/inputs. So first I will explain individual section of the input xml file.

Setting single/multiple reminder

Below is the overall format of the xml file.

<?xml version="1.0" encoding="utf-8"?>
<Reminders>
   <Reminder>
   </Reminder>
</Reminders>

As you can see, you can set single/multiple reminders with the help of single xml input file.

Setting web/site and list/library

With this xml file we are configuring reminder for Task List which will send reminder for not started task. So lets see how we can set that.

<!--Url of list--> 
<WebUrl><your web/site url></WebUrl>
<!--List internal name for which reminder to be sent-->
<ListInternalName>Task</ListInternalName>
<!--Caml query to filter list for which reminder to be sent--> 
<Caml>
  <Where>
    <Eq>
      <FieldRef Name='Status' />
      <Value Type='Choice'>Not Started</Value>
    </Eq>
  </Where>
</Caml>
<!--view fields for query-->
<ViewFields>
  <FieldRef Name="FSObjType" />
  <FieldRef Name="FileDirRef" />
  <FieldRef Name="FileLeafRef" />
  <FieldRef Name="AssignedTo" />
  <FieldRef Name="Author" />
  <FieldRef Name="Editor" />
  <FieldRef Name="Title" />
  <FieldRef Name="DueDate" />
  <FieldRef Name="PercentComplete" />
  <FieldRef Name="Checkmark" />
  <FieldRef Name="Body" />
  <FieldRef Name="Modified" />
  <FieldRef Name="Predecessors" />
  <FieldRef Name="Priority" />
  <FieldRef Name="RelatedItems" />
  <FieldRef Name="StartDate" />
  <FieldRef Name="Status" />
</ViewFields>

So with this I am setting 4 parameters Web/Site URL, List Internal Name, CAML Query, ViewFields. As mentioned earlier that we are interested in sending reminder to those people whose tasks are not started so that is the filtering criteria in caml query. You can create caml as per your requirement.

Setting email template

Next part is email template and that is also part of the same xml and its straight forward and doesn’t require any explanation. Let me know if you have doubt on this section. Just one note on token format which has to “{” + InternalFieldName + “}” and this field must be there in view fields.

<Email>
  <!--From email address--> 
  <From>reminder@yourdomain.com</From>
  <!--To email address {FieldInternalName}-->
  <To>{AssignedTo}</To>
  <Cc>{AssignedTo},ravisoftltd@gmail.com,{Editor}</Cc>
  <Bcc>{AssignedTo},ravisoftltd@gmail.com,{Editor}</Bcc>
  <!--Subject of email-->
  <Subject>Reminder for "{Title}" due on "{DueDate}"</Subject>
  <!--Email Template--> 
  <Body>
    %Complete: {PercentComplete}
    AssignedTo: {AssignedTo}
    Completed: {Checkmark}
    Created: {Created}
    Description: {Body}
    Due Date: {DueDate}
    Modified: {Modified}
    Predecessors: {Predecessors}
    Priority: {Priority}
    Related Items: {RelatedItems}
    Start Date: {StartDate}
    Task Name: {Title}
    Task Status: {Status}
    Created By: {Author}
    Modified By: {Editor}
  </Body>
  <!--SMTP--> 
  <Smtp></Smtp>
</Email>

Setting log file

And the last part is about log file. The log is generated in xml format. With this we are setting three parameters File Format, Is log folder, Log folder format. You can read comments in below configuration section for further details. With below configuration, tool will create date folder and log file with the given format.

<LogFile>
  <!-- format of log file -->
  <FileFormat>yyyy-MM-dd_hhmmss</FileFormat>
  <!-- if this value is true then folder will be created every day -->
  <IsLogFolder>true</IsLogFolder>
  <!-- format of folder for eg yyyy-dd-MM -->
  <LogFolderFormat>yyyy-MM-dd</LogFolderFormat>
</LogFile>

So here we are ready with input xml configuration file for PowerShell script. Once you are ready with your input file create a folder(for e.g c:\Reminders) and save this configuration file to your server. In the same folder copy the content of the powershell script(will share in a moment). So now you should have XML configuration file and PowerShell script.

Below is sample log file content,

<?xml version='1.0' encoding='utf-8' ?>
<ReminderLog>
  <WebUrl><your web/site url></WebUrl>
  <ListInternalName>Task</ListInternalName>
  <Emails>
    <Email>
      <From>reminder@yourdomain.com</From>
      <To>ravi.khambhati@yourdomain.com</To>
      <Cc>ravi.khambhati@yourdomain.com,ravisoftltd@gmail.com,ravi.khambhati@yourdomain.com</Cc>
      <Bcc>ravi.khambhati@yourdomain.com,ravisoftltd@gmail.com,ravi.khambhati@yourdomain.com</Bcc>
      <Subject>Reminder for "This is test task" due on "1/30/2014 12:00:00 AM"</Subject>
      <Body>
        %Complete: 0
        AssignedTo: ravi.khambhati@yourdomain.com
        Completed: boolean;#0
        Created: 1/29/2014 11:42:21 AM
        Description:
        Due Date: 1/30/2014 12:00:00 AM
        Modified: 1/29/2014 11:43:33 AM
        Predecessors:
        Priority: (2) Normal
        Related Items:
        Start Date: 1/29/2014 12:00:00 AM
        Task Name: This is test task
        Task Status: Not Started
        Created By: ravi.khambhati@yourdomain.com
        Modified By: ravi.khambhati@yourdomain.com
      </Body>
      <Error>
        error if any
      </Error>
    </Email>
  </Emails>
</ReminderLog>

Setting PowerShell script to windows task schedule

Once you are ready with your XML Configuration and Powershell script create a bat file with below content,

cd C:\PSTFS\Reminders
powershell.exe C:\Reminders\Reminder.ps1 C:\Reminders\ReminderList.xml

And here you are ready with your bat file to schedule in windows task scheduler. Below are steps to create scheduler

  • Open task scheduler: Start -> Administrative Tools -> Task Scheduler
  • Setup Triggers: Configure when you want to run this scheduler
  • Select Action: Select batch file which will trigger powershell script



Complete XML Configuration file

<?xml version="1.0" encoding="utf-8"?>
<Reminders>
  <Reminder>
    <!--Url of list-->
    <WebUrl><your web/site url></WebUrl>
    <!--List internal name for which reminder to be sent-->
    <ListInternalName>Task</ListInternalName>
    <!--Caml query to filter list for which reminder to be sent-->
    <Caml>
      <Where>
        <Eq>
          <FieldRef Name='Status' />
          <Value Type='Choice'>Not Started</Value>
        </Eq>
      </Where>
    </Caml>
    <!--view fields for query-->
    <ViewFields>
      <FieldRef Name="FSObjType" />
      <FieldRef Name="FileDirRef" />
      <FieldRef Name="FileLeafRef" />
      <FieldRef Name="AssignedTo" />
      <FieldRef Name="Author" />
      <FieldRef Name="Editor" />
      <FieldRef Name="Title" />
      <FieldRef Name="DueDate" />
      <FieldRef Name="PercentComplete" />
      <FieldRef Name="Checkmark" />
      <FieldRef Name="Body" />
      <FieldRef Name="Modified" />
      <FieldRef Name="Predecessors" />
      <FieldRef Name="Priority" />
      <FieldRef Name="RelatedItems" />
      <FieldRef Name="StartDate" />
      <FieldRef Name="Status" />
    </ViewFields>
    <Email>
      <!--From email address-->
      <From>reminder@yourdomain.com</From>
      <!--To email address {InternalListName.FieldInternalName} / {FieldInternalName}-->
      <To>{AssignedTo}</To>
      <Cc>{AssignedTo},ravisoftltd@gmail.com,{Editor}</Cc>
      <Bcc>{AssignedTo},ravisoftltd@gmail.com,{Editor}</Bcc>
      <!--Subject of email-->
      <Subject>Reminder for "{Title}" due on "{DueDate}"</Subject>
      <!--Email Template-->
      <Body>
        %Complete: {PercentComplete}
        AssignedTo: {AssignedTo}
        Completed: {Checkmark}
        Created: {Created}
        Description: {Body}
        Due Date: {DueDate}
        Modified: {Modified}
        Predecessors: {Predecessors}
        Priority: {Priority}
        Related Items: {RelatedItems}
        Start Date: {StartDate}
        Task Name: {Title}
        Task Status: {Status}
        Created By: {Author}
        Modified By: {Editor}
      </Body>
      <!--SMTP-->
      <Smtp></Smtp>
    </Email>
    <LogFile>
      <!-- format of log file -->
      <FileFormat>yyyy-MM-dd_hhmmss</FileFormat>
      <!-- if this value is true then folder will be created every day -->
      <IsLogFolder>true</IsLogFolder>
      <!-- format of folder for eg yyyy-dd-MM -->
      <LogFolderFormat>yyyy-MM-dd</LogFolderFormat>
    </LogFile>
  </Reminder>
</Reminders>

PowerShell Script

param([String]$xmlFileName)

function ProcessChildItems
{
	param($spList, [string]$innerXml, [string]$viewFields, [string]$folder, [string]$filePath)
	
	$spQuery = New-Object Microsoft.SharePoint.SPQuery
	$spQuery.Query = $innerXml
	$spQuery.ViewFields= $viewFields
	
	if ([string]::IsNullOrEmpty($folder) -eq $false)
    {
        $spQuery.Folder = $spList.ParentWeb.GetFolder($folder);
    }
	
	$spListChildItems = $spList.GetItems($spQuery)
	foreach ($item in $spListChildItems)
    {
		$itemType = [Convert]::ToInt32($item["FSObjType"])

		if ( $itemType -eq 1 )
		{
			$itemFolder = [string]::Format("{0}/{1}", [Convert]::ToString($item["FileDirRef"]), $item.Name)
			ProcessChildItems $spList $innerXml $viewFields $itemFolder $filePath
		}
		else
		{
			SendEmail $item $reminder $filePath
		}
	}
}

function GetTokens
{
	param([String]$tokens)
	
	if ( [String]::IsNullOrEmpty($tokens) -eq $true )
	{
		return $null
	}
	
	$tokenList = @()
	
	$tokenStartIndex = 0
	
	while( $tokenStartIndex -ne "-1" )
	{
		$tokenStartIndex = $tokens.IndexOf("{", $tokenStartIndex)
		
		if( $tokenStartIndex -eq "-1" )
		{
			break;
		}
		
		$tokenEndIndex = $tokens.IndexOf("}", $tokenStartIndex)
		
		$singleToken = $tokens.Substring($tokenStartIndex + 1, $tokenEndIndex - $tokenStartIndex - 1)
		
		if ( $tokenList -eq $null )
		{
			$tokenList = @($singleToken)
		}
		else
		{
			$tokenList += $singleToken
		}
		
		$tokenStartIndex = $tokenStartIndex + 1
	}
	
	return $tokenList
}

function ReplaceToken
{
	param([Microsoft.SharePoint.SPListItem]$item, [String]$content)
	
	$tempContent = $content
	$tokenList = $null
	
	if ( [String]::IsNullOrEmpty($content) )
	{
		return $null
	}
	
	$tokenList = GetTokens $content
	
	foreach($token in $tokenList)
	{
		$fieldType = $item.Fields.GetFieldByInternalName($token).TypeAsString
		
		$fieldValue = $item[$token]
		$actualFieldValue = [String]::Empty
		
		if( [String]::Compare($fieldType, "UserMulti", $true) -eq 0 )
		{
			$users = New-Object Microsoft.SharePoint.SPFieldUserValueCollection($item.Web, $fieldValue)
			$usersEmailAddress = [String]::Empty
			
			foreach($user in $users)
			{
				$user = $user.User
				
				$actualFieldValue += $user.Email + ","
			}
			
			$actualFieldValue = $actualFieldValue.Trim(',')
			
		}
		elseif( [String]::Compare($fieldType, "User", $true) -eq 0 )
		{
			$user = New-Object Microsoft.SharePoint.SPFieldUserValue($item.Web, $fieldValue)
			$user = $user.User
			$actualFieldValue = $user.Email
		}
		else
		{
			$actualFieldValue = [Convert]::ToString($fieldValue)
		}
		$tempContent = $tempContent.Replace("{" + $token + "}", $actualFieldValue)
	}
	
	return $tempContent
}

function SendEmail
{
	param([Microsoft.SharePoint.SPListItem]$item, [System.Xml.XmlElement]$reminder, [string]$filePath)
	
	try
	{
		#SMTP server name
		$smtpServer = $reminder.Email.Smtp

		#Creating a Mail object
		$msg = new-object Net.Mail.MailMessage

		#Creating SMTP server object
		$smtp = new-object Net.Mail.SmtpClient($smtpServer)

		#Email structure
		$msg.From = $reminder.Email.From
		$msg.subject = $reminder.Email.Subject
		$msg.body = $reminder.Email.Body
		
		$toEmail = ReplaceToken $item $reminder.Email.To
		foreach($email in $toEmail.Split(",", [StringSplitOptions]::RemoveEmptyEntries))
		{
			$msg.To.Add($email)
		}
		
		$ccEmails = ReplaceToken $item $reminder.Email.Cc
		if ( $ccEmails -ne $null )
		{
			foreach($email in $ccEmails.Split(",", [StringSplitOptions]::RemoveEmptyEntries))
			{
				$msg.Cc.Add($email)
			}
		}
		
		$bccEmails = ReplaceToken $item $reminder.Email.Bcc
		if ( $bccEmails -ne $null )
		{
			foreach($email in $bccEmails.Split(",", [StringSplitOptions]::RemoveEmptyEntries))
			{
				$msg.Bcc.Add($email)
			}
		}
		
		$msg.subject = ReplaceToken $item $reminder.Email.Subject
		$msg.body = ReplaceToken $item $reminder.Email.Body
		
		if( $toEmail -eq $null -or [string]::IsNullOrEmpty($toEmail) )
		{
			$toEmail = $reminder.Email.To
			throw "To email id is missing"
		}
		
		Write-Host "Sending email..." $toEmail -ForegroundColor Green
		$smtp.Send($msg)
		
		$content = [string]::Format("<Email><From>{0}</From><To>{1}</To><Cc>{2}</Cc><Bcc>{3}</Bcc><Subject>{4}</Subject><Body>{5}</Body></Email>", `
			$msg.From, $toEmail, $ccEmails, $bccEmails, $msg.subject, $msg.body)
			
		WriteToFile $filePath $content
	}
	catch [Exception]
	{
		Write-Host "Error while sending email" $webUrl -ForegroundColor Red
		Write-Host $_.Exception
		
		
		$content = [string]::Format("<Email><From>{0}</From><To>{1}</To><Cc>{2}</Cc><Bcc>{3}</Bcc><Subject>{4}</Subject><Body>{5}</Body><Error>{6}</Error></Email>", `
			$msg.From, $toEmail, $ccEmails, $bccEmails, $msg.subject, $msg.body, $_.Exception)
			
		WriteToFile $filePath $content
	}
}

function WriteToFile
{
	param([string]$fileName, [string]$content)
	
	$fileObj = New-Object System.IO.StreamWriter($fileName, $true)
	try
	{
		$fileObj.WriteLine($content);
	}
	finally
	{
		if($fileObj -ne $null)
		{
			$fileObj.Dispose()	
		}
	}
}

if ( [String]::IsNullOrEmpty($xmlFileName) -eq $true )
{
     Write-Host 'Input parameter missing: File Name' -ForegroundColor Red;
     return;
}

cls

if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{    
	Write-Host "Adding 'Microsoft.SharePoint.PowerShell'" -ForegroundColor Green
	Add-PSSnapin Microsoft.SharePoint.PowerShell
}

try
{
	Write-Host "Input file:", $xmlFileName #-ForegroundColor Yellow

	[xml]$reminders = Get-Content $xmlFileName

	$currentPath = Get-Location | select Path

	foreach( $reminder in $reminders.Reminders.Reminder)
	{
		$webUrl = $reminder.WebUrl
		try
		{
			$fileName = [datetime]::Now.ToString($reminder.LogFile.FileFormat)
			$IsDateFolder = $false
			
			if([string]::IsNullOrEmpty($reminder.LogFile.IsLogFolder) -eq $false)
			{
				$IsDateFolder = [Convert]::ToBoolean($reminder.LogFile.IsLogFolder)
			}
			
			$logFolder = "\"
			
			if ( $IsDateFolder -eq $true )
			{
				$logFolder = [datetime]::Now.ToString($reminder.LogFile.LogFolderFormat)
				$logFolder = "\" + $logFolder + "\"
				
				$logFolderPath = $currentPath.Path + $logFolder
				
				$isLogFolderExists = Test-Path $logFolderPath
				
				if ( $isLogFolderExists -eq $false )
				{
					Write-Host "Creating new directory '$logFolder'" -ForegroundColor Green
					$newFolder = New-Item -ItemType directory -Path $logFolderPath
					Write-Host "Directory '$logFolder' created" -ForegroundColor Green
				}
			}
			
			$filePath = $currentPath.Path + $logFolder + $fileName + ".xml"
			$content = "<?xml version='1.0' encoding='utf-8' ?><ReminderLog><WebUrl>" + $webUrl + "</WebUrl>"
			WriteToFile $filePath $content

			if ( [string]::IsNullOrEmpty($webUrl) )
			{
				Write-Host "Web url is not configured" -ForegroundColor Red
				return;
			}
			Write-Host $webUrl -ForegroundColor Yellow
			
			$listInternalName = $reminder.ListInternalName
			if ( [string]::IsNullOrEmpty($webUrl) )
			{
				Write-Host "ListName is not configured" -ForegroundColor Red
				return;
			}
			Write-Host $listInternalName -ForegroundColor Yellow
			
			$content = "<ListInternalName>" + $listInternalName + "</ListInternalName>"
			WriteToFile $filePath $content
			WriteToFile $filePath "<Emails>"
			
			$spWeb = Get-SPWeb -Identity $webUrl
			try
			{
				if ( $spWeb -eq $null )
				{
					Write-Host "Invalid URL:" $webUrl
					return;
				}
				
				$spList = $spWeb.Lists.TryGetList($listInternalName)
				
				if ( $spList -eq $null )
				{
					Write-Host "List not found" -ForegroundColor Red
					return;
				}
				
				if ( [String]::IsNullOrEmpty($reminder.Email.Smtp) )
				{
					Write-Host "SMTP is not configured" -ForegroundColor Red
					return
				}
				
				if ( $reminder.Email.From -eq $null )
				{
					Write-Host "From EmailId is not configured" -ForegroundColor Red
					return
				}
				
				if ( $reminder.Email.From -eq $null )
				{
					Write-Host "From EmailId is not configured" -ForegroundColor Red
					return
				}
				
				if ( $reminder.Email.To -eq $null )
				{
					Write-Host "To EmailId is not configured" -ForegroundColor Red
					return
				}
				
				$innerXml = $reminder.Caml.InnerXml
				$viewFields = $reminder.ViewFields.InnerXml
				
				$spQuery = New-Object Microsoft.SharePoint.SPQuery
				$spQuery.Query = $innerXml
				$spQuery.ViewFields= $viewFields
				
				$spListItems = $spList.GetItems($spQuery)
				foreach ($item in $spListItems)
			    {
					$itemType = [Convert]::ToInt32($item["FSObjType"])
					
					if ( $itemType -eq 1 )
					{
						$itemFolder = [string]::Format("{0}/{1}", [Convert]::ToString($item["FileDirRef"]), $item.Name)
						ProcessChildItems $spList $innerXml $viewFields $itemFolder $filePath
					}
					else
					{
						SendEmail $item $reminder $filePath
					}
			    }
			}
			finally {
				if ( $spWeb -ne $null )
				{
    				$spWeb.Dispose()
				}
			}
		}
		catch [Exception]
		{
			Write-Host "Error while sending reminder" $webUrl -ForegroundColor Red
			Write-Host $_.Exception
		}
		
		WriteToFile $filePath "</Emails>"
		WriteToFile $filePath "</ReminderLog>"
	}
	
	Write-Host "Completed" -ForegroundColor Green
}
catch [Exception]
{
	Write-Host "Error while sending reminder" -ForegroundColor Red
	Write-Host $_.Exception
}

Google+

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: