Sending an Outlook Calendar meeting event via sp_send_dbmail

by: DATABASE LADY

All the user has to do was to open the ICS attachment, and to click Save&Close to add it to his calender. Prerequisites : A functioning Database mail configuration in your database. The main part of the procedure is the sp_send_dbmail proc that sends the mail to the use:

EXEC msdb.dbo.sp_send_dbmail

     @recipients ='DatabaseLady@HelloWorld.com'

    ,@profile_name = 'ProfileName'

    ,@subject = 'A Friendly reminder'

    , @query = '

--

SET NOCOUNT ON;

--

SELECT ''BEGIN:VCALENDAR''

+ CHAR(13)

+ ''PRODID:-//My Company//Company Calendar//EN''

+ CHAR(13)

+ ''VERSION:2.0''

+ CHAR(13)

+ ''METHOD:PUBLISH''

+ CHAR(13)

+ ''BEGIN:VEVENT''

+ CHAR(13)

+ ''CLASS:PUBLIC''

+ CHAR(13)

+ ''DESCRIPTION:Please make a reminder''

+ CHAR(13)

+ ''SUMMARY:Please make a reminder''

+ CHAR(13)

+ ''DTEND:20140930T190000''

+ CHAR(13)

+ ''DTSTART:20140930T190000''

+ CHAR(13)

+ ''LOCATION:''

+ 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 = 'DividendEvent.ics'




The interesting part is the @query parameter.
In this part, you create an iCalendar file that Outlook support and recognize.

BEGIN:VCALENDAR
PRODID:-//My Company//Company Calendar//EN
VERSION:2.0
METHOD:PUBLISH                                          Post notification of an event.
BEGIN:VEVENT
CLASS:PUBLIC
DESCRIPTION:Please make a reminder       The event description.
SUMMARY:Please make a reminder              The event title.
DTEND:20140930T190000                             Event end time, in ISO 8601 format
DTSTART:20140930T190000                    Event start time, in ISO 8601 format
LOCATION:                                                          The venue name.
END:VEVENT
END:VCALENDAR

More information on iCalendar format:

• http://en.wikipedia.org/wiki/ICalendar
• http://tools.ietf.org/html/rfc2446#page-14

More information on sp_end_dbmail:
http://msdn.microsoft.com/en-us/library/ms190307.aspx