if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0)) 
{ 
Write-Progress -Activity "Loading Modules" -Status "Loading Microsoft.SharePoint.PowerShell"
Add-PSSnapin Microsoft.SharePoint.PowerShell 
} 
 
$ToRecipients = "joelplaut@MyDomain.com"
 
$CCRecipients = $ToRecipients; #set to any audience you want, semicolon separated
$ToRecipientsArray = $ToRecipients.Split(",");
$CCRecipientsArray = $CCRecipients.Split(",");
 
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration")
 
 
function Save-File([string] $Dir, [string] $PartialName, [system.Data.DataTable] $MyTable)
{
#writes file to the a temporary disk based location.  I add a timestamp to name to ensure uniqueness.  $PartialName is a text description used to start the filename
# export-csv does all the heavy lifting.  Note the attribute "-NoTypeInformation" is needed so there isn't a header on top of the field headers
$timestamp = Get-Date
$timestampstr = $timestamp.Month.ToString("00") + "_" + $timestamp.Day.ToString() + "_" + $timestamp.Year.ToString("0000") + " " + $timestamp.Hour.ToString("00") + "_" + $timestamp.Minute.ToString("00") + "_" + $timestamp.Second.ToString("00") 
 
$FileName = $Dir + '' + $PartialName+'_'+$timestampstr+'.CSV';
 
$MyTable.rows | Export-Csv -Path $FileName -NoTypeInformation
 
return $FileName;
 
}
 
#writes file to the Reporting Library within SharePoint, adds timestamp to name to ensure uniqueness
function Upload-File([string] $FilePath)
{
    $Files = $RepList.rootfolder.Files
    $DocLibName = $RepList.Title; #this is not necessarily accurate 
    #Get File Name from Path
    $FileName = $FilePath.Substring($FilePath.LastIndexOf("")+1)
 
    #delete the File from library, if already exist.  This should never occur
    try
    {
        if($Files.Item($DocLibName +"/" + $FileName))
        {
            $Files.delete($DocLibName +"/" + $FileName)
        }
    } catch {}
    #Get the File
    $File= Get-ChildItem $FilePath
 
    #Add File to the collection
    $x=$Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$false)
 
     $ReportFilename = $RepList.ParentWeb.Url + '/'+ $DocLibName + '/' + $Filename;
     return $reportFileName;
 }
 
 
# If we are running in a Claims Authentication environment, we can strip off the claims tags
Function Strip-ClaimsHeader ($s)
{
    if ($s.IndexOf('#') -gt 0)  #handle stripping claims tags off name
    {
            return $s.Substring($s.IndexOf("#")+1)
 
    }
    else
    {
    return $s
    }
}
 
#used for HTML processing and fixup; this is a bit dense, but it supports the PowerShell pipeline, and restores XML tags
Function Convert-HTMLEscape {
 
<#
convert < and > to < and >
It is assumed that these will be in pairs
#>
 
[cmdletbinding()]
 
Param (
[Parameter(Position=0,ValueFromPipeline=$True)]
[string[]]$Text
)
 
Process {
foreach ($item in $text) {
    if ($item -match "<") {
        <#
          replace codes with actual symbols. This line is a shortcut to do two replacements
          with one line of code. The code in the first set of parentheses revised text with "<". This normally gets written to the pipeline. By wrapping it in parentheses it tells PowerShell to treat it as an object so I can then call the Replace() method again and add the >.
        #>
        (($item.Replace("<","<")).Replace(">",">")).Replace(""",'"')
     }
     else {
        #otherwise just write the line to the pipeline
        $item
     }
 }
} #close process
 
} #close function
 
 $env = "Prod"  # I like setting flags for changing environment settings
  
 if ($env -eq "Prod")
 {
    $RefListLocation = "http ://SharePoint/sites/mySiteCollection/Subsite"
    $TempLoc = "C:TempLocation"  # disk location to temporararily store the file
}
 
 
$table = New-Object system.Data.DataTable “JoelFancyReport”  #this is an internal name, that is not very important to set
 
#let's define a field (column) for every possible field; even those we don't yet use in a given report
$col1 = New-Object system.Data.DataColumn Link,([string])
$col2 = New-Object system.Data.DataColumn BusinessProcess,([string])
$col3 = New-Object system.Data.DataColumn DocType,([string])
$col4 = New-Object system.Data.DataColumn Modified,([string])
$col5 = New-Object system.Data.DataColumn Title,([string])
$col6 = New-Object system.Data.DataColumn FileName,([string])
$col7 = New-Object system.Data.DataColumn Editor,([string])
$col8 = New-Object system.Data.DataColumn CheckedOut,([string])
$col9 = New-Object system.Data.DataColumn CheckedOutBy,([string])
$col10= New-Object system.Data.DataColumn FileFolder,([string])
$col11= New-Object system.Data.DataColumn FileLink,([string])
$col12= New-Object system.Data.DataColumn ReportAction,([string])
 
# Just add the columns to the table
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)
$table.columns.add($col8)
$table.columns.add($col9)
$table.columns.add($col10)
$table.columns.add($col11)
$table.columns.add($col12)
 
#we can create multiple tables with the same layout easily. 
# Copy is shown here for reference, and is not used in this simple example
$TableCopy = $table.Copy() 
 
#loop through whatever SPItems need to be looped
# here is where the basic logic gets put, before generating the actual report
# this is just a placeholder loop of items in a list, but this could be anything, including SPQuery results
 
$JPWeb = get-spweb $RefListLocation
$JPList  = $JPWeb.lists["MyList"]
$JPItems = $JPList.items;
$JPItemCount = $JPItems.count;
 
for ($k=0; $k -lt $JPItemsCount; $k++)
{
    $SourceItem = JPItems[$k];
 
                if ($SourceItem.File.CheckOutStatus -ne "None")
                {
                    $ReportAction += ", CheckedOut "+$SourceItem.File.CheckOutStatus+" to:"+$SourceItem.File.CheckedOutBy.displayname;
                    $CheckedOut = "Yes"
                    $CheckedOutBy = $SourceItem.File.CheckedOutBy.displayname;
                }
                else
                {
                    $CheckedOut = "No"
                    $CheckedOutBy = $null;
                }
                    #let's create a new row 
                    $row = $Table.NewRow()
 
                    $FileLink =   $($JPWeb.Url)+'/'+$($SourceItem.url) ;
                    $row.FileLink = $FileLink;
                    $row.Link = "<a href="+'">'+ " Link " + "</a>";
 
                    $FolderURL = $FileLink.substring(0,$FileLink.LastIndexOf('/'));
 
                    $row.FileFolder = $FolderURL;
 
                    $row.Title = $item2.Title;
 
                    $row.FileName= $item2.Name;
 
                    $row.Editor = strip-claimsHeader $item2["Editor"];
 
                    $row.Modified= $item2["Modified"];
 
                    $row.BusinessProcess = $item2["BusinessProcess"] #sample field; add as many as you'd like
                    $row.DocType = $item2["DocType"]
 
                    $row.checkedOut = $CheckedOut;
                    $row.checkedOutBy = $CheckedOutBy;
 
                    $Table.Rows.Add($row)
}
 
 
        $RunStatus = "All the processing completed $(get-date)"
        $Header = "Weekly report for my friends"
        $emailTitle = "Weekly Report email $($RunSummary)"
 
 
# this is a nice table style, collapsed, colored header.    
 # Let's creat the HTML table, specifying only the columns from the table that we'd like to see.  Any other columns won't appear in this report 
# By piping it to the Convert-HTMLEscape function, it fixes up the HTML  

 $MyOutput = $table| ConvertTo-Html Link, BusinessProcess, DocType, Editor, Modified, FileName, Action -head $a -body "
$($rows.count) $($Header)| Convert-HTMLEscape
   # if we need to reuse table, just make a copy of it first.
   # While Convert-HTML offered the luxury of column selection, the Convert-CSV converts every darn column, so let's remove the extra ones 
    now.
        $table.Columns.Remove("Link") #how to remove a Column. links don't look too pretty within a CSV, so for an example I remove it 
         here.
 
$f2 = save-file $TempLoc ($FileDescription) $Table  #Saves the temp file to disk, driven out of the $Table
        $Report2URL = upload-file $f2
 
        $ReportSummary = "Rolled Report Available at: "+"<a href="+'">'+ $FileDescription+" Custom Report " + "</a>"+"";
  #debug technique; it's easy to route the HTML to a file for examination, see the commented out line below:
    #$MyOutput &gt; C:A.html 
[string] $From = "SharePoint Support@MyDomain.com"
[string] $To = $null; #$Recipients #"joelplaut@MyDomain.com"
[string] $Title = $emailTitle;
$SmtpClient = New-Object System.Net.Mail.SmtpClient
$SmtpServer = "mail.MyDomain.com"
$SmtpClient.host = $SmtpServer
$MailMessage = New-Object system.net.mail.mailmessage
 
$mailmessage.from = $From;
foreach ($recip in $ToRecipientsArray)
{
$mailmessage.To.add($recip)
}
foreach ($recip in $CCRecipientsArray)
{
$mailmessage.CC.add($recip)
}
$mailmessage.Subject = $Title
$mailmessage.Body = $myoutput #"Body"
$MailMessage.set_IsBodyHtml($true)
$smtpclient.Send($mailmessage)

if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0)) { Write-Progress -Activity "Loading Modules" -Status "Loading Microsoft.SharePoint.PowerShell" Add-PSSnapin Microsoft.SharePoint.PowerShell } $ToRecipients = "joelplaut@MyDomain.com" $CCRecipients = $ToRecipients; #set to any audience you want, semicolon separated $ToRecipientsArray = $ToRecipients.Split(","); $CCRecipientsArray = $CCRecipients.Split(","); [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration") function Save-File([string] $Dir, [string] $PartialName, [system.Data.DataTable] $MyTable) { #writes file to the a temporary disk based location. I add a timestamp to name to ensure uniqueness. $PartialName is a text description used to start the filename # export-csv does all the heavy lifting. Note the attribute "-NoTypeInformation" is needed so there isn't a header on top of the field headers $timestamp = Get-Date $timestampstr = $timestamp.Month.ToString("00") + "_" + $timestamp.Day.ToString() + "_" + $timestamp.Year.ToString("0000") + " " + $timestamp.Hour.ToString("00") + "_" + $timestamp.Minute.ToString("00") + "_" + $timestamp.Second.ToString("00") $FileName = $Dir + '' + $PartialName+'_'+$timestampstr+'.CSV'; $MyTable.rows | Export-Csv -Path $FileName -NoTypeInformation return $FileName; } #writes file to the Reporting Library within SharePoint, adds timestamp to name to ensure uniqueness function Upload-File([string] $FilePath) { $Files = $RepList.rootfolder.Files $DocLibName = $RepList.Title; #this is not necessarily accurate #Get File Name from Path $FileName = $FilePath.Substring($FilePath.LastIndexOf("")+1) #delete the File from library, if already exist. This should never occur try { if($Files.Item($DocLibName +"/" + $FileName)) { $Files.delete($DocLibName +"/" + $FileName) } } catch {} #Get the File $File= Get-ChildItem $FilePath #Add File to the collection $x=$Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$false) $ReportFilename = $RepList.ParentWeb.Url + '/'+ $DocLibName + '/' + $Filename; return $reportFileName; } # If we are running in a Claims Authentication environment, we can strip off the claims tags Function Strip-ClaimsHeader ($s) { if ($s.IndexOf('#') -gt 0) #handle stripping claims tags off name { return $s.Substring($s.IndexOf("#")+1) } else { return $s } } #used for HTML processing and fixup; this is a bit dense, but it supports the PowerShell pipeline, and restores XML tags Function Convert-HTMLEscape { &lt;# convert &amp;lt; and &amp;gt; to &lt; and &gt; It is assumed that these will be in pairs #&gt; [cmdletbinding()] Param ( [Parameter(Position=0,ValueFromPipeline=$True)] [string[]]$Text ) Process { foreach ($item in $text) { if ($item -match "&amp;lt;") { &lt;# replace codes with actual symbols. This line is a shortcut to do two replacements with one line of code. The code in the first set of parentheses revised text with "&lt;". This normally gets written to the pipeline. By wrapping it in parentheses it tells PowerShell to treat it as an object so I can then call the Replace() method again and add the &gt;. #&gt; (($item.Replace("&amp;lt;","&lt;")).Replace("&amp;gt;","&gt;")).Replace("&amp;quot;",'"') } else { #otherwise just write the line to the pipeline $item } } } #close process } #close function $env = "Prod" # I like setting flags for changing environment settings if ($env -eq "Prod") { $RefListLocation = "http ://SharePoint/sites/mySiteCollection/Subsite" $TempLoc = "C:TempLocation" # disk location to temporararily store the file } $table = New-Object system.Data.DataTable “JoelFancyReport” #this is an internal name, that is not very important to set #let's define a field (column) for every possible field; even those we don't yet use in a given report $col1 = New-Object system.Data.DataColumn Link,([string]) $col2 = New-Object system.Data.DataColumn BusinessProcess,([string]) $col3 = New-Object system.Data.DataColumn DocType,([string]) $col4 = New-Object system.Data.DataColumn Modified,([string]) $col5 = New-Object system.Data.DataColumn Title,([string]) $col6 = New-Object system.Data.DataColumn FileName,([string]) $col7 = New-Object system.Data.DataColumn Editor,([string]) $col8 = New-Object system.Data.DataColumn CheckedOut,([string]) $col9 = New-Object system.Data.DataColumn CheckedOutBy,([string]) $col10= New-Object system.Data.DataColumn FileFolder,([string]) $col11= New-Object system.Data.DataColumn FileLink,([string]) $col12= New-Object system.Data.DataColumn ReportAction,([string]) # Just add the columns to the table $table.columns.add($col1) $table.columns.add($col2) $table.columns.add($col3) $table.columns.add($col4) $table.columns.add($col5) $table.columns.add($col6) $table.columns.add($col7) $table.columns.add($col8) $table.columns.add($col9) $table.columns.add($col10) $table.columns.add($col11) $table.columns.add($col12) #we can create multiple tables with the same layout easily. # Copy is shown here for reference, and is not used in this simple example $TableCopy = $table.Copy() #loop through whatever SPItems need to be looped # here is where the basic logic gets put, before generating the actual report # this is just a placeholder loop of items in a list, but this could be anything, including SPQuery results $JPWeb = get-spweb $RefListLocation $JPList = $JPWeb.lists["MyList"] $JPItems = $JPList.items; $JPItemCount = $JPItems.count; for ($k=0; $k -lt $JPItemsCount; $k++) { $SourceItem = JPItems[$k]; if ($SourceItem.File.CheckOutStatus -ne "None") { $ReportAction += ", CheckedOut "+$SourceItem.File.CheckOutStatus+" to:"+$SourceItem.File.CheckedOutBy.displayname; $CheckedOut = "Yes" $CheckedOutBy = $SourceItem.File.CheckedOutBy.displayname; } else { $CheckedOut = "No" $CheckedOutBy = $null; } #let's create a new row $row = $Table.NewRow() $FileLink = $($JPWeb.Url)+'/'+$($SourceItem.url) ; $row.FileLink = $FileLink; $row.Link = "<a href="+'">'+ " Link " + "</a>"; $FolderURL = $FileLink.substring(0,$FileLink.LastIndexOf('/')); $row.FileFolder = $FolderURL; $row.Title = $item2.Title; $row.FileName= $item2.Name; $row.Editor = strip-claimsHeader $item2["Editor"]; $row.Modified= $item2["Modified"]; $row.BusinessProcess = $item2["BusinessProcess"] #sample field; add as many as you'd like $row.DocType = $item2["DocType"] $row.checkedOut = $CheckedOut; $row.checkedOutBy = $CheckedOutBy; $Table.Rows.Add($row) } $RunStatus = "All the processing completed $(get-date)" $Header = "Weekly report for my friends" $emailTitle = "Weekly Report email $($RunSummary)" # this is a nice table style, collapsed, colored header. # Let's creat the HTML table, specifying only the columns from the table that we'd like to see. Any other columns won't appear in this report # By piping it to the Convert-HTMLEscape function, it fixes up the HTML $MyOutput = $table| ConvertTo-Html Link, BusinessProcess, DocType, Editor, Modified, FileName, Action -head $a -body " $($rows.count) $($Header)| Convert-HTMLEscape # if we need to reuse table, just make a copy of it first. # While Convert-HTML offered the luxury of column selection, the Convert-CSV converts every darn column, so let's remove the extra ones now. $table.Columns.Remove("Link") #how to remove a Column. links don't look too pretty within a CSV, so for an example I remove it here. $f2 = save-file $TempLoc ($FileDescription) $Table #Saves the temp file to disk, driven out of the $Table $Report2URL = upload-file $f2 $ReportSummary = "Rolled Report Available at: "+"<a href="+'">'+ $FileDescription+" Custom Report " + "</a>"+""; #debug technique; it's easy to route the HTML to a file for examination, see the commented out line below: #$MyOutput &gt; C:A.html [string] $From = "SharePoint Support@MyDomain.com" [string] $To = $null; #$Recipients #"joelplaut@MyDomain.com" [string] $Title = $emailTitle; $SmtpClient = New-Object System.Net.Mail.SmtpClient $SmtpServer = "mail.MyDomain.com" $SmtpClient.host = $SmtpServer $MailMessage = New-Object system.net.mail.mailmessage $mailmessage.from = $From; foreach ($recip in $ToRecipientsArray) { $mailmessage.To.add($recip) } foreach ($recip in $CCRecipientsArray) { $mailmessage.CC.add($recip) } $mailmessage.Subject = $Title $mailmessage.Body = $myoutput #"Body" $MailMessage.set_IsBodyHtml($true) $smtpclient.Send($mailmessage)