How to Send Automated Appointments from a LightSwitch Application
Last article I wrote about how you could automate Outlook to send appointments from a button on a screen in a LightSwitch application. If you missed it:
How To Create Outlook Appointments from a LightSwitch Application
That solution automates Outlook to create an appointment from entity data on a LightSwitch screen and allows the user to interact with the appointment. In this post I want to show you how you can automate the sending of appointments using the iCalendar standard format which many email clients can read, including Outlook. I’ll also show you how you can send updates to these appointments when appointment data in the LightSwitch application changes. We will use SMTP to create and send a meeting request as a business rule. This is similar to the first HTML email example I showed a couple weeks ago. Automated emails are sent from the server (middle-tier) when data is being inserted or updated against the data source. Let’s see how we can build this feature.
The Appointment Entity
Because we want to also send updated and cancelled meeting requests when appointment data is updated or deleted in the system, we need to add a couple additional properties to the Appointment entity to keep track of the messages we’re sending out. First we need a unique message ID which can be a GUID stored as a string. We also need to keep track of the sequence of any updates that are made to the appointment so that email clients can correlate them. We can simply increment a sequence number anytime we send out an updated appointment email. So here’s the schema of the Appointment entity (click to enlarge).
Notice that I also have relations to Customer and Employee in this example. We will be sending meeting requests for these two parties and we’ll make the Employee the organizer of the meeting and the Customer the attendee. In this entity I also am not showing the MsgID and MsgSequence properties on the screen. These will be used on code only. Now that we have our Appointment entity defined let’s add some business rules to set the values of these properties automatically. Drop down the “Write Code” button on the top-right of the Entity Designer and select Appointments_Inserting and Appointments_Updating. Write the following code to set these properties on the server-side before they are sent to the data store:
Public Class ApplicationDataService
Private Sub Appointments_Inserting(ByVal entity As Appointment)
'used to track any iCalender appointment requests
entity.MsgID = Guid.NewGuid.ToString()
entity.MsgSequence = 0
End Sub
Private Sub Appointments_Updating(ByVal entity As Appointment)
'Update the sequence anytime the appointment is updated
entity.MsgSequence += 1
End Sub
End Class
I also want to add a business rule on the StartTime and EndTime properties so that the start time is always before the end time. Select the StartTime property on the Entity and now when you drop down the “Write Code” button you will see StartTime_Validate at the top. Select that and write the following code:
Public Class Appointment
Private Sub StartTime_Validate(ByVal results As EntityValidationResultsBuilder)
If Me.StartTime >= Me.EndTime Then
results.AddPropertyError("Start time cannot be after end time.")
End If
End Sub
Private Sub EndTime_Validate(ByVal results As Microsoft.LightSwitch.EntityValidationResultsBuilder)
If Me.EndTime < Me.StartTime Then
results.AddPropertyError("End time cannot be before start time.")
End If
End Sub
End Class
Finally make sure you create a New Data Screen for this Appointment entity.
Creating the Email Appointment Helper Class
Now that we have our Appointment entity and New Data Screen to enter them we need to build a helper class that we can access on the server to send the automated appointment email. Just like before, we add the helper class to the Server project. Switch to File View on the Solution Explorer and add a class to the Server project:
I named the helper class SMTPMailHelper. The basic code to send an email is simple. You just need to specify the SMTP server, user id, password and port by modifying the constants at the top of the class. TIP: If you only know the user ID and password then you can try using Outlook 2010 to get the rest of the info for you automatically.
The trick to creating the meeting request is to create an iCalendar formatted attachment and add it as a text/calendar content type. And in fact, this code would work the same in any .NET application, there’s nothing specific to LightSwitch in here. I’m setting the basic properties of the meeting request but there are a lot of additional properties you can use depending on what kind of behavior you want. Take a look at the spec for more info (the iCalendar is an open spec and it’s here. There’s an abridged version that is a little easier to navigate here.)
Imports System.Net
Imports System.Net.Mail
Imports System.Text
Public Class SMTPMailHelper
Public Shared Function SendAppointment(ByVal sendFrom As String,
ByVal sendTo As String,
ByVal subject As String,
ByVal body As String,
ByVal location As String,
ByVal startTime As Date,
ByVal endTime As Date,
ByVal msgID As String,
ByVal sequence As Integer,
ByVal isCancelled As Boolean) As Boolean
Dim result = False
Try
If sendTo = "" OrElse sendFrom = "" Then
Throw New InvalidOperationException("sendTo and sendFrom email addresses must both be specified.")
End If
Dim fromAddress = New MailAddress(sendFrom)
Dim toAddress = New MailAddress(sendTo)
Dim mail As New MailMessage
With mail
.Subject = subject
.From = fromAddress
'Need to send to both parties to organize the meeting
.To.Add(toAddress)
.To.Add(fromAddress)
End With
'Use the text/calendar content type
Dim ct As New System.Net.Mime.ContentType("text/calendar")
ct.Parameters.Add("method", "REQUEST")
'Create the iCalendar format and add it to the mail
Dim cal = CreateICal(sendFrom, sendTo, subject, body, location,
startTime, endTime, msgID, sequence, isCancelled)
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(cal, ct))
'Send the meeting request
Dim smtp As New SmtpClient(SMTPServer, SMTPPort)
smtp.Credentials = New NetworkCredential(SMTPUserId, SMTPPassword)
smtp.Send(mail)
result = True
Catch ex As Exception
Throw New InvalidOperationException("Failed to send Appointment.", ex)
End Try
Return result
End Function
Private Shared Function CreateICal(ByVal sendFrom As String,
ByVal sendTo As String,
ByVal subject As String,
ByVal body As String,
ByVal location As String,
ByVal startTime As Date,
ByVal endTime As Date,
ByVal msgID As String,
ByVal sequence As Integer,
ByVal isCancelled As Boolean) As String
Dim sb As New StringBuilder()
If msgID = "" Then
msgID = Guid.NewGuid().ToString()
End If
'See iCalendar spec here: https://tools.ietf.org/html/rfc2445
'Abridged version here: https://www.kanzaki.com/docs/ical/
sb.AppendLine("BEGIN:VCALENDAR")
sb.AppendLine("PRODID:-//Northwind Traders Automated Email")
sb.AppendLine("VERSION:2.0")
If isCancelled Then
sb.AppendLine("METHOD:CANCEL")
Else
sb.AppendLine("METHOD:REQUEST")
End If
sb.AppendLine("BEGIN:VEVENT")
If isCancelled Then
sb.AppendLine("STATUS:CANCELLED")
sb.AppendLine("PRIORITY:1")
End If
sb.AppendLine(String.Format("ATTENDEE;RSVP=TRUE;ROLE=REQ-PARTICIPANT:MAILTO:{0}", sendTo))
sb.AppendLine(String.Format("ORGANIZER:MAILTO:{0}", sendFrom))
sb.AppendLine(String.Format("DTSTART:{0:yyyyMMddTHHmmssZ}", startTime.ToUniversalTime))
sb.AppendLine(String.Format("DTEND:{0:yyyyMMddTHHmmssZ}", endTime.ToUniversalTime))
sb.AppendLine(String.Format("LOCATION:{0}", location))
sb.AppendLine("TRANSP:OPAQUE")
'You need to increment the sequence anytime you update the meeting request.
sb.AppendLine(String.Format("SEQUENCE:{0}", sequence))
'This needs to be a unique ID. A GUID is created when the appointment entity is inserted
sb.AppendLine(String.Format("UID:{0}", msgID))
sb.AppendLine(String.Format("DTSTAMP:{0:yyyyMMddTHHmmssZ}", DateTime.UtcNow))
sb.AppendLine(String.Format("DESCRIPTION:{0}", body))
sb.AppendLine(String.Format("SUMMARY:{0}", subject))
sb.AppendLine("CLASS:PUBLIC")
'Create a 15min reminder
sb.AppendLine("BEGIN:VALARM")
sb.AppendLine("TRIGGER:-PT15M")
sb.AppendLine("ACTION:DISPLAY")
sb.AppendLine("DESCRIPTION:Reminder")
sb.AppendLine("END:VALARM")
sb.AppendLine("END:VEVENT")
sb.AppendLine("END:VCALENDAR")
Return sb.ToString()
End Function
End Class
Writing the Server-side Business Rules
Now that we have our helper class in the server project we can call it from the server-side business rules. Again, drop down the “Write Code” button on the top-right of the Entity Designer and now add Appointments_Inserted, Appointments_Updated and Appointments_Deleting methods to the ApplicationDataService. Call the SendAppointment method passing the Appointment entity properties. In the case of Appointment_Deleting then also pass the isCancelled flag in as True. So now the ApplicationDataService should look like this:
Public Class ApplicationDataService
Private Sub Appointments_Inserted(ByVal entity As Appointment)
Try
SMTPMailHelper.SendAppointment(entity.Employee.Email,
entity.Customer.Email,
entity.Subject,
entity.Notes,
entity.Location,
entity.StartTime,
entity.EndTime,
entity.MsgID,
entity.MsgSequence,
False)
Catch ex As Exception
System.Diagnostics.Trace.WriteLine(ex.ToString)
End Try
End Sub
Private Sub Appointments_Updated(ByVal entity As Appointment)
Try
SMTPMailHelper.SendAppointment(entity.Employee.Email,
entity.Customer.Email,
entity.Subject,
entity.Notes,
entity.Location,
entity.StartTime,
entity.EndTime,
entity.MsgID,
entity.MsgSequence,
False)
Catch ex As Exception
System.Diagnostics.Trace.WriteLine(ex.ToString)
End Try
End Sub
Private Sub Appointments_Deleting(ByVal entity As Appointment)
Try
SMTPMailHelper.SendAppointment(entity.Employee.Email,
entity.Customer.Email,
entity.Subject,
entity.Notes,
entity.Location,
entity.StartTime,
entity.EndTime,
entity.MsgID,
entity.MsgSequence,
True)
Catch ex As Exception
System.Diagnostics.Trace.WriteLine(ex.ToString)
End Try
End Sub
Private Sub Appointments_Inserting(ByVal entity As Appointment)
'used to track any iCalender appointment requests
entity.MsgID = Guid.NewGuid.ToString()
entity.MsgSequence = 0
End Sub
Private Sub Appointments_Updating(ByVal entity As Appointment)
'Update the sequence anytime the appointment is updated
entity.MsgSequence += 1
End Sub
End Class
Okay let’s run this and check if it works. First I added an employee and a customer with valid email addresses. I’m playing the employee so I added my Microsoft email address. Now when I create a new Appointment, fill out the screen, and click Save, I get an appointment in my inbox. Nice!
Now update the appointment in LightSwitch by changing the time, location, subject or notes. Hit save and this will send an update to the meeting participants.
Nice! This means that anytime we change the Appointment data in LightSwitch, an updated appointment will be sent via email automatically. Keep in mind though, that if users make changes to the appointment outside of LightSwitch then those changes will not be reflected in the database. Also I’m not allowing users to change the customer and employee on the Appointment after it’s created otherwise updates after that would not be sent to the original attendees. Instead when the Appointment is deleted then a cancellation goes out. So the idea is to create a new Appointment record if the meeting participants need to change.
I think I prefer this method to automating Outlook via COM like I showed in the previous post. You do lose the ability to let the user interact with the appointment before it is sent, but this code is much better at keeping the data and the meeting requests in sync and works with any email client that supports the iCalendar format.
Enjoy!
Comments
Anonymous
February 19, 2012
Beth, Thank you for all this great info, I have learned a lot from your blog. I have an office all using Exchange and patients will call into a secretary to make an appointment with 1 of 3 doctors. The secretary is publishing editor on the doctor's Outlook calendars. Is there a way to specify which calendar to add the appointment onto without an email waiting user interaction. Thank you, JimAnonymous
November 03, 2014
The comment has been removed