After digging around the web I created a script which utilizes the Exchange web service API, Powershell and a scheduled task. I borrowed heavily from this script http://gsexdev.blogspot.com/2009/08/twitter-your-exchange-calendar.html. After some exchange mailing list configurations, a little tweaking of the twitter script, and adding a function for email, I was able to come up with something that seems to work pretty well.
In addition to e-mailed reminders you can also share the CompanyReminder calendar to ALL of your employees and use it as a company calendar.
To enter a CompanyReminder event the user only has to do the following:
1. Create the appointment
2. Add CompanyReminder as the recipient
3. And save
Every morning around 8am the script runs and pulls all of the upcoming appointments for that day and sends it to everyone in an email.
Things you will need to configure in Exchange:
- Install the Exchange web service API from here http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c3342fb3-fbcc-4127-becf-872c746840e1
- You will need 1 exchange e-mail account, moving forward this account will be referred to as the CompanyReminder account.
- The sole purpose of this account is for receiving company reminders
- The CompanyReminder account is setup to automatically send a copy of every email to all employees and also to automatically accept all calendar invites.
- You will need a groupmailing list setup to email the intended employees (for our company it was a company wide email list), moving forward this group email list as the CompanyWide e-mail list.
$MailboxName = CompanyReminders@yourcompany.comScript Schedule Execution Steps:
function SendCompanyEmail($PostString){
$smtp = New-Object System.Net.Mail.SMTPClient -ArgumentList MAILSERVERIP $smtp.Send($MailboxName, 'CompanyWide@yourcompany.com','Upcoming Company Reminders', $PostString)
}
$dllpath = 'C:\Program Files\Microsoft\Exchange\Web Services\1.1\Microsoft.Exchange.WebServices.dll'
[reflection.assembly]::loadfile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$uri=[system.URI] ("https://dayman.cyber-balance.com/ews/exchange.asmx")
$service.Url = $uri
$service.Credentials = New-Object System.Net.NetworkCredential(CompanyReminder@yourcompany.com,"CompanyReminderPASSWORD","COMPANYDOMAIN")
$folderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName)
$CalendarFolder = [Microsoft.Exchange.WebServices.Data.CalendarFolder]::Bind($service,$folderid)
$cvCalendarview = new-object Microsoft.Exchange.WebServices.Data.CalendarView([System.DateTime]::Now, [System.DateTime]::Now.AddDays(14))
$cvCalendarview.MaxItemsReturned = 200;
$cvCalendarview.PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$frCalendarResult = $CalendarFolder.FindAppointments($cvCalendarview)
$appointmentString = ""
foreach ($apApointment in $frCalendarResult.Items){
if ($apApointment.Sensitivity -ne [Microsoft.Exchange.WebServices.Data.Sensitivity]::Private -band $apApointment.IsReminderSet -eq $true){
#$ReminderSendTime = $apApointment.Start.AddMinutes(-$apApointment.ReminderMinutesBeforeStart)
$ReminderSendTime = $apApointment.Start
if ($ReminderSendTime -ge [System.DateTime]::Now -band $ReminderSendTime -le [System.DateTime]::Now.AddMinutes(1500) -band !$apApointment.IsCancelled )
{
$appointmentString = $appointmentString + $apApointment.Organizer.Name + " " + $apApointment.Organizer.Address + "`n`n" + $apApointment.Subject.ToString() + "`n`nStarts : " + $apApointment.Start.ToString("ddd MM/dd/yyyy - HH:mm:ss") + "`n`Ends : " + $apApointment.End.ToString("ddd MM/dd/yyyy - HH:mm:ss") + "`n`n" + $apApointment.Location
if(!$apApointment.Body.Text -eq "")
{
$appointmentString = $appointmentString + "`n`n" + $apApointment.Body.Text
}
$appointmentString = $appointmentString + "`n`n---------------------------------------------------`n`n"
}
}
}
if(!$appointmentString -eq "")
{
SendCompanyEmail($appointmentString)
}
Take the above script and save is as a c:\Scripts\CompanyReminder.ps1 file (powershell).
Setup a windows scheduled task to run whenever you want the daily notification e-mail sent with the following command Powershell.exe c:\Scripts\CompanyReminder.ps1
And that's it!
Sample E-Mail:
---------------------------------------------------
Created By: CompanyReminders
Testing 8am CompanyReminder
Starts : Fri 04/08/2011 - 10:30:00
Ends : Fri 04/08/2011 - 11:00:00
Location: My Fun Location
---------------------------------------------------
References:
Twitter Exchange Calendary Reminder with powershell
http://gsexdev.blogspot.com/2009/08/twitter-your-exchange-calendar.html
http://msdn.microsoft.com/en-us/library/dd633710%28v=exchg.80%29.aspx