Searching olFolderSentMail by domain of email address with VBA

Question

I want to find mail in the olFolderSentMail folder, using the domains of the email addresses, with VBA.

When I make an advanced filter in Outlook, the result comes. I got the sql code from there. There is no result in VBA.

For example, I want to find [email protected], [email protected] both together. Only names appear in the whom section in Outlook as a and b. Mail addresses are hidden in names.

Sub sentmails()
 
Dim objNS As Outlook.Namespace: Set objNS = GetNamespace("MAPI") Dim
olFolder As Outlook.MAPIFolder

Set olFolder = objNS.GetDefaultFolder(olFolderSentMail)

filterstr = "@SQL=(""http://schemas.microsoft.com/mapi/proptag/0x0e04001f""
CI_STARTSWITH 'kpmg' OR ""http://schemas.microsoft.com/mapi/proptag/0x0e03001f"" CI_STARTSWITH 'kpmg')"

Set arama = olFolder.Items.Restrict(filterstr)

End Sub

Answer 1

Firstly, you are not searching for a suffix (*kpmg.com), you are searching for a prefix (kpmg*). Secondly, To/CC/BCC properties might not even contain email addresses, only names.

On the Extended MAPI level (C++ or Delphi), one can create a subrestriction on the message recipients, but Outlook Object Model does not expose it.

If using Redemption (I am its author) is an option, you can use a script similar to the one below:

Set Session = CreateObject("Redemption.RDOSession")
Session.MAPIOBJECT = Application.Session.MAPIOBJECT
Set folder = Session.GetDefaultFolder(olFolderSentMail)
set restItems = folder.Items.Restrict("Recipients LIKE '%kpmg.com%' ")
MsgBox restItems.Count