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 a@kpmg.com, b@kpmg.com 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

The question cannot be easily understood which is possibly why there was a downvote. It appears to be about the known issue of SQL success vs VBA fail. See https://stackoverflow.com/questions/55924883/how-to-filter-an-outlook-view-in-vba-based-on-to-email-addresses and https://stackoverflow.com/questions/63413285/search-by-email-address-for-latest-email-in-all-folders-and-reply-all — niton, Nov 11 '20 at 18:00
When I search outlook, the result comes out, I use the sql query in outlook in vba, the result is not coming. — erdem akdemir, Nov 11 '20 at 21:18
Appears I understood the question. The links appear to agree, in this case, the incorrect result is expected with VBA. — niton, Nov 11 '20 at 21:31

Dmitry Streblechenko · Accepted Answer · 2022-05-01T05:08:50.187

0

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

edited May 01 '22 at 05:08

answered Nov 12 '20 at 04:42

Dmitry Streblechenko

62,942
4
53
78

thank you dmitry. I'll try your advice and share the situation here. – erdem akdemir Nov 12 '20 at 07:30
Can't the solution be developed without redemption installation? – erdem akdemir Nov 12 '20 at 14:41
Do you mean without Redemption? Or using Redemption without an installer? In the latter case, the installation is simply copying the dll to any folder and registering it using regsvr32,exe – Dmitry Streblechenko Nov 12 '20 at 15:12
Can you send the dll file to me. Isn't there a solution without redemption? – erdem akdemir Nov 12 '20 at 15:25
You can create a restriction in Extended MAPI, but you can only do that in C++ or Delphi, VB won't let you use Extended MAPI. Redemption can be downloaded from http://www.dimastr.com/redemption/download.htm – Dmitry Streblechenko Nov 12 '20 at 15:42
Hi Dmitry, thank you for your support. It was very useful. Can I get folders in olfolderinbox in a single query. – erdem akdemir Nov 12 '20 at 21:18
What do you mean? Do you mean you need to lop through the subfolders? Use MAPIFolder.Folders collection. – Dmitry Streblechenko Nov 12 '20 at 22:16
If you know the folder name, you can just use it instead of an integer index: `set subFolder = ParentFolder.Folders("The Name")` – Dmitry Streblechenko Nov 13 '20 at 02:49

Searching olFolderSentMail by domain of email address with VBA

1 Answers1