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

This blog articulates how to get list data in a CSV file and send that CSV as an attachment using 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 ,<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"

       $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.