Extract List Data To CSV File And Send An Email Through PowerShell

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 Names	Data 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

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$MyWeb = Get-SPWeb "http://WebApp/Web/"
$MyList = $MyWeb.Lists["ListName"]
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$camlQuery = '<Where><Eq><FieldRef Name="Date_x0020_of_x0020_visit"/><Value Type="DateTime"><Today OffsetDays="1"/></Value></Eq></Where>'
$spQuery.Query = $camlQuery
$spListItems = $MyList.GetItems($spQuery)
Write-Host "Count: " $spListItems.Count
if($spListItems.Count -gt 0)
{
$spListItems | foreach {
$Name = $_["Host"].split('#')
New-Object PSObject -Property @{
"Host" = $Name[1]
"Date of Visit"= $_["Date_x0020_of_x0020_visit"]
"Name of Visitor" = $_["Name_x0020_of_x0020_vistor"]
"Company" = $_["Company"]
"Licence Plate Number" = $_["License_x0020_plate_x0020_number"]
}
} | Select-Object "Date of Visit", "Name of Visitor", "Company","Licence Plate Number", "Host" | Export-Csv -path 'D:\Reports\TodaysVisitors.csv' -NoTypeInformation
}

Here is the script to send an email using PowerShell.

#Define variables
$fromaddress = "[email protected]"
$toaddress = "[email protected]"
$bccaddress = "[email protected]"
$CCaddress = "[email protected]"
$Subject = "Visitor's Data"
$body ="Hello , Please find attached reports of all Visitors who will be visiting tomorrow. Do revert for any concerns. Note : This is system generated mail. In case of any further queries please contact [email protected] Kind Regards, SharePoint"
$smtpserver = "SMPTServer"
#Implementation Code
$message = new-object System.Net.Mail.MailMessage
$message.From = $fromaddress
$message.To.Add($toaddress)
$message.CC.Add($CCaddress)
$message.Bcc.Add($bccaddress)
$message.IsBodyHtml = $True
$message.Subject = $Subject
#your file location
$files=Get-ChildItem “D:\Reports"
Foreach($file in $files)
{
#Write-Host “Attaching File :- ” $file
$attachment = New-Object System.Net.Mail.Attachment –ArgumentList D:\Reports\$file
$message.Attachments.Add($attachment)
}
#$attach = new-object Net.Mail.Attachment($attachment)
#$message.Attachments.Add($attach)
$message.body = $body
$smtp = new-object Net.Mail.SmtpClient($smtpserver)
$smtp.Send($message)
$attachment.Dispose();
$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.

3 Comments

Join the conversation! Your thoughts help the community grow.

Piyush Agarwal8y ago
This really helps... Sending emails using power shell can help in eliminating workflow issues alot.... Thanks for this
Sagar Pardeshi9y ago
Good blog..sachin ..
- Sachchin Annam9y ago
  Thanks @Sagar for encouraging me to post such articles on C# Corner.