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!