Send Outlook Calendar Requests in Email

Just a few days ago, I received a request to build a procedure to send calendar item to the customer when there is an appointment has been booked.  

I did a quick search and basically I found two ways to send calendar items.

iCalendar

The first is to use sp_send_dbmail. Basic concept here is to generate an iCalendar item through SQL statement, and then send the item via attachment. Some useful links to help you understand iCalendar are listed below:

• The specification of iCalendar item is listed in rfc2445(http://tools.ietf.org/pdf/rfc2445.pdf).
• The minimum requirement to create an iCalendar item is recorded at page 59 section 4.1.1 of rfc2446 (http://tools.ietf.org/html/rfc2446#page-59).
• To find a full list of supported applications for iCalendar item, you can go to http://en.wikipedia.org/wiki/List_of_applications_with_iCalendar_support.

One thing confused me is about the property “PRODID”. In the rfc2445 and rfc2446 document it stats that PRODID is required, but in my test I can leave it without value, or even omit it. So let me know if you know the reason.

 

 

Below code is to demonstrate how we can send the iCalendar item through sp_send_dbmail.

 

 EXEC msdb.dbo.sp_send_dbmail    
  @recipients ='<your email address>'    
  ,@subject = 'iCalendar Example'    
  , @query = 'SET NOCOUNT ON;SELECT ''BEGIN:VCALENDAR''+ CHAR(13)+ ''VERSION:2.0''+ CHAR(13)+ ''METHOD:PUBLISH''+ CHAR(13)+ ''BEGIN:VEVENT''+ CHAR(13)+ ''CLASS:PUBLIC''+ CHAR(13)+ ''DESCRIPTION:This is the iCalendar example Most client application support it''+ CHAR(13)+ ''SUMMARY:iCalendar''+ CHAR(13)+ ''DTEND:20150325T110000''+ CHAR(13)+ ''DTSTART:20150325T120000''+ CHAR(13)+ ''LOCATION:My Office''+ CHAR(13)+ ''END:VEVENT''+ CHAR(13)+ ''END:VCALENDAR'''   
  , @attach_query_result_as_file = 1   
  , @query_result_header = 0   
  , @query_result_separator = 'CHAR(10)+CHAR(13)'   
  , @exclude_query_output = 1   
  , @query_attachment_filename = '123.ics'  

 

Exchange Web Service

Sending a calendar item to someone through Exchange Web Service is quite straightforward, just create a request for CalendarItemType, set CalendarItemType properties, then create it. Below is what I did:

Firstly, we need to add the Exchange Web Service, usually you can find it at https://webmail.<company>.com.au/ews/Services.wsdl

Now I create a helper method to retrieve the ExchangeServiceBinding object. It is a generic method I am using across many my Exchange Web Service projects.

 private ExchangeServiceBinding GetExchangeServiceBining(string username, string password, string domain, string url)  
     {  
       System.Net.NetworkCredential NC = new NetworkCredential(username, password, domain);  
       ExchangeServiceBinding ESB = new ExchangeServiceBinding();  
       ESB.Url = url;  
       ESB.Credentials = NC;  
       return ESB;  
     }  

 Finally in my main code:

 
       string UserName = "your ad name";  
       string Password = "your password";  
       string Domain = "your domain";  
       string EWS_URL = @"https://webmail.<company>.com.au/ews/Exchange.asmx";  
       ExchangeServiceBinding esb = GetExchangeServiceBining(UserName, Password, Domain, EWS_URL);  
       CalendarItemType appointment = new CalendarItemType();  
       appointment.Body = new BodyType();  
       appointment.Body.BodyType1 = BodyTypeType.Text;  
       appointment.Body.Value = "this is request body";  
       appointment.Subject = "Appointment created from esb";  
       appointment.Start = new DateTime(2015, 3, 24, 16, 30, 0);  
       appointment.StartSpecified = true;  
       appointment.End = new DateTime(2015, 3, 24, 17, 0, 0);  
       appointment.EndSpecified = true;  
       AttendeeType attendee = new AttendeeType();  
       attendee.Mailbox = new EmailAddressType();  
       attendee.Mailbox.EmailAddress = "attendee email address";  
       attendee.Mailbox.RoutingType = "SMTP";  
       AttendeeType attendee2 = new AttendeeType();  
       attendee2.Mailbox = new EmailAddressType();  
       attendee2.Mailbox.EmailAddress = "2nd recipient email address";  
       appointment.RequiredAttendees = new AttendeeType[] { attendee, attendee2 };  
       CreateItemType CreateRequest = new CreateItemType();  
       CreateRequest.SendMeetingInvitations = CalendarItemCreateOrDeleteOperationType.SendToAllAndSaveCopy;  
       CreateRequest.SendMeetingInvitationsSpecified = true;  
       CreateRequest.SavedItemFolderId = new TargetFolderIdType { Item = new DistinguishedFolderIdType { Id = DistinguishedFolderIdNameType.calendar } };  
       CreateRequest.Items = new NonEmptyArrayOfAllItemsType();  
       CreateRequest.Items.Items = new ItemType[] { appointment };  
       CreateItemResponseType response = esb.CreateItem(CreateRequest);  
       var responsemessage = response.ResponseMessages.Items[0];  

Quite simple, isn’t it?

I did some tests for both methods, and what I thought are

Because the iCalendar item is a standard, it can be accepted by most applications. The Exchange Web Service method, unfortunately needs some extra works to save the item as iCalendar attachment, then it can be recognized by other applications (if you know other hints, please let me know).

However within an MS Outlook and Exchange Service environment, Exchange Web Service is much more clear: the request will be added into Outlook Calendar directly (if attendee is required). So you do not need to open the iCalendar item and save it.

Let me know if you have any idea about this procedure :D