As I continue to work with SharePoint, I continue to find that PowerShell is handy tool. The more I use PowerShell the more powerful I find it to be in general.
In this scenario, I was tasked to send a daily email of the events of the day on a given calendar. I realize that this seems counter productive, as the users could easily just look at SharePoint, however I don’t call the shots. So, I took my task to heart and after exhausting my ideas someone suggested PowerShell. DING! Why hadn’t I thought of that? I guess I still wasn’t convinced of the power of the shell (bad joke, I’m full of them). The plan became, create a script that will get the days events and send an email to the desired users. The only other piece left would be how to make it run and the plan there was to use Task Schedule that’s built into Windows. I’m not going to cover that piece here but you’ll need to take into consideration any authentication you might need to do with your mail server relating to the user your sending the email as.
If you read my previous post you’ll see that the top of the script does the same things. Load SharePoint assemblies, connect to SharePoint and get the calendar needed (which is just another form of a list). However, this time I only need specific data and since it’s a calendar I want to filter on date. In this instance I tried several things, but eventually settled on using SPQuery. You’ll see after it’s instantiated, that it get assigned a query. The query tells us to get all event dates that are equal to today. If you open up SharePoint and look at the calendar you’ll see that the different views of the calendar match what can be placed in this script. So if you change it to month, then it would give you all events for the month. Then we need to set the calendar date to today, expand all recurring events, and order them. Finally we can pass the query to the GetItems method and it will give us the items as we’ve requested.
Here’s the caml query as it didn’t want to show up because it was confusing it with HTML:
Now all that’s left to do is loop through the items and build a string for the body of the email and then send the email. Enjoy!
#some temp variables if we want to update other lists later
$SharePointSite = "http://sharepoint/"
$SharePointWeb = "List"
$SharePointList = "Calendar"
#make the connection to sharepoint and to the list
$spSite = new-object Microsoft.SharePoint.SPSite($SharePointSite)
$spWeb = $spSite.OpenWeb($SharePointWeb)
$spList = $spWeb.Lists[$SharePointList]
$query = New-Object Microsoft.SharePoint.SPQuery
$query.query = ""
$query.CalendarDate = Get-Date
$query.RecurrenceOrderBy = $true
$query.ExpandRecurrence = $true
$spItems = $spList.GetItems($query)
$emailBody = "Start Email Body"
#loop through all of the lists and update each one from the file we generated from SQL
foreach ($spItem in $spItems)
#write-host $spItem["Title"] " - " $spItem["Start Time"] " - " $spItem["End Time"]
$emailBody += $spItem["Title"] + ""
$emailFrom = "email@example.com"
$emailTo = "firstname.lastname@example.org"
$emailCc = "email@example.com"
$emailSubject = "My Email"
send-mailmessage -from $emailFrom -to $emailTo -cc $emailCc -subject $emailSubject -body $emailBody -smtpServer mail.domain.com -BodyAsHtml:$true
Helpful reference when I was getting started with PowerShell.