Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading Outlook from Access (XP)

    Many thanks to Hans.
    However, the code supplied crashes and I can't work out why (except in the most general of terms).

    The code:

    Function FindMeeting(dtCalDate As Date) As String

    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim tdystart As Date
    Dim tdyend As Date
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    tdystart = VBA.Format(dtCalDate, "Short Date") & " 12:00 AM"
    tdyend = VBA.Format(dtCalDate, "Short Date") & " 11:55 PM"
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Ite ms
    Set currentAppointment = myAppointments.Find("[Start] >= """ & tdystart & """ And [Start] <= """ & tdyend & """")
    While TypeName(currentAppointment) <> "Nothing"
    MsgBox currentAppointment.Subject
    Set currentAppointment = myAppointments.FindNext
    Wend

    End Function

    crashes at:
    Set currentAppointment = myAppointments.Find("[Start] >= """ & tdystart & """ And [Start] <= """ & tdyend & """")
    with the error message:
    Run Time Error '-417202167 (e7220009)'
    Method 'Find' of object '_Items' failed

    What I read in that (& I'm probably incorrect) is that the Find method crashed on the Start parameter.

    Can you help?

    Thank you

    Robin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    Since this is a follow-up question to <post#=459913>post 459913</post#>, it would have been better to post it as a reply there.

    You should declare tdystart and tdyend as String, not as Date. It is essential that you pass the start and end time as a formatted string.

    BTW, you should always use error handling in code that manipulates object variables, so that you can release the object memory even if an error occurs.

    Function FindMeeting(dtCalDate As Date) As String
    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim tdyStart As String
    Dim tdyEnd As String
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem

    On Error GoTo ErrHandler

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    tdyStart = Format(dtCalDate, "Short Date") & " 12:00 AM"
    tdyEnd = Format(dtCalDate, "Short Date") & " 11:55 PM"
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Ite ms
    Set currentAppointment = myAppointments.Find("[Start] >= """ & _
    tdyStart & """ And [Start] <= """ & tdyEnd & """")
    Do While TypeName(currentAppointment) <> "Nothing"
    MsgBox currentAppointment.Subject
    Set currentAppointment = myAppointments.FindNext
    Loop

    ExitHandler:
    Set currentAppointment = Nothing
    Set myAppointments = Nothing
    Set myNameSpace = Nothing
    Set myOlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

  3. #3
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Thanks Hans,

    I'm still getting an error which is still occuring at that line. This time it is -1387134967 Automation Error.

    Function FindMeeting(dtCalDate As Date) As String

    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim tdyStart As String
    Dim tdyEnd As String
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem

    On Error GoTo ErrHandler

    dtCalDate = Format(dtCalDate, "mm/dd/yy")

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    tdyStart = Format(dtCalDate, "Short Date") & " 12:00 AM"
    tdyEnd = Format(dtCalDate, "Short Date") & " 11:55 PM"
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Ite ms
    Set currentAppointment = myAppointments.Find("[Start] >= """ & tdyStart & """ And [Start] <= """ & tdyEnd & """")
    Do While TypeName(currentAppointment) > "Nothing"
    MsgBox currentAppointment.Subject
    Set currentAppointment = myAppointments.FindNext
    Loop

    ExitHandler:
    Set currentAppointment = Nothing
    Set myAppointments = Nothing
    Set myNameSpace = Nothing
    Set myOlApp = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Number & " " & Err.Description, vbExclamation
    Resume ExitHandler
    End Function


    Thanks

    Robin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    The line

    Do While TypeName(currentAppointment) > "Nothing"

    should be

    Do While TypeName(currentAppointment) <> "Nothing"

    or alternatively

    Do While Not (currentAppointment Is Nothing)

    With either of these changes, the code runs OK for me (Office XP SP-3). I don't get an error message at the line you mention. Are you sure you are supplying a valid date?

    You can try the following:
    - Click in the line Function FindMeeting(....)
    - Press F9 to set a breakpoint.
    - When you call the function, code execution will pause at the line with the breakpoint.
    - Advance line by line by pressing F8 repeatedly.
    - When the line Set CurrentAppointment = ... is highlighted, hover the mouse pointer over tdyStart and tdyEnd to see if they are valid.

  5. #5
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Hi Hans and thanks for the help,

    Even with these changes it crashes. Here is the full code again. The crash occurs at
    Set currentAppointment = myAppointments.Find("[Start]..................
    The error this time is -1248722935 Automation error

    It is very strange. Originally I thought that '<' were some characters that came only with this post & were not a part of the code so I removed them from the line
    Do While not (currentAppointment) <> "Nothing"
    as they were causing another error, "Compile Error, expected end of statement" with the ; highlighted in the code. I took the whole statement out originally (<) and the compile error went away but obviously there were other errors generated.

    I then tried it with the line
    Do While Not (currentAppointment Is Nothing)
    as suggested and no compile errors appeared.

    This is weird as the line
    Set currentAppointment = myAppointments.Find("[Start] >= """ _
    & tdyStart & """ And [Start] <= """ & tdyEnd & """")

    has that string in it and there is no compile error there.

    I run this function by going to the Immediate Window and entering
    ?findmeeting(#2/3/05#)
    (ie 2nd March 2005 - Australian standard).

    The code as it stands now is

    Function FindMeeting(dtCalDate As Date) As String

    Dim myOlApp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim tdyStart As String
    Dim tdyEnd As String
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem

    On Error GoTo ErrHandler

    dtCalDate = Format(dtCalDate, "mm/dd/yy")

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    tdyStart = Format(dtCalDate, "Short Date") & " 12:00 AM"
    tdyEnd = Format(dtCalDate, "Short Date") & " 11:55 PM"
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Ite ms
    Set currentAppointment = myAppointments.Find("[Start] >= """ _
    & tdyStart & """ And [Start] <= """ & tdyEnd & """")
    ' Do While not (currentAppointment) <> "Nothing"
    Do While Not (currentAppointment Is Nothing)

    MsgBox currentAppointment.Subject
    Set currentAppointment = myAppointments.FindNext
    Loop

    ExitHandler:
    Set currentAppointment = Nothing
    Set myAppointments = Nothing
    Set myNameSpace = Nothing
    Set myOlApp = Nothing
    Exit Function

    ErrHandler:
    ' MsgBox Err.Number & " " & Err.Description, vbExclamation
    Debug.Print Err.Number & " " & Err.Description
    Resume ExitHandler
    End Function

    One last thing, what does the string < mean and where can I find help on these and other expressions like these?

    Once again, thanks for you help. (I intend to download SP3 now to see if this helps).

    Cheers

    Robin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    Sometimes the browser messes up text containing "greater than" or "lesser than" symbols. This was the cause of your problem with the Do While ... line, and perhaps also with the Set currentAppointment = ... line. To make sure that you know what is intended, I have attached a screenshot.

  7. #7
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Hans

    I've worked the errors out at least (and then I just got your response which confirmed it). There was a strange literal that was turning up within Access from these posts ("<") which turned out to be the less than sign (<). Cleaning these and only using the line

    Do While Not (currentAppointment Is Nothing)

    cleaned out the errors. However now I am having trouble with the dates.

    Using Date as the parameter works well but trying to use something like 2/3/05 (2nd March 2005) or format(#2/3/05#, "mm/dd/yy") etc doesn't work even though I have appointments there on the 2nd. The code is basically saying that there is no current appointment at
    Do While Not (currentAppointment Is Nothing).

    What to do about these dates?

    Cheers

    Robin

    Thanks

  8. #8
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    (Sorry, I responded to myself, not you the last time duh!)
    Hans

    I've worked the errors out at least (and then I just got your response which confirmed it). There was a strange literal that was turning up within Access from these posts ("<") which turned out to be the less than sign (<). Cleaning these and only using the line

    Do While Not (currentAppointment Is Nothing)

    cleaned out the errors. However now I am having trouble with the dates.

    Using Date as the parameter works well but trying to use something like 2/3/05 (2nd March 2005) or format(#2/3/05#, "mm/dd/yy") etc doesn't work even though I have appointments there on the 2nd. The code is basically saying that there is no current appointment at Do While Not (currentAppointment Is Nothing).

    What to do about these dates?

    Cheers

    Robin

  9. #9
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Edited - again <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> - by HansV to provide link to MSKB article using <!t>[mskb=149095]<!/t>

    Sorry Hans, I allowed myself to get too excited at the thought I had worked it out.

    I found what I thought was a fix at <!mskb=149095>Microsoft Knowledge Base Article 149095<!/mskb>

    However, I keep getting all sorts of conversion errors when I try to use this.

    For example:

    dtCalDate = MakeUSDate(dtCalDate)

    gives Error No:13 Type mismatch", because, I think, this function returns a variant and not a date. When I changed the MakeUSDate function to return a date, I got the same error "Type mismatch".

    I have experimented with this function (as I remember it is the best to use for date conversions) but have got nowhere.

    Where and what am I doing wrong?

    Thanks

    Robin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    Try

    tdyStart = Format(dtCalDate, "mm/dd/yyyy") & " 12:00 AM"
    tdyEnd = Format(dtCalDate, "mm/dd/yyyy") & " 11:59 PM"

  11. #11
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Using format(dtCalDate, "mm/dd/yyyy") doesn't work either. I have checked & double checked that I have meetings on the 2nd March 2005 so one would have thought that using that format would have captured the date in the US format. Not so.

    Cheers (and thanks for the effort)

    Robin

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    What is your local date format?

  13. #13
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Local format is dd/mm/yyyy

    In Control Panel it is:
    (GMT +10) Canberra, Melbourne, Sydney

    It is now adjusted for Australian Eastern Daylight Saving Time (+1)

    Cheers

    Robin

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Reading Outlook from Access (XP)

    Try the local format then:

    Try

    tdyStart = Format(dtCalDate, "dd/mm/yyyy") & " 12:00 AM"
    tdyEnd = Format(dtCalDate, "dd/mm/yyyy") & " 11:59 PM"

  15. #15
    New Lounger
    Join Date
    Mar 2005
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Outlook from Access (XP)

    Hans,

    I think I found the problem.

    I just created two dummy meetings for the 2nd March and found they were returned in the function. However, the 2 that were there are recurring appointments. I would have thought that an AppointmentItem would have also included a recurring appointment. It doesn't seem to do so. Maybe it has something to do with the Object Model?

    Thanks

    Robin

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •