In C# (I’m using Visual Studio 2008) create a class as indicated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Net.Mail;
using System.Net.Mime;

namespace icalEmail
{
    public class StoredProcedures
    {
        [SqlProcedure]
        public static void SendiCal(DateTime startDateTime, DateTime endDateTime, string emailSubject,
           string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string notes, string UID, string location, string seqNo, string smtpserver, int smtpport)
        {
            // create the iCal string in the proper format
            string iCal = CreateiCalFormat(startDateTime, endDateTime, emailSubject, userEmailAddress,
            cnAttendee, methodType, createdOn, UID, notes, location, seqNo);
            // create the MIME protocol Content-Type 
            var calendarType = new ContentType("text/calendar;method=REQUEST; charset=UTF-8");
            calendarType.Parameters.Add("method", "REQUEST");

            AlternateView caledarView = AlternateView.CreateAlternateViewFromString(iCal, calendarType);
            caledarView.TransferEncoding = TransferEncoding.SevenBit;
            // connect to smtpserver
            var client = new SmtpClient(smtpserver, smtpport);
            // create the email
            var mailMesage = new MailMessage();
            mailMesage.From = new MailAddress("donotreply@email.com");
            mailMesage.To.Add(new MailAddress(userEmailAddress));
            mailMesage.Subject = emailSubject;
            mailMesage.Body = iCal;
            mailMesage.AlternateViews.Add(caledarView);
            client.Send(mailMesage);
        }

        private static string _dateFormat = "yyyyMMddTHHmmssZ";
        private static string CreateiCalFormat(DateTime startDateTime, DateTime endDateTime,
        string emailSubject, string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string UID,
        string notes, string location, string seqNo)
        {
            string iCal =

            "BEGIN:VCALENDAR" +

            "\nPRODID:-//SampleApp//AoT//EN" +

            "\nVERSION:2.0" +
            /// REQUEST for add or update, CANCEL for cancellation
            "\nMETHOD:" + methodType +

            "\nBEGIN:VEVENT" +

            "\nORGANIZER:CN='Matt'" +

            //"\nORGANIZER:MAILTO:matt.balent@anvil-of-time.com" +

            "\nATTENDEE;CN='" + cnAttendee + "'" +

            ";ROLE=REQ-PARTICIPANT;RSVP=TRUE;CN=" + userEmailAddress + 

            "\nDTSTART:" + startDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nDTEND:" + endDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nSTATUS:CONFIRMED" +

            "\nTRANSP:OPAQUE" +
            /// 0 for add, 1 for update or cancellation
            "\nSEQUENCE:" + seqNo +

            "\nUID:" + Guid.NewGuid() +
            /// resend original UID for updates and cancellations
            //"\nUID:" + UID +

            "\nDTSTAMP:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLAST-MODIFIED:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLOCATION:" + location +

            "\nDESCRIPTION:" + notes +

            "\nSUMMARY:" + emailSubject +

            "\nPRIORITY:5" +

            "\nCLASS:PUBLIC" +

            "\nEND:VEVENT" +

            "\nEND:VCALENDAR";

            return iCal;
        }

    }
}

This would be compiled into a .dll which needs to be registered in SQL Server. This assembly is to be marked as UNSAFE so someone with sysadmin priviges needs to register it.

CREATE ASSEMBLY [eventEmail]
AUTHORIZATION [dbo]
FROM 'C:\temp\icalemail.dll'
WITH PERMISSION_SET = UNSAFE

There is an interesting discussion of ‘UNSAFE’ assemblies on Stackoverflow.

Now create the stored procedure which will call the assembly.

create procedure dbo.usp_mb_eventnotify(@startDateTime datetime
				, @endDateTime datetime
				, @emailSubject nvarchar(80)
				, @emailSummary nvarchar(1000)
				, @location nvarchar(255)
				, @attendeeName nvarchar(100)
				, @attendeeEmail nvarchar(255)
				, @organizerName nvarchar(100)
				, @organizerEmail nvarchar(255)
				, @UID nvarchar(255)
				, @status nvarchar(12)
				, @requestType nvarchar(12)
				, @smtpserver nvarchar (255)
				, @smtpport bigint)
as external name [iCalEmail].[iCalEmail.Storedprocedures].[SendiCal];

Examples of use:

Create the event

exec dbo.usp_mb_eventnotify  
'2012-08-08 13:00:00',
'2012-08-08 15:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Update the event

exec dbo.usp_mb_eventnotify				
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Cancel the event

exec dbo.usp_mb_eventnotify 
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CANCELLED',
'CANCEL',
'1234.na.corp.aot.com',
25

The script does the following:

1. Check if the email has an attachment
2. If the attachment is an ICalendar (.ICS) file, remove it to a common folder and update the email with a link to the detached file.
3. Loop through the ICS files in the designated folder and load them into an array.
4. If the last element in the array contains the value “PROCESSED”, skip it
5. Take the UID (Unique IDentifier) array element and turn it into an Outlook Event GUID.
6. Check if there are any events which have the UID Guid. If there are, this indicates the new file is an update to a previously processed event.
7. If there is no previous event with the UID Guid, add the event using the UID Guid as an identifier.
8. If there is a previous event and the new notice is a cancellation, delete the event.
9. If there is a previous event and the new notice is not a cancellation, update the event.
10. Append a line to the ICS file with the value “PROCESSED”.

Public Sub SaveICSAttachmentsMacro()
    Dim objOL As Outlook.Application
    Dim objitem As Outlook.MailItem 'Object
    Dim objAttachments As Outlook.Attachments
    Dim objSelection As Outlook.Selection
    Dim i As Long
    Dim lngCount As Long
    Dim strFile As String
    Dim strFolderpath As String
    Dim strDeletedFiles As String
    Dim strArray() As String 
    Dim ac As Long
    Dim lSeq As Long
    Dim lPriority As Long
    Dim strLine As String
    Dim strField As String
    Dim strValue As String
    Dim strMethod As String
    Dim strUid As String
    Dim strSummary As String
    Dim strLocation As String
    Dim strDesc As String
    Dim strOrganizer As String
    Dim strDate As String
    Dim dtStart As Date
    Dim dtEnd As Date
    
     Dim hexUID
     
    ' Instantiate an Outlook Application object.
    Set objOL = Application
     
     Dim objNs As Outlook.NameSpace
     Set objNs = objOL.Session
     Dim objFolder As Outlook.MAPIFolder
     
     Set objFolder = objNs.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar)
     Dim objAppts As Outlook.Items
     Set objAppts = objFolder.Items
     Dim objAppt As Outlook.AppointmentItem
     Dim strFilter As String
     
     
     
    ' Get the path to your My Documents folder
    strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
    On Error Resume Next


    ' Get the collection of selected objects.
    Set objSelection = objOL.ActiveExplorer.Selection
''''''''''''''''''''
'[snip] see code from Removing attachments post
'''''''''''''''''''
    ' call function in different module
    ' this populates the string array from the iCalendar file
    icsFileToArray strFile, strArray
    
    ' check last element of array to determine if file previously processed
    If strArray(UBound(strArray)) = "PROCESSED" Then
        'file already processed
        GoTo ExitSub
    Else
        ' loop through array to process event
        For ac = 1 To UBound(strArray)
            ' separate data pairs
            strLine = strArray(ac)
            If InStr(1, strLine, ":") > 0 Then
                strField = Mid(strLine, 1, InStr(1, strLine, ":") - 1)
                strValue = Mid(strLine, InStr(1, strLine, ":") + 1)
            End If
            
            Select Case strField
                Case "METHOD"
                 ' REQUEST or CANCEL
                    strMethod = strValue
                Case "UID"
                 ' Unique identifier for event
                    strUid = strValue
                Case "DTSTART"
                    ' pull out values into 'mm/dd/yyyy hh:mm:ss' format
                    strDate = (Mid(strValue, 5, 2) & "/" & Mid(strValue, 7, 2) & _
                    "/" & Mid(strValue, 1, 4) & " " & Mid(strValue, 10, 2) & ":" & _
                    Mid(strValue, 12, 2))
                    dtStart = CDate(strDate)
                    ' put datetime into Outlook format
                    dtStart = Format(dtStart, "\#m\/d\/yyyy h:mm:ss AM/PM\#")
                Case "DTEND"
                    ' pull out values into 'mm/dd/yyyy hh:mm:ss' format
                    strDate = (Mid(strValue, 5, 2) & "/" & Mid(strValue, 7, 2) & _
                    "/" & Mid(strValue, 1, 4) & " " & Mid(strValue, 10, 2) & ":" & _
                    Mid(strValue, 12, 2))
                    dtEnd = CDate(strDate)
                    dtEnd = Format(dtEnd, "\#m\/d\/yyyy h:mm:ss AM/PM\#")
                Case "SUMMARY"
                    strSummary = strValue
                Case "LOCATION"
                    strLocation = strValue
                Case "ORGANIZER"
                    ' can not set this as of 6-30-2011
                    strOrganizer = strValue
                Case "PRIORITY"
                    lPriority = Val(strValue)
                Case "SEQUENCE"
                 ' if an update must be > 0
                    lSeq = Val(strValue)
                Case "DESCRIPTION"
                    strDesc = strValue
                
                Case Else
                
            End Select
        Next
    End If
    ' check if appointment already exists for given UID
    'convert UID to hex. this should match the GlobalAppointmentID if the item was
    ' imported manually
    'Start with the standard preamble (original example had 26 and not 12 here)=======vv
    hexUID = "040000008200E00074C5B7101A82E0080000000000000000000000000000000000000000120000007643616C2D55696401000000"
    'Now add the provided string
    For i = 1 To Len(strUid)
        hexUID = hexUID & CStr(Hex(Asc(Mid(strUid, i, 1))))
    Next
    'Terminate the UID
    hexUID = hexUID & "00"
    ' this filter used if the new field is set to the GlobalAppointmentID
    strFilter = "[UID] = '" & hexUID & "'"
    Set objAppt = objAppts.Find(strFilter)
    If objAppt Is Nothing Then
        'New event
         Dim myItem As Outlook.AppointmentItem
         Set myItem = objOL.CreateItem(olAppointmentItem)
         myItem.Start = dtStart
         myItem.End = dtEnd
         myItem.Subject = strSummary
         myItem.Location = strLocation
         myItem.Body = strDesc
	 ' this was not working when I did this
         ' myItem.Organizer = strOrganizer
	 ' add a new property "UID" to the event so it can be updated
         myItem.ItemProperties.Add "UID", olText, True
         myItem.Save
         myItem.ItemProperties.Item("UID").Value = hexUID
         myItem.Save
    Else
        If strMethod = "CANCEL" Then
            ' remove item from calendar
            objAppt.Delete
        ElseIf lSeq > 0 Then
            ' update must have sequence > 0
            objAppt.Start = dtStart
            objAppt.End = dtEnd
            objAppt.Subject = strSummary
            objAppt.Location = strLocation
            objAppt.Body = strDesc
            objAppt.Save
        End If
        
    End If
    ' mark file processed
    icsWriteToFile strFile
    
ExitSub:

Set objAttachments = Nothing
Set objitem = Nothing
Set objSelection = Nothing
Set objOL = Nothing
End Sub

The biggest issue I came across in dealing with Outlook is the ability to update or delete a previously scheduled event. One would think Microsoft could handle the setting of an ID which could then be used to find it consistently. Outlook does assign an ID to events but these get changed if you move them around. The ‘GlobalAppointmentID’ does get set (as a GUID) and remains constant but you have no way of searching for it (sigh). Anyway I get around this by adding a property to any Event created via this process and assign the UID (which is converted into a GUID) to it. This way I can do a simple ‘Find’ of my apppointments and process appropriately. Here are some links related to GUIDs and Outlook

MSDN Outlook GUID stuff

Changing a string into an Outlook Event GUID See answer by banjaxed.

Note this process does not handle recurring appointments

Public Sub icsFileToArray(ByVal FileName As String, ByRef TheArray As Variant)
'
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFSTR As Scripting.TextStream
Dim lCtr As Long

Set oFSTR = oFSO.OpenTextFile(FileName, 1, 0) ' for reading

Do While Not oFSTR.AtEndOfStream
    ReDim Preserve TheArray(lCtr) As String
    TheArray(lCtr) = oFSTR.ReadLine
    lCtr = lCtr + 1
    DoEvents
Loop
oFSTR.Close
ErrorHandler:
Set oFSTR = Nothing
End Sub


Public Sub icsWriteToFile(ByVal FileName As String)
'
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFSTR As Scripting.TextStream
'Dim ret As Long
Dim lCtr As Long

Set oFSTR = oFSO.OpenTextFile(FileName, 8, 0) ' for appending
oFSTR.Write "PROCESSED"
oFSTR.Close
ErrorHandler:
Set oFSTR = Nothing
End Sub

The iCalendar format is designed to transmit calendar-based data and is meant “provide the definition of a common format for openly exchanging calendaring and scheduling information across the Internet”. It is supported by a number of products including Outlook, Lotus Notes, Google Calendar, Yahoo Calendar to name a few. See the article on Wikipedia for for additional information.

To schedule an event, create and send the following file:

BEGIN:VCALENDAR
VERSION:2.0
METHOD:REQUEST
PRODID:-//MyCompany//MyGreatSoftware//EN
BEGIN:VEVENT
UID:12345
CLASS:PUBLIC
DTSTAMP:20110624T145010
DTSTART:20110628T090000
DTEND:20110628T110000
SUMMARY:Follow Up with Big Customer
LOCATION:Office
ORGANIZER:CN="Smith, Agent" mailto:agent.smith@email.com
PRIORITY:5
SEQUENCE:0
END:VEVENT
END:VCALENDAR

In order to update or cancel a previously entered item you must know the UID and change the SEQUENCE segment. For a cancel, you change the METHOD from REQUEST to CANCEL.

Update previously sent event.

BEGIN:VCALENDAR
VERSION:2.0
METHOD:REQUEST
PRODID:-//MyCompany//MyGreatSoftware//EN
BEGIN:VEVENT
UID:12345
CLASS:PUBLIC
DTSTAMP:20110624T145010
DTSTART:20110628T093000
DTEND:20110628T113000
SUMMARY:Change to Followup with Big Customer
LOCATION:Office
ORGANIZER:CN="Smith, Agent" mailto:agent.smith@email.com
PRIORITY:5
SEQUENCE:1
END:VEVENT
END:VCALENDAR

To Cancel an event.

BEGIN:VCALENDAR
VERSION:2.0
METHOD:CANCEL
PRODID:-//MyCompany//MyGreatSoftware//EN
BEGIN:VEVENT
UID:12345
CLASS:PUBLIC
DTSTAMP:20110624T145010
DTSTART:20110628T093000
DTEND:20110628T113000
SUMMARY:Cancel Follow up with Big Customer
LOCATION:Office
ORGANIZER:CN="Smith, Agent" mailto:agent.smith@email.com
PRIORITY:5
SEQUENCE:1
END:VEVENT
END:VCALENDAR

Note the Organizer doesn’t have to be a valid email address. Outlook allows you to look at appointments in .ics format which can give you some further insight into the process (how to set reminders and such). Note that not all applications will recognize all atributes of the .ics file.