vbaoutlookoutlook-2010

Search for appointments by Date/Time to extract subject and description


I'm trying to find an appointment on or after a specific date (for this program today was chosen) in the current session of a single workstation.
I then want to extract the Subject line from the appointment and the Description and display them in a message box (for future error checking).
If possible I also want to count how many appointments are in a single day.

I'm having trouble setting the object as well as binding "i" to the correct item in Outlook's array. I say "Outlook's array" because in the base code I have oItems.Item(i) where i is an Item from some appointment in my calender at the designated number.

Maybe it would be better to use something else besides Item?
Or better yet find an item's location in the array that is relevant to the date that I'm restricting the search to?

Below is the old code before Dmitry's link.

Private Sub FindAppt()
 
    Dim oItems As Items
    Dim oItemOriginal As AppointmentItem
    Dim Subject As String
    Dim Descript As String
 
    Set oItems = Outlook.Application.Session.GetDefaultFolder(olFolderCalendar).Items
    
    If oItems >= Format(Date, "mmmm dd yyyy") Then
  
        Set oItemOriginal = oItems.Item(i)

    End If
 
    With oItemOriginal
 
        Subject = .Subject
        Descript = .FormDescription
    
    End With
    
    MsgBox (Subject & Description)
 
End Sub

New code. With the new approach should I be breaking up these array binding and array extraction pieces into separate "Subs"?

Sub FindAppt()
 
    Dim myNameSpace As Outlook.NameSpace
    Dim tdystart As Date
    Dim tdyend As Date
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem
    Dim SubjectArray(50) As Variant
    Dim i As Integer
    Dim DescArray(50) As Variant
    Dim Excl As Excel.Application
 
    Set myNameSpace = Application.GetNamespace("MAPI")
    'This line is Bonus, if you're just looking to start your search for today's_  
      date.
    tdystart = VBA.Format(Now, "Short Date") 
    'This input works which means a user form with combo boxes will work or user input_
      will work as long as user input conforms to VBA date formats.
    'tdystart = "04/01/2014" 
    'This line is Bonus, if you're just looking for the day after and after_      
       appointments.
    tdyend = VBA.Format(Now + 1, "Short Date") 
    'tdyend = VBA.Format(tdystart + 5, "Short Date")
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Items
    myAppointments.Sort "[Start]"
    myAppointments.IncludeRecurrences = True
    
    Set currentAppointment = myAppointments.Find("[Start] >= """ & tdystart & """ and [Start] <= """ & tdyend & """")
    
    While TypeName(currentAppointment) <> "Nothing"
    
        MsgBox currentAppointment.Subject & " " & currentAppointment.FormDescription
        
        While currentAppointment = True
            
            For i = 0 To 50
                SubjectArray(i) = currentAppointment.Subject
                DescArray(i) = currentAppointment.FormDescription
                ReDim Preserve SubjectArray(1 To Count + 1)
                ReDim Preserve DescArray(1 To Count + 1)
            Next i
        
        Wend
        
        Set currentAppointment = myAppointments.FindNext
    
    Wend
    
End Sub

Private Sub Timecard()
        
    Set Excl = Excel.Application
    Dim i As Integer
    Dim SubjectArray(byRef 50, byValue) As Variant
    Dim DecArray (byRef,byvalue)
    
    With Excl
        .fPath = ("C:\FilePathName\Book1.xlsx")
        Excl.Open
    End With
        
    For i = 0 To 50
        Excl.Application.Activesheet.Range(i, 0) = SubjectArray(i)
        Excl.Application.Activesheet.Range(i, 1) = DecArray(i)
    Next
    
End Property
    
End Sub

Solution

  • I am not sure what the line "If oItems >= Format(Date, "mmmm dd yyyy") Then" is supposed to do: you are comparing an Items object with a string.

    See http://msdn.microsoft.com/en-us/library/office/ff866969(v=office.15).aspx for an example on how to retrieve items in a particular time range.