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:
More information on sp_end_dbmail:
http://msdn.microsoft.com/en-us/library/ms190307.aspx
