VBOffice

Find Emails of The Same Topic

Use VBA to search for all emails of the same topic.

Last modified: 2017/01/26 | Accessed: 8.559  | #153
◀ Previous sample Next sample ▶

Content

Reporter Reporter
VBOffice Reporter is an easy to use tool for data analysis and reporting in Outlook. A single click, for instance, allows you to see the number of hours planned for meetings the next month.

Search Subject

By a right click on an email, then 'Find All' you can search for emails of the same sender, or the same topic. This sample demonstrates how to build your own search by VBA. See the variable 'ViewName' where you can enter any name for the new view.


tip  How to add macros to Outlook
Public Sub SubjectFilter()
  Dim Folder As Outlook.MAPIFolder
  Dim obj As Object
  Dim Mail As Outlook.MailItem
  Dim View As Outlook.View
  Dim Find As String, DASL As String
  Dim Filter As String
  Dim ViewName As String
  Dim Created As Boolean
  
  'Name of your custom view
  ViewName = "My-View"
  
  Set obj = Application.ActiveExplorer.Selection(1)
  
  If Not TypeOf obj Is Outlook.MailItem Then
    'this sample is for emails
    Exit Sub
  End If
  
  Set Mail = obj
  Set Folder = Mail.Parent
  
  '[Property] = [Value]
  Filter = "#0 = '#1'"
  
  'DASL-Name of the property we want to search
  DASL = "http://schemas.microsoft.com/mapi/proptag/0x0037001F"
  
  'Value to search for
  Find = Mail.Subject
  
  'Find or create the view
  Set View = Folder.CurrentView
  If LCase$(View.Name) <> LCase$(ViewName) Then
    Set View = Folder.Views(ViewName)
    If View Is Nothing Then
      Set View = Folder.Views.Add(ViewName, olTableView, olViewSaveOptionAllFoldersOfType)
      Created = True
    End If
  End If
  
  'Apply filter 
  DASL = Chr(34) & DASL & Chr(34)
  Filter = Replace(Filter, "#0", DASL)
  Filter = "(" & Filter & ")"
  Filter = Replace(Filter, "#1", Find)
  
  View.Filter = Filter
  View.Apply
  If Created Then View.Save
End Sub
OLKeeper OLKeeper
OLKeeper reliably prevents users from closing their Outlook window and thus possibly missing reminders or e-mails.

Search For a Substring

This sample does almost the same but instead of searching for the subject of the selected email it looks for a substring in the current folder.

Public Sub SubjectFilter2()
  Dim Folder As Outlook.MAPIFolder
  Dim View As Outlook.View
  Dim Find As String, Dasl As String
  Dim Filter As String
  Dim ViewName As String
  Dim Created As Boolean
  
  'Name of your custom view
  ViewName = "My-View"
  
  Set Folder = Application.ActiveExplorer.CurrentFolder
  
  '[Property] = [Value]
  Filter = "#0 like '%#1%'"
  
  'DASL-Name of the property we want to search
  Dasl = "http://schemas.microsoft.com/mapi/proptag/0x0037001F"
  
  'Value to search for
  Find = InputBox("Find:")
  
  'Find or create the view
  Set View = Folder.CurrentView
  If LCase$(View.Name) <> LCase$(ViewName) Then
    Set View = Folder.Views(ViewName)
    If View Is Nothing Then
      Set View = Folder.Views.Add(ViewName, olTableView, olViewSaveOptionAllFoldersOfType)
      Created = True
    End If
  End If
  
  'Apply filter
  Dasl = Chr(34) & Dasl & Chr(34)
  Filter = Replace(Filter, "#0", Dasl)
  Filter = "(" & Filter & ")"
  Filter = Replace(Filter, "#1", Find)
  
  View.Filter = Filter
  View.Apply
  If Created Then View.Save
End Sub
ReplyAll ReplyAll
ReplyAll alerts you before unintentionally replying all, or if you are a confidential BCC recipient of the e-mail.

Remove Filter

This removes quickly the current filter:

Public Sub RemoveFilter()
  Dim Folder As Outlook.MAPIFolder
  Dim View As Outlook.View
  
  Set Folder = Application.ActiveExplorer.CurrentFolder
  Set View = Folder.CurrentView
  View.Filter = ""
  View.Apply
End Sub
OLKeeper OLKeeper
OLKeeper reliably prevents users from closing their Outlook window and thus possibly missing reminders or e-mails.
email  Send a message