query outlook using vba

Status
Not open for further replies.

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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:
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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:
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
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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 ')
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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
 

Bruce Jackson

Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
T Query about one aspect of migrating .pst files from Outlook 2003 to Outlook 2013 Using Outlook 5
C Outlook local calendar & iCloud calendar syncing query Using Outlook 3
D outlook 2003/2007 macro query Using Outlook 2
D Peculiar Outlook message format query Using Outlook 1
M Expand query builder permanently - outlook 2007 Using Outlook 3
M WMI query for Get Disk IO performance in exchange Exchange Server Administration 0
B Select / activate first email item in the searched query Using Outlook 1
Q Mail account setup query Using Outlook 0
T Query About "Delay Delivery" Function Using Outlook 5
S Subject add ticket number from URL query Outlook VBA and Custom Forms 2
P Calendar permissions query Using Outlook 1
R 'Display as' email query Using Outlook 1
C 'Show as conversation' query Using Outlook 0
C Unusual Signature & Spell Check Query Using Outlook 1
A Advanced find query facilty Help please Using Outlook 2
L Exchange TLS query Exchange Server Administration 3
S PSO Issue Query Exchange Server Administration 4
S Search using query builder Using Outlook 2
S DNS query failed error in exchnage 2010 only while sending email to hotmail.com Exchange Server Administration 7
T 451 4.4 dns query failed on exchange 2010 hub transport server Exchange Server Administration 1
V Problem with Search-Mailbox Query Exchange Server Administration 6
D 451 4.4.0 DNS Query Failed. Exchange Server Administration 3
J Rule Category Conditions Query Outlook VBA and Custom Forms 1
J Opening an existing message from a database query Outlook VBA and Custom Forms 1
K Imap PC Outlook + Android phone Using Outlook 0
GregS Outlook 2016 Move Outlook to new computer? Using Outlook 2
I Outlook 2003 shows html code when To: field is empty Using Outlook 5
O The Outlook API wrongfully shows an outlook folder to have zero sub-folders Outlook VBA and Custom Forms 1
O The Outlook API wrongfully shows an outlook folder to have zero sub-folders Outlook VBA and Custom Forms 2
C All Gmails don't show in Outlook 2019 Using Outlook 2
D Outlook 2007 vs. Outlook 2010 -- ToDo Bar Using Outlook 0
U Outlook 2016 Outlook 2016 sender name Using Outlook 1
T Compuserve, Yahoo, Oath2 and Outlook 2013 Using Outlook 4
E Work uses live accounts for emails for all employees. Can we use the outlook calendar to give the employees an universal work schedule calendar? Using Outlook 1
witzker Macro to move @domain.xx of a Spammail to Blacklist in Outlook 2019 Outlook VBA and Custom Forms 4
D Outlook 2007 on 365 Using Outlook.com accounts in Outlook 2
S Macro for other actions - Outlook 2007 Outlook VBA and Custom Forms 19
P outlook 2008 search box criteria couldn't be saved Using Outlook 2
pcunite Outlook 2019/O365 Build 13127.20408 errors when using MAPI calls Using Outlook 1
C Outlook with Office365 - search across account, by date rate, in multiple folders - how? Using Outlook 1
D Outlook 2010 Outlook in Windows 10 keeps asking for user name and password repeatedly Using Outlook 14
S Outlook mail adressing stops after first match in GAL Using Outlook 0
A Apply Selected Emails to outlook rules and Run Rules Using Outlook 5
T Changing Sent Items location in Outlook 2019 Using Outlook 0
J How do I disable advertising in Outlook 2019? Using Outlook 13
S Outlook (2016 32bit; Gmail IMAP) - Save sent message to Outllook Folder Outlook VBA and Custom Forms 0
L Unable to Sync Web/Android MS To Do with Windows Outlook Tasks Using Outlook 3
S Macro to move “Re:” & “FWD:” email recieved the shared inbox to a subfolder in outlook Outlook VBA and Custom Forms 0
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
S Outlook Macro to move reply mail based on the key word in the subjectline Outlook VBA and Custom Forms 0

Similar threads

Top