create calendar event using external data

B

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
DTSTART:mad:FromDate
DTEND:mad:ThroughDate
SUMMARY:pTO
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
 
B

Brian Tillman

Bob said:
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
METHOD:pUBLISH 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top