We help IT Professionals succeed at work.

Query Exchange Calender with ASP Page

isol8
isol8 asked
on
591 Views
Last Modified: 2008-02-26
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?
Comment
Watch Question

Author

Commented:
We're running Exchange 2003 as well.  

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.

Author

Commented:
I've read through those, but they don't really show me any of the necessary code.
Top Expert 2004

Commented:

Author

Commented:
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.
Top Expert 2004

Commented:
I'm just providing you the tools...I can't write this all for you.

Author

Commented:
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.
Top Expert 2004

Commented:
That link above should work:

debug it...
http://support.microsoft.com/?kbid=179639

Author

Commented:
I've made some progress using the following code:

------------------------------------------------------------------------------------------------------
<% @LANGUAGE = "VBScript" %>
<html>
<body>
<% servername = Request.ServerVariables("SERVER_NAME")
username = Request.ServerVariables("REMOTE_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.Connection")
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL

subject  = Request.QueryString("subject")
If subject = "" Then
  Response.Write "Upcoming Events:<p>"
  strSQL = "SELECT ""urn:schemas:httpmail:subject"", ""DAV:displayname""" & _
   " FROM SCOPE('shallow traversal of """ & calURL & """')" & _
   " ORDER BY ""urn:schemas:httpmail:date"" 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:displayname")) %>'>
    <% = Server.HTMLEncode(rs("urn:schemas:httpmail:subject")) %>
    </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.Fields("urn:schemas:httpmail:sendername")) %><br>
  <b>Subject:</b> <% = Server.HTMLEncode(rec.Fields("urn:schemas:httpmail:subject")) %><br>
  <input type='submit' value='OK'><br>
  <b>Message:</b><br>
  <% = Replace(Server.HTMLEncode(rec.Fields("urn:schemas:httpmail:textdescription")), 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?

Author

Commented:
I also just noticed that this query does not show Recurring Appointments

Author

Commented:
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("SERVER_NAME")
username = Request.ServerVariables("REMOTE_USER")
p = Instr(username, "\")
If p <> 0 Then username = Right(username, Len(username) - p)

set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
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()),month(dateadd("m",1,now())),1)& " " & timeserial(0,0,0))
dtListFrom = isodateit(dateserial(year(now()),month(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.Connection")
conn.Provider = "ExOLEDB.DataSource"
conn.Open calURL
subject  = Request.QueryString("subject")
If subject = "" Then

  strSQL = "SELECT ""urn:schemas:httpmail:subject"", ""DAV:displayname"", ""urn:schemas:calendar:dtstart"", ""urn:schemas:calendar:dtend"", ""urn:schemas:calendar:busystatus"" , ""urn:schemas:calendar:instancetype"",""urn:schemas:calendar:location"" " & _
   " FROM SCOPE('shallow traversal of """ & calURL & """')" & _
   " WHERE (""urn:schemas:calendar:dtend"" > CAST(""" & dtListFrom & """ as 'dateTime'))" & _
   " AND (""urn:schemas:calendar:dtstart"" < CAST(""" & dtListTo & """ as 'dateTime'))"& _
   " AND ""DAV:contentclass"" = 'urn:content-classes:appointment'" & _
   " ORDER BY ""urn:schemas:calendar:dstart"" 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:calendar:dtstart")) %>  <a href='events.asp?subject=<% = Server.URLEncode(rs("DAV:displayname")) %>'>
    <br><% = Server.HTMLEncode(rs("urn:schemas:httpmail:subject")) %>
    </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.Fields("urn:schemas:httpmail:sendername")) %><br>
  <b>Subject:</b> <% = Server.HTMLEncode(rec.Fields("urn:schemas:httpmail:subject")) %><br>
<b>Start:</b> <% = Server.HTMLEncode(rec.Fields("urn:schemas:calendar:dtstart")) %><br>
<b>End:</b> <% = Server.HTMLEncode(rec.Fields("urn:schemas:calendar:dtend")) %><br>
<br>
  <b>Notes:</b><br>
  <hr>
  <% = Replace(Server.HTMLEncode(rec.Fields("urn:schemas:httpmail:textdescription")), 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?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Figured out my last question as well.
Changed my output commands to:

<% response.write dateadd("h",-7,rs.fields("urn:schemas:calendar:dtstart")) %>
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.