How could you send a Daily summary email for all the list items based on column status. Below are few of the ways you can do it. I have written a basic powershell script to do this.
1. Create a custom timer job which would run daily.
2. Create a For each loop in sharepoint designer which would parse through all the items in library and send summary email. The workflow will pause for duration.
3. Use powershell to send summary email.Schedule the .ps1 script to run at 6PM everyday.
option 3 would be the most easiest one. so below is the powershell script
Note: The script is “AS IS”. Please test in test environment before you try in production.
Add-PSSnapin “Microsoft.SharePoint.PowerShell”
$web = Get-SPWeb “https://a.contoso.com/sites/finance/”
$list = $web.Lists[“Shared Documents”]
# Approved Internally it stores as Numeric value 0
# Rejected Internally the value of this filed is 1
# Pending Internally the value of this field is 2
$listItems = $list.Items | ?{$_[“Approval Status”] -eq “0”}
$a = Get-Date
$sysdate = $a.ToShortDateString()
$Approveditemsforday = New-Object System.Collections.ArrayList
foreach($item in $listItems)
{
$Datecompare = $item[“Modified”]
$ApprovedDate = $Datecompare.ToString().split(” “)
if($sysdate -eq $ApprovedDate[0])
{
$Approveditemsforday.Add($item.Name);
}
}
# Pipe Get-Content result to the Out-String to display data in email body. otherwise the data will be in single line
$Approveditemsforday2 = $Approveditemsforday | Out-String
function sendMail{
#SMTP server name
$smtpServer = “smtp.contoso.com”
#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 = “SharePointAdmin@contoso.com”
$msg.ReplyTo = “none@contoso.com”
$msg.To.Add(“smartsharepointguy@contoso.com”)
$msg.subject = “Daily Invoice Approved Summary Email $sysdate”
$body = “List of Invoices Approved for the Date $sysdate`r`n`r`n”
$body += “$Approveditemsforday2`r`n`r`n”
$msg.body = “$body”
#Sending email
$smtp.Send($msg)
}
#Calling function
sendMail
$web.dispose();
SharePoint SQL IIS Blog 