query outlook using vba

Discussion in 'Slipstick.com: VBA and Programming Articles' started by Bruce Jackson, Jul 26, 2017.

  1. Bruce Jackson

    Bruce Jackson

    Member
    Is it possible to search outlook using a query in a vba script .I have a 3 calendars set up where I write to outlook and insert delivery job numbers and some information pertaining to the job as appointments
    I would like to clear the calendar of that job number entry once the delivery has been made
    I currently use
    For Each oObject In oFolder.Items ,which checks each item of outlook to see if the particular job number exists
    and if it does it clears the entry
    the problem with that method is as each day passes the script takes longer and longer to execute
    I need some way to query outlook,to cut down on checking every single item
     
  2. Diane Poremsky

    Diane Poremsky

    Senior Member
    Below is the method i use to find and delete travel times from old appt. i'm not sure its any faster to use count over for each... but when i run (usually monthly), i need to delete 30 or 40 old events. (Will need to test it again one using find)

    what you need to use is find or restrict - one sample is here How to print a list of recurring dates using VBA

    Code (Text):
    Copy Source
    Sub BlockedTimeDelete()
        Dim objOutlook As Outlook.Application
        Dim objNamespace As Outlook.NameSpace
        Dim objSourceFolder As Outlook.MAPIFolder
        Dim objVariant As Variant
        Dim lngMovedItems As Long
        Dim intCount As Integer
       
        Set objOutlook = Application
        Set objNamespace = objOutlook.GetNamespace("MAPI")
        Set objSourceFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
       
        For intCount = objSourceFolder.Items.Count To 1 Step -1
            Set objVariant = objSourceFolder.Items.Item(intCount)
            DoEvents
            If objVariant.Subject = "Travel Time" Or objVariant.Subject = "Meeting Review Time" Then
                If objVariant.Start < Now Then
                  objVariant.Delete
                 
                  'count the # of items moved
                   lngMovedItems = lngMovedItems + 1
                End If
            End If
        Next
       
        ' Display the number of items that were moved.
        MsgBox "Moved " & lngMovedItems & " messages(s)."
    End Sub
     
     
  3. Diane Poremsky

    Diane Poremsky

    Senior Member
    This is faster - i need it to delete one of two subjects and will need to work on that, but for single subjects it is faster.

    Code (Text):
    Copy Source
     
    Sub DeleteBlockedTime()
       
       Dim CalFolder As Outlook.MAPIFolder
       Dim CalItems As Outlook.Items
       Dim ResItems As Outlook.Items
       Dim sFilter, strSubject, strOccur As String
       Dim iNumRestricted As Integer
       Dim itm, ListAppt As Object
       Dim tStart, tEnd As Date
       ' Use the selected calendar folder
       Set CalFolder = Application.ActiveExplorer.CurrentFolder
       Set CalItems = CalFolder.Items

       ' Sort all of the appointments based on the start time
       CalItems.Sort "[Start]"
       CalItems.IncludeRecurrences = True

       ' Set an end date
        tEnd = Format(Now, "Short Date")
       
        strSubject = "Travel Time"

       'create the Restrict filter by day and recurrence
       sFilter = "[End] < '" & tEnd & "' And [Subject] = " & strSubject
    Debug.Print sFilter
       Set ResItems = CalItems.Restrict(sFilter)

       iNumRestricted = 0

       'Loop through the items in the collection.
       For Each itm In ResItems
          iNumRestricted = iNumRestricted + 1
          itm.Delete
       Next
     
       Set itm = Nothing
       Set ListAppt = Nothing
       Set ResItems = Nothing
       Set CalItems = Nothing
       Set CalFolder = Nothing
     
    End Sub
     
  4. Bruce Jackson

    Bruce Jackson

    Member
    Thanks Diane
    if I understand this correctly-that gets all items between dates,then loops through each item checking for the string value passed ?
    where did you set your tStart in this code and how would the sfilter look with the tStart
    Thanks for your help
     
  5. Diane Poremsky

    Diane Poremsky

    Senior Member
    This sorts the calendar by date (in a hidden list view) and then looks for events matching the filter conditions- in my case, looking for the end date and subject. If it finds it, the item is deleted.

    Note that if you are deleting more than one item, it will skip some - as many as every other one - because it "loses count" when you delete items and it moves forward (if you have 4 items and delete # 1, outlook moves to #2, which was previously #3). In my tests with 8 travel and review events, it skipped one or two when i used case statements and ran the filter twice, but 4 when i put both subjects in the same filter. The first macro steps backward so the count isn't affected.

    I'm only checking the end date (which is the same as the start date for one day events, which all are so it doesn't make a difference here...). if you need to check the start date, you'd basically copy the tEnd code and change it to tStart. Or since the only place that Start or End really matters is in the filter, you could just change [End] to [Start]. If you need to check both start and end, you'd add [Start] < '" & tStart & to the filter (with the proper # of " and ')
     
  6. Bruce Jackson

    Bruce Jackson

    Member
    Thanks Diane
    I am not sure about the losing count- I loop through a recordset in an access db and delete one record in outlook for each loop in the database.when I first started it took outlook maybe a few secs per record,and all up maybe 1 minute for the script to complete
    now-its 10mins and rising,which is not sustainable
    thanks again
    I will experiment a bit-and see how I go
     
  7. Diane Poremsky

    Diane Poremsky

    Senior Member
    it should work fine for you if its one item per loop. I don't know if it would be much faster, but if outlook could get an array of the search terms, it might be faster to run everything in Outlook. Using an array rather than reading the access data base over and over would be faster too.
     
  8. Bruce Jackson

    Bruce Jackson

    Member
    I could pass a string array to outlook-delimted by what ever outlook would recognise
    of course to build the array would require looping through the recordset-but that happens very quickly using vba within access.
    what how would I pass that to outlook to process
     
  9. Diane Poremsky

    Diane Poremsky

    Senior Member
    The problem isn't how fast Access does it, it's that Outlook needs to keep reading it (or Access needs to keep going back to Outlook). If you can read the db once, it eliminates one step that gets repeated many times.

    This page is not a really good example because it uses an array in the macro - i'll see if i can find an example from reading a file -
    Using Arrays in Outlook macros

    once the array is in the variant in memory, you do roll through the array doing whatever....

    For i = LBound(arrSubject) To UBound(arrSubject)

    sFilter = "[End] < '" & tEnd & "' And [Subject] = " & arrSubject(i)
    Debug.Print sFilter
    Set ResItems = CalItems.Restrict(sFilter)

    iNumRestricted = 0

    'Loop through the items in the collection.
    For Each itm In ResItems
    iNumRestricted = iNumRestricted + 1
    itm.Delete
    Next

    Next i
     
  10. Bruce Jackson

    Bruce Jackson

    Member
    heres the sub I use
    Public Sub DeleteScheduledJob(ByVal argSubject As String, MailName As String)

    'the job number I delete is supplied in argSubject

    Dim OApp As Object
    Dim oNameSpace As Object
    Dim oApptItem As Object
    Dim oFolder As Object
    Dim oMeetingoApptItem As Object
    Dim oObject As Object
    Dim sErrorMessage As String

    On Error Resume Next
    ' check if Outlook is running
    Set OApp = CreateObject("Outlook.Application")


    On Error GoTo Err_Handler
    Set oNameSpace = OApp.GetNamespace("MAPI")
    Set oFolder = GetFolderPath(MailName & "\Calendar")
    For Each oObject In oFolder.Items
    If oObject.Class = olAppointment Then
    Set oApptItem = oObject
    If InStr(oApptItem.Subject, argSubject) > 0 Then
    oApptItem.Delete

    End If
    End If
    Next oObject

    Set OApp = Nothing
    Set oNameSpace = Nothing
    Set oApptItem = Nothing
    Set oFolder = Nothing
    Set oObject = Nothing
    Set oMeetingoApptItem = Nothing

    Exit Sub

    Err_Handler:
    sErrorMessage = Err.Number & " " & Err.Description

    End Sub

    I could add another argument to the sub containing the string array built in the calling sub
    just not sure what I need to do to use that array
     
  11. Bruce Jackson

    Bruce Jackson

    Member
    this is how I could supply a comma delimted array of job numbers to delete
    Public Sub DeleteScheduledJob(ByVal argSubject As String, MailName As String)

    dim myarray

    myarray=split(argSubject,",")
    from here I am lost as to how to use the array
    hope this is making sense to you
    again-thanks for the help
     
  12. Diane Poremsky

    Diane Poremsky

    Senior Member
    on the macro - are you the only user? it might speed it up a little to use early binding to the outlook object model. late binding is easier when you are sharing it with others.



    Something like this should work -

    For i = LBound(myarray) To UBound(myarray)

    sFilter = "[End] < '" & tEnd & "' And [Subject] = " & myarray(i)
    Debug.Print sFilter
    Set ResItems = CalItems.Restrict(sFilter)

    iNumRestricted = 0

    'Loop through the items in the collection.
    For Each itm In ResItems
    iNumRestricted = iNumRestricted + 1
    itm.Delete
    Next

    Next i
     
  13. Bruce Jackson

    Bruce Jackson

    Member
    unfortunately I need to use late binding as it has several users and I can never be sure what version of outlook they use
    I will give it a try and see how I go
    I got the guy who looks after outlook 365 for this company to clean up and archive outlook items
    hes also set up some automatic archiving(whatever that means)-and the script is back running fast (delete 40 entries in maybe 10 secs)
    I don't understand the outlook model -its methods and propertys,and what you can or cant do -so it will be trial and error for me
    Regards
     
  14. Diane Poremsky

    Diane Poremsky

    Senior Member
    Yeah, in that case you definitely need late binding.

    >> I got the guy who looks after outlook 365 for this company to clean up and archive outlook items
    It sounds like the problem was due, in part, to so many items to search though.
     
Loading...

Share This Page