Recently, I came across a scenario where there is a list which contains visitors' information and the admin team wants to get an email of Visitors for the following day.

This can be easily achieved using Timer Job or CSOM (using scheduler). I tried to achieve this using Powershell and Task Scheduler.

Here is my list.


Columns NamesData Type
Date of Visit Date and Time
Host People
Name fo visitor Single of Text
Company Single of Text
License Plate Number Single of Text
Created a View and applied Today+1 filter on the "Date of Visit" column.
Here is the PowerShell script to fetch the list data
  1. Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
  3. $MyWeb = Get-SPWeb "http://WebApp/Web/"
  4. $MyList = $MyWeb.Lists["ListName"]
  5. $spQuery = New-Object Microsoft.SharePoint.SPQuery
  6. $camlQuery = '<Where><Eq><FieldRef Name="Date_x0020_of_x0020_visit"/><Value Type="DateTime"><Today OffsetDays="1"/></Value></Eq></Where>'
  7. $spQuery.Query = $camlQuery
  8. $spListItems = $MyList.GetItems($spQuery)
  9. Write-Host "Count: " $spListItems.Count
  11. if($spListItems.Count -gt 0)
  12. {
  14. $spListItems | foreach {
  15. $Name = $_["Host"].split('#')
  16. New-Object PSObject -Property @{
  17. "Host" = $Name[1]
  18. "Date of Visit"= $_["Date_x0020_of_x0020_visit"]
  19. "Name of Visitor" = $_["Name_x0020_of_x0020_vistor"]
  20. "Company" = $_["Company"]
  21. "Licence Plate Number" = $_["License_x0020_plate_x0020_number"]
  23. }
  24. } | Select-Object "Date of Visit", "Name of Visitor", "Company","Licence Plate Number", "Host" | Export-Csv -path 'D:\Reports\TodaysVisitors.csv' -NoTypeInformation
  25. }
Here is the script to send an email using PowerShell.
  1. #Define variables
  3. $fromaddress = "[email protected]"
  4. $toaddress = "[email protected]"
  5. $bccaddress = "[email protected]"
  6. $CCaddress = "[email protected]"
  7. $Subject = "Visitor's Data"
  9. $body ="Hello ,<br /> Please find attached reports of all Visitors who will be visiting tomorrow.<br />Do revert for any concerns. <br /> Note : This is system generated mail. <br /> In case of any further queries please contact [email protected] <br /><br /> Kind Regards, <br />SharePoint"
  11. $smtpserver = "SMPTServer"
  13. #Implementation Code
  15. $message = new-object System.Net.Mail.MailMessage
  16. $message.From = $fromaddress
  17. $message.To.Add($toaddress)
  18. $message.CC.Add($CCaddress)
  19. $message.Bcc.Add($bccaddress)
  20. $message.IsBodyHtml = $True
  21. $message.Subject = $Subject
  22. #your file location
  23. $files=Get-ChildItem “D:\Reports"
  25. Foreach($file in $files)
  26. {
  27. #Write-Host “Attaching File :- ” $file
  28. $attachment = New-Object System.Net.Mail.Attachment –ArgumentList D:\Reports\$file
  29. $message.Attachments.Add($attachment)
  31. }
  33. #$attach = new-object Net.Mail.Attachment($attachment)
  34. #$message.Attachments.Add($attach)
  35. $message.body = $body
  36. $smtp = new-object Net.Mail.SmtpClient($smtpserver)
  37. $smtp.Send($message)
  38. $attachment.Dispose();
  39. $message.Dispose();
The above PowerShell can be configured to Task Scheduler and here are the links for the same.
Schedule PowerShell Scripts Powershell Script with Arguments as a Scheduled Task
Here is the output in CSV file.

csv