isol8
asked on
Query Exchange Calender with ASP Page
I want to query a single user's Exchange calendar and return the appointments for the next week or so. Or possibly display the next five events in the calendar from today.
I've looked at CDO and ADO and EXOLEDB and WebDAV....etc. But I can't seem to get a solution to work.
I can get this query using ADO to work http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/_clb_viewing_the_calendar_using_ado_vbs.asp
I'll be running the queries on our Exchange server as it is also our IIS server. We use SSL as well.
Is there an easy way to do this?
I've looked at CDO and ADO and EXOLEDB and WebDAV....etc. But I can't seem to get a solution to work.
I can get this query using ADO to work http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/_clb_viewing_the_calendar_using_ado_vbs.asp
I'll be running the queries on our Exchange server as it is also our IIS server. We use SSL as well.
Is there an easy way to do this?
https://www.experts-exchange.com/questions/20590551/Accessing-Calendar-folder-in-Exchange-server.html?query=Exchange+calendar&topics=97
https://www.experts-exchange.com/questions/20387184/Query-Exchange-Calendar-Address-Book.html?query=Exchange+calendar&topics=97
https://www.experts-exchange.com/questions/21593531/View-exchange-calendar-in-an-ASP-page.html?query=Exchange+calendar&topics=97
https://www.experts-exchange.com/questions/20387184/Query-Exchange-Calendar-Address-Book.html?query=Exchange+calendar&topics=97
https://www.experts-exchange.com/questions/21593531/View-exchange-calendar-in-an-ASP-page.html?query=Exchange+calendar&topics=97
ASKER
I've read through those, but they don't really show me any of the necessary code.
Here's code:
http://support.microsoft.com/?kbid=181483
http://support.microsoft.com/?kbid=181483
ASKER
That code is for Exchange so I'm not sure if it is meant to work on Exchange 2003.
It gives me this error:
[Collaboration Data Objects - [MAPI_E_NOT_FOUND(8004010F )]]
Also, that code will show all events in the calendar, not just the next few.
It gives me this error:
[Collaboration Data Objects - [MAPI_E_NOT_FOUND(8004010F
Also, that code will show all events in the calendar, not just the next few.
I'm just providing you the tools...I can't write this all for you.
ASKER
No, I understand. I appreciate your help. Its just that I've gone through all of the Microsoft links and searched everywhere already.
It doesn't seem like it should be too difficult, but I can't find any samples that work.
It doesn't seem like it should be too difficult, but I can't find any samples that work.
ASKER
I've made some progress using the following code:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------
<% @LANGUAGE = "VBScript" %>
<html>
<body>
<% servername = Request.ServerVariables("S ERVER_NAME ")
username = Request.ServerVariables("R EMOTE_USER ")
p = Instr(username, "\")
If p <> 0 Then username = Right(username, Len(username) - p)
calURL = "https://<ourserver>/Exchange/" & username & "/Calendar/"
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL
subject = Request.QueryString("subje ct")
If subject = "" Then
Response.Write "Upcoming Events:<p>"
strSQL = "SELECT ""urn:schemas:httpmail:sub ject"", ""DAV:displayname""" & _
" FROM SCOPE('shallow traversal of """ & calURL & """')" & _
" ORDER BY ""urn:schemas:httpmail:dat e"" DESC"
Set rs = conn.Execute(strSQL)
n = 1
Do While (Not rs.EOF) And (n <= 10)
%><a href='ado.asp?subject=<% = Server.URLEncode(rs("DAV:d isplayname ")) %>'>
<% = Server.HTMLEncode(rs("urn: schemas:ht tpmail:sub ject")) %>
</a><br>
<% n = n + 1
rs.MoveNext
Loop
Response.Write "<p>User: " & username
Set rs = Nothing
Else
Set rec = Server.CreateObject("ADODB .Record")
rec.Open calURL & subject, conn
%><form>
<b>From:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:http mail:sende rname")) %><br>
<b>Subject:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:http mail:subje ct")) %><br>
<input type='submit' value='OK'><br>
<b>Message:</b><br>
<% = Replace(Server.HTMLEncode( rec.Fields ("urn:sche mas:httpma il:textdes cription") ), vbCrLf, "<br>") %>
<% rec.Close : Set rec = Nothing
%><p>
<input type='submit' value='OK'>
</form>
<% End If
conn.Close : Set conn = Nothing
%></body>
</html>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
This was taken from http://www.msexchange.org/articles/Put_Your_Exchange_2000_Mailbox_On_The_Web_With_ADO.html
It sort of does what I want it to do. It was made to read the inbox though. I'd like it to show the start and endtime of the appt. And only show the next 5 or so appointments. Or possibly the all appointments for the next week.
I think I just need to change the DAV queries.
Anyone have any ideas on that?
--------------------------
<% @LANGUAGE = "VBScript" %>
<html>
<body>
<% servername = Request.ServerVariables("S
username = Request.ServerVariables("R
p = Instr(username, "\")
If p <> 0 Then username = Right(username, Len(username) - p)
calURL = "https://<ourserver>/Exchange/" & username & "/Calendar/"
Set conn = Server.CreateObject("ADODB
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL
subject = Request.QueryString("subje
If subject = "" Then
Response.Write "Upcoming Events:<p>"
strSQL = "SELECT ""urn:schemas:httpmail:sub
" FROM SCOPE('shallow traversal of """ & calURL & """')" & _
" ORDER BY ""urn:schemas:httpmail:dat
Set rs = conn.Execute(strSQL)
n = 1
Do While (Not rs.EOF) And (n <= 10)
%><a href='ado.asp?subject=<% = Server.URLEncode(rs("DAV:d
<% = Server.HTMLEncode(rs("urn:
</a><br>
<% n = n + 1
rs.MoveNext
Loop
Response.Write "<p>User: " & username
Set rs = Nothing
Else
Set rec = Server.CreateObject("ADODB
rec.Open calURL & subject, conn
%><form>
<b>From:</b> <% = Server.HTMLEncode(rec.Fiel
<b>Subject:</b> <% = Server.HTMLEncode(rec.Fiel
<input type='submit' value='OK'><br>
<b>Message:</b><br>
<% = Replace(Server.HTMLEncode(
<% rec.Close : Set rec = Nothing
%><p>
<input type='submit' value='OK'>
</form>
<% End If
conn.Close : Set conn = Nothing
%></body>
</html>
--------------------------
This was taken from http://www.msexchange.org/articles/Put_Your_Exchange_2000_Mailbox_On_The_Web_With_ADO.html
It sort of does what I want it to do. It was made to read the inbox though. I'd like it to show the start and endtime of the appt. And only show the next 5 or so appointments. Or possibly the all appointments for the next week.
I think I just need to change the DAV queries.
Anyone have any ideas on that?
ASKER
I also just noticed that this query does not show Recurring Appointments
ASKER
Okay I've updated my code, but still have a few questions...
Now I can see all appointment types, but I'm still not sure how to limit it to just this week's appointments.
Here is the code:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
<% @LANGUAGE = "VBScript" %>
<html>
<body>
<% servername = Request.ServerVariables("S ERVER_NAME ")
username = Request.ServerVariables("R EMOTE_USER ")
p = Instr(username, "\")
If p <> 0 Then username = Right(username, Len(username) - p)
set shell = createobject("wscript.shel l")
strValueName = "HKLM\SYSTEM\CurrentContro lSet\Contr ol\TimeZon eInformati on\ActiveT imeBias"
minTimeOffset = shell.regread(strValueName )
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
dtListFrom = DateAdd("n", minTimeOffset, now())
gmttime = dateadd("h",toffset,now())
dtListTo = isodateit(dateserial(year( now()),mon th(dateadd ("m",1,now ())),1)& " " & timeserial(0,0,0))
dtListFrom = isodateit(dateserial(year( now()),mon th(now()), 1) & " " & timeserial(0,0,0))
function isodateit(datetocon)
strDateTime = year(datetocon) & "-"
if (Month(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Month(datetocon) & "-"
if (Day(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4 ) &":00Z"
isodateit = strDateTime
end function
response.write "<font face ='arial' size='1'>GMT Time Now: " & gmttime & "<br></font>"
calURL = "https://venus/Exchange/" & username & "/Calendar/"
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL
subject = Request.QueryString("subje ct")
If subject = "" Then
strSQL = "SELECT ""urn:schemas:httpmail:sub ject"", ""DAV:displayname"", ""urn:schemas:calendar:dts tart"", ""urn:schemas:calendar:dte nd"", ""urn:schemas:calendar:bus ystatus"" , ""urn:schemas:calendar:ins tancetype" ",""urn:sc hemas:cale ndar:locat ion"" " & _
" FROM SCOPE('shallow traversal of """ & calURL & """')" & _
" WHERE (""urn:schemas:calendar:dt end"" > CAST(""" & dtListFrom & """ as 'dateTime'))" & _
" AND (""urn:schemas:calendar:dt start"" < CAST(""" & dtListTo & """ as 'dateTime'))"& _
" AND ""DAV:contentclass"" = 'urn:content-classes:appoi ntment'" & _
" ORDER BY ""urn:schemas:calendar:dst art"" ASC"
Set rs = conn.Execute(strSQL)
n = 1
Do While (Not rs.EOF) And (n <= 30)
%><font face ='arial' size='1'><% = Server.HTMLEncode(rs("urn: schemas:ca lendar:dts tart")) %> <a href='events.asp?subject=< % = Server.URLEncode(rs("DAV:d isplayname ")) %>'>
<br><% = Server.HTMLEncode(rs("urn: schemas:ht tpmail:sub ject")) %>
</a> </font><br>
<% n = n + 1
rs.MoveNext
Loop
Set rs = Nothing
Else
Set rec = Server.CreateObject("ADODB .Record")
rec.Open calURL & subject, conn
%><form>
<font face ='arial' size='1'><b>From:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:http mail:sende rname")) %><br>
<b>Subject:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:http mail:subje ct")) %><br>
<b>Start:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:cale ndar:dtsta rt")) %><br>
<b>End:</b> <% = Server.HTMLEncode(rec.Fiel ds("urn:sc hemas:cale ndar:dtend ")) %><br>
<br>
<b>Notes:</b><br>
<hr>
<% = Replace(Server.HTMLEncode( rec.Fields ("urn:sche mas:httpma il:textdes cription") ), vbCrLf, "<br>") %>
<% rec.Close : Set rec = Nothing
%><hr><p>
<input type='submit' value='OK'>
</font>
</form>
<% End If
conn.Close : Set conn = Nothing
%></body>
</html>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------
This shows all appointments within 30 days. I just want to show all appointments that occur over the next 7 days.
Any ideas?
Now I can see all appointment types, but I'm still not sure how to limit it to just this week's appointments.
Here is the code:
--------------------------
<% @LANGUAGE = "VBScript" %>
<html>
<body>
<% servername = Request.ServerVariables("S
username = Request.ServerVariables("R
p = Instr(username, "\")
If p <> 0 Then username = Right(username, Len(username) - p)
set shell = createobject("wscript.shel
strValueName = "HKLM\SYSTEM\CurrentContro
minTimeOffset = shell.regread(strValueName
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
dtListFrom = DateAdd("n", minTimeOffset, now())
gmttime = dateadd("h",toffset,now())
dtListTo = isodateit(dateserial(year(
dtListFrom = isodateit(dateserial(year(
function isodateit(datetocon)
strDateTime = year(datetocon) & "-"
if (Month(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Month(datetocon) & "-"
if (Day(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4
isodateit = strDateTime
end function
response.write "<font face ='arial' size='1'>GMT Time Now: " & gmttime & "<br></font>"
calURL = "https://venus/Exchange/" & username & "/Calendar/"
Set conn = Server.CreateObject("ADODB
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL
subject = Request.QueryString("subje
If subject = "" Then
strSQL = "SELECT ""urn:schemas:httpmail:sub
" FROM SCOPE('shallow traversal of """ & calURL & """')" & _
" WHERE (""urn:schemas:calendar:dt
" AND (""urn:schemas:calendar:dt
" AND ""DAV:contentclass"" = 'urn:content-classes:appoi
" ORDER BY ""urn:schemas:calendar:dst
Set rs = conn.Execute(strSQL)
n = 1
Do While (Not rs.EOF) And (n <= 30)
%><font face ='arial' size='1'><% = Server.HTMLEncode(rs("urn:
<br><% = Server.HTMLEncode(rs("urn:
</a> </font><br>
<% n = n + 1
rs.MoveNext
Loop
Set rs = Nothing
Else
Set rec = Server.CreateObject("ADODB
rec.Open calURL & subject, conn
%><form>
<font face ='arial' size='1'><b>From:</b> <% = Server.HTMLEncode(rec.Fiel
<b>Subject:</b> <% = Server.HTMLEncode(rec.Fiel
<b>Start:</b> <% = Server.HTMLEncode(rec.Fiel
<b>End:</b> <% = Server.HTMLEncode(rec.Fiel
<br>
<b>Notes:</b><br>
<hr>
<% = Replace(Server.HTMLEncode(
<% rec.Close : Set rec = Nothing
%><hr><p>
<input type='submit' value='OK'>
</font>
</form>
<% End If
conn.Close : Set conn = Nothing
%></body>
</html>
--------------------------
This shows all appointments within 30 days. I just want to show all appointments that occur over the next 7 days.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured out my last question as well.
Changed my output commands to:
<% response.write dateadd("h",-7,rs.fields(" urn:schema s:calendar :dtstart") ) %>
Changed my output commands to:
<% response.write dateadd("h",-7,rs.fields("
ASKER
I don't have much experience with ASP either, so I can't make much sense of Microsoft's examples.
It seems like WebDAV or ExOLEDB may be the best solution, but I'm not sure how to get those working in ASP.