I want to create filtered views on the fly. The scenario: multiple projects, with a prefix in the subject identifying the project. I want to select a filter and apply it. I don't want to use Instant Search.
You can do this using a macro and a userform. The userform reads a text file stored on your hard drive, so it's easy to update the list when needed.
Use a UserForm to Select a Keyword
This macro sample loads a list of keywords in a userform. Run the macro and select the keyword to use in the filter.
- Create a text file containing the keywords, one keyword (or phrase) per line. Add reset as one keyword to reset the view. (Table views will immediately show everything when you click reset; you need to leave the calendar and come back to see everything.)
- Create a Userform with one ListBox and a Command button.
- Right click on the Userform, choose View Code then paste the following macro into it.
- Update the path to the text file in the macro.
This ViewFilterUserForm1 zip file has a sample UserForm (and the views macro), ready to Import into the VB Editor.
Private Sub UserForm_Initialize() Dim fn As String, ff As Integer, txt As String fn = "C:\Users\Diane\Documents\subject.txt" '< --- .txt file path txt = Space(FileLen(fn)) ff = FreeFile Open fn For Binary As #ff Get #ff, , txt Close #ff Dim myArray() As String 'Use Split function to return a zero based one dimensional array. myArray = Split(txt, vbCrLf) 'Use .List method to populate listbox. ListBox1.List = myArray lbl_Exit: Exit Sub End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) For lngCount = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(lngCount) = True Then strFilter = ListBox1.List(lngCount) End If Next Unload Me lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click() For lngCount = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(lngCount) = True Then strFilter = ListBox1.List(lngCount) End If Next Unload Me lbl_Exit: Exit Sub End Sub
After adding the userform, right click on Project1 and choose Insert, Module. Paste the following macro in the new module.
To use this run this macro to load the userform. Either double click on the keyword or select it and click OK and the current view will show only items containing that keyword.
This macro works on any folder; Reset will clear the filter, however the calendar folder won't update until you leave the folder and come back to it.
This sample macro filters by a word in the subject EXPECT if the select word is Personal, then it filters by category. To use more words in the If statement, use a case statement.
The keyword filter is not case sensitive, the category filter is.
Public lstNum As Long Public strFilter As String Public Sub FilterView() Dim objView As View UserForm1.Show Set objView = Application.ActiveExplorer.CurrentView ' keywords in subject ' not case sensitive objView.Filter = Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " LIKE '%" & strFilter & "%'" ' case sensitive If LCase(strFilter) = "personal" Then objView.Filter = Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " = '" & strFilter & "'" End If Debug.Print objView.Filter objView.Save ' filter removed from calendar, does not refresh ' table views refresh If LCase(strFilter) = "reset" Then objView.Reset End If objView.Apply End Sub
Create a UserForm Video Tutorial
Use Macro Buttons
This version of the macro uses buttons on the ribbon. This sample uses different filters.
Dim strKeyword As String Dim strFilter As String Sub View1() strKeyword = "search term" strFilter = Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " LIKE '%" & strKeyword & "%'" FilterView2 End Sub Sub View2() strKeyword = "= 'Business'" strFilter = Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & strKeyword FilterView2 End Sub Private Sub FilterView2() Dim objView As View Set objView = Application.ActiveExplorer.CurrentView objView.Filter = strFilter Debug.Print objView.Filter objView.Save objView.Apply End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
Changed:
Dim objView As View to Dim objView As Outlook.View
Now working with Office365,
by the way great macro, thanks.
This saved me so much time, thanks.
Hello Diane, great facilitation.
Is there any way for the filter to work on all mailboxes in the view, or just the current one?
possibly, i will need to check...
Can you expand this vba code which works really well so this filter search is not only for strings applicalble in the Subject Line but also onto strings find in the mails themselves. THanks for your assistance !
You'd use the following in the code -
"urn:schemas:httpmail:textdescription" LIKE '%keyword to find%'
to find the keyword in the subject or body, use
("urn:schemas:httpmail:textdescription" LIKE '%keyword to find%' OR "urn:schemas:httpmail:subject" LIKE '%keyword to find%')
Hi Diane,
THANK YOU for developing this code- I want to look in subject or body, but I'm getting this error message: Run-time error '13': Type mismatch
That means things like you are in contacts and nut its set for mail items, or a variable is set to be a number and you have text. Post the entire code you are using and I'll take a look.
I took your code from FilterView (thank you so much it's amazing!) but when I try to combine looking in subject and body, I get an error message.
Public Sub FilterView()
Dim objView As View
UserForm1.Show
Set objView = Application.ActiveExplorer.CurrentView
' keywords in subject
' not case sensitive
LIKEstrFilter = Chr(34) & " LIKE '%" & strFilter & "%'"
' Look in both Subject or Body gives error!
'objView.Filter = (Chr(34) & "urn:schemas:httpmail:subject" & LIKEstrFilter) Or _
(Chr(34) & "urn:schemas:httpmail:textdescription" & LIKEstrFilter)
' case sensitive
If LCase(strFilter) = "personal" Then
objView.Filter = Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " = '" & strFilter & "'"
End If
Debug.Print objView.Filter
objView.Save
' filter removed from calendar, does not refresh
' table views refresh
If LCase(strFilter) = "reset" Then
objView.Reset
End If
objView.Apply
End Sub
I want to search both body and subject at same time and return the results.
I thought your example would work with the OR, but it doesn't.
-------
LIKEstrFilter = Chr(34) & " LIKE '%" & strFilter & "%'"
objView.Filter = ("urn:schemas:httpmail:subject" & LIKEstrFilter) Or ("urn:schemas:httpmail:textdescription" & LIKEstrFilter)
Dear Diana,
This piece of code is very useful, many thanks for developing it! May I ask for your assistance in guiding me on the re-edit of the code? What I need is to apply filtered views on the range of dates! Is that possible to do so? If so, please provide some clue
You filter by pretty much anything - just change the filter line:
objView.Filter = Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " LIKE '%" & strFilter & "%'"
If you need to filter by two factors, they'd go in in line, if you need to filter by date sometimes or keywords other, you'd use an if statement (as is used in the code)
To get the filter line to use, go into View settings and create the filter you want to use then copy it from the SQL tab. You need to replace the double quotes with chr(34) as I've done and replace the value with strFilter.
I was able to use the standard statement:
objView.Filter = "%lastweek(""urn:schemas:httpmail:datereceived"")%"
Where I changed lastweek to yesterday and it also worked.
But I failed to properly change the following statement.
objView.Filter = Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " LIKE '%" & strFilter & "%'"
Can you please advise what does each element of the statement stands for?
This should work -
objView.Filter = Chr(34) & "%" strFilter & "(" & Chr(34) & Chr(34) & "urn:schemas:httpmail:datereceived" & Chr(34) & Chr(34) & ")%" & Chr(34)
This might work - i prefer using chr(34) for the double quotes instead of duplicating them. if it doesn't work with 3 quotes in a row, try 4.
objView.Filter = Chr(34) & "%" strFilter & "("""urn:schemas:httpmail:datereceived""")%" & Chr(34)
I am getting Compile Error: Syntax Error. What that might be?
does it error on any specific line?
Both of the objView.Filter = Chr(34) & "%" strFilter & "(" & Chr(34) & Chr(34) & "urn:schemas:httpmail:datereceived" & Chr(34) & Chr(34) & ")%" & Chr(34) and the objView.Filter = Chr(34) & "%" strFilter & "("""urn:schemas:httpmail:datereceived""")%" & Chr(34). The font of the line became red.
It looks like there is a & missing - the lines should start like
objView.Filter = Chr(34) & "%" & strFilter
with that fixed (and another pair of ' in the second one), the result for either should be "%keyword(""urn:schemas:httpmail:datereceived"")%"
This macro shows what gets pushed to the filter -
Sub test()
strfilter = "keyword"
Debug.Print Chr(34) & "%" & strfilter & "(" & Chr(34) & Chr(34) & "urn:schemas:httpmail:datereceived" & Chr(34) & Chr(34) & ")%" & Chr(34)
Debug.Print Chr(34) & "%" & strfilter & "(""""urn:schemas:httpmail:datereceived"""")%" & Chr(34)
End Sub
The Sub test ran well. But in in the Views module I am getting the other error as follows:
Now it says Run-time error ‘-1525661691 (a5104005)’:
Cannot parse condition. Error at “%Yesterday(“”urn:schemas:httpmail:dater…”.
I used the statement "Today", "Yesterday" and "reset" in the text file in the following link.
fn = "c:\Buttons\Commands.txt" '< --- .txt file path
Do you think this is related to the statement I used or the code?
That means the filter in invalid. Compare it to what outlook shows in the sql tab - it looks like we have too many ".
This Chr(34) & "%" & strfilter & "(" & Chr(34) & "urn:schemas:httpmail:datereceived" & Chr(34) & ")%" & Chr(34) or Chr(34) & "%" & strfilter & "(""urn:schemas:httpmail:datereceived"")%" & Chr(34) print out the same as shown in the sql pane (the ' on the ends is so it works in VBA)
This is the query I built using filter. In the tab messages From I used my account Planner, in the tab Advanced date sent yesterday and in the SQL I got this line
("https://schemas.microsoft.com/mapi/proptag/0x0042001f" LIKE '% Planner (TC)%' AND %yesterday("urn:schemas:httpmail:date")%)
SQL runs well, but I failed to re-edit in VBA, it gets me Compile Error, Syntax error.
Hello Diana,
No, it does not work, unfortunately.
This is the ouput I get from SQL tab
("https://schemas.microsoft.com/mapi/proptag/0x0042001f" LIKE '%Planner (TC)%' AND %yesterday("urn:schemas:httpmail:date")%)
But I have hard time re-code the line. Made so many attempts, always something wrong with the line.
Can you please have a look advise which way to put? I am using MS Outlook 2016 (Exchange)
Does this work?
"(" & chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & chr(34) & " LIKE '%Planner (TC)%' AND %yesterday(" & chr(34) & "urn:schemas:httpmail:date" & chr(34) & ")%)"
Once you get this format right, then work on switching to variables.
Dear Diana,
This link works just perfect
"(" & chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & chr(34) & " LIKE '%Planner (TC)%' AND %yesterday(" & chr(34) & "urn:schemas:httpmail:date" & chr(34) & ")%)"
Then I replaced %yesterday with this '%" & strFilter & "%'" - and those this line
objView.Filter = "(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%' AND '%" & strFilter & "%'" (" & Chr(34) & "urn:schemas:httpmail:date" & Chr(34) & ")%)"
The font became red and I am getting Compile Error, Expected: End of Statement
Then slightly changed the line to this
objView.Filter = "(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%' AND '%" & strFilter & (" & Chr(34) & "urn:schemas:httpmail:date" & Chr(34) & ")%)"'
Again no luck!
This looks wrong - strFilter & "%'" (" & -
Should be: strFilter & "%' (" &
Hello Diana,
Now I used the following statement
objView.Filter = "(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%' AND '%" & strFilter & "%' (" & Chr(34) & "urn:schemas:httpmail:date" & Chr(34) & ")%')"
This error pops up.
Run-time error ‘-1252646651 (af604005) ‘:
Cannot parse the condition. Error at “(“https://schemas.microsoft.com/mapi/prop...”.
Any clues what this time I made wrong?
i used the debug.print code to print it out and paste it into the sql field in outlook - it tells me it is invalid.
one issue is here: strFilter & "%' (" & Chr(34) & "urn:schemas:httpmail:date" & Chr(34) & ")%')" - mismatched % - the one after strfilter is not needed.
Dear Diana,
No luck again. I used the following code:
objView.Filter = "(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%' AND '%" & strFilter & "' (" & Chr(34) & "urn:schemas:httpmail:date" & Chr(34) & ")')"
And the error message was:
Run-time error ‘-1214234619 (b7a04005)’:
Cannot parse condition. Error at “(https://schemas.microsoft.com/mapi/prop...”.
I look forward to hearing your advice.
what value is strFilter? does it work if you use just "(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%')"
For strFilter I used Today, Yesterday and reset in the txt file saved at "c:\Buttons\Commands.txt". The code provided by you worked ok, no issues.
"(" & Chr(34) & "https://schemas.microsoft.com/mapi/proptag/0x0042001f" & Chr(34) & " LIKE '%Training Planner (TC)%')"
Hi Diane,
I hope you may help me.
I wanna optimize the "search" ribbon
Because at the opening of Outlook, the field are not well spaced, every time I've to remove the last field (obj) and re-add it to have an acceptable spacing...
Is there a way to write a macro to do this w/ just one click?
Thanks in advance for you help.
Piero
You means the fields in the search results pane are not well spaced? That is controlled with a view.