create calendar event using external data

[Bob]

I am try to create a calendar event by using data from our HR application.
This code creates an email and an attachment but when it will not open but
gives a message indicating that a gregorian date is required vs a lunar date.
Is it a formatting issue?

We are using Outlook 2003


USE [SawyerMisc]
GO
/****** Object: StoredProcedure [dbo].[SendEmployeeReviewEmails] Script
Date: 07/17/2008 10:16:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[SendPTOToSupervisors]
as

declare @Today datetime
set @Today = convert(datetime,convert(varchar,getdate(),101))
declare @FromDate datetime
declare @ThroughDate datetime
declare @SupervisorUsername varchar(51)
declare @EmployeeName varchar(51)
declare @Notes varchar(255)
declare @Hours int
declare @AccrualType varchar(10)
declare @CCs varchar(500)
declare @emailBody varchar(4000)
declare @emailSubject varchar(250)

declare curPTOs cursor for
select

open curPTOs

fetch next from curPTOs into @FromDate, @ThroughDate, @SupervisorUsername,
@EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject


while @@fetch_status = 0
begin
set @CCs = ''
set @emailSubject = 'Paid Time Off'

-- get the PTO approvals

select SupervisorUsername,
AccrOption,
FromDate,
ThroughDate,
Hours,
Notes,
EEName
from dbo.PTORequests
where DateProcessed = null
set @FromDate = FromDate
@ThroughDate = ThroughDate
@SupervisorUsername = SupervisorUsername + '@srhllc.com'
@EmployeeName = EEName
@Notes = Notes
@Hours = Hours
@AccrualType = AccrOption
@emailBody = '@EEName' + ' has scheduled ' + '@AccrOption' + ' time from '
+ '@FromDate' + ' through ' + '@ThroughDate' +
' for a total of ' + '@Hours' + ' hours.'

--set @SupervisorUsername = '(e-mail address removed)' --send to me when deving
--set @emailBody = + @emailBody
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//hacksw/handcal//NONSGML v1.0//EN
BEGIN:VEVENT
DTSTARTFromDate
DTENDThroughDate
SUMMARYTO
END:VEVENT
END:VCALENDAR

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'UltiProMail',
@recipients = @SupervisorUsername,
@copy_recipients = @CCs,
@subject = @emailSubject,
@body = @emailBody,
@body_format = 'HTML'


fetch next from fetch next from curPTOs into @FromDate, @ThroughDate,
@SupervisorUsername, @EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject

-- need an update of the record somewhere in the code

end -- cursor loop

close curPTOs
deallocate curPTOs


Any assistance would be greatly appreciated

 

[Brian Tillman]

I am try to create a calendar event by using data from our HR
application. This code creates an email and an attachment but when it
will not open but gives a message indicating that a gregorian date is
required vs a lunar date. Is it a formatting issue?

We are using Outlook 2003

Make sure your dates are formatted in Gregorian format. Try adding the
METHODUBLISH command and make sure DTSTAMP is the correct format. DTSTAMP
is required in a VEVENT, according to RFC 2445 which defines the iCal
standard. http://tools.ietf.org/html/rfc2445