asked on

How to link to MS Access to Outlook Custom Search Box

Hello Team

I have a MS Access database that links to multiple outlook folders.

One of the folders that I would like to link to is a custom search folder (Emails Per Day) that captures and counts how many emails are received into my mailbox each day.

When attempting to link my MS Access database, I can't find the custom search folder I created.

I am thinking that it may not be possible to link to it as it is not visible on the Import\Exchange Outlook Wizard in MS Access.

Can you please advise if it is not possible to directly link via the wizard, is there anyway around this, e.g., VBA?

Thanks in advance to any responses received.

Sincerely

Dale

Daniel Pineault

Here's a sample that should get you going

' EnumerateSearchFolderItems "Emails Per Day"
Sub EnumerateSearchFolderItems(ByVal sSearchFolderName As String)
    Dim oOutlook              As Object    'Outlook.Application
    Dim oNameSpace            As Object    'Outlook.Namespace
    Dim oStores               As Object    'Outlook.Stores
    Dim oStore                As Object    'Outlook.Store
    Dim oSearchFolders        As Object    'Outlook.folders
    Dim oFolder               As Object    'Outlook.folder

    On Error Resume Next

    Set oOutlook = GetObject(, "Outlook.Application")
    Set oNameSpace = oOutlook.GetNamespace("MAPI")
    Set oStores = oNameSpace.Session.Stores
    For Each oStore In oStores
        Set oSearchFolders = oStore.GetSearchFolders
        For Each oFolder In oSearchFolders
            If oFolder.Name = sSearchFolderName Then
                With oFolder
                    For i = 1 To .Items.Count
                        Debug.Print .Items.Item(i).SenderName, .Items.Item(i).Subject, .Items.Item(i).ReceivedTime, .Items.Item(i).Categories
                    Next i
                End With
            End If
        Next
    Next

    If Not oFolder Is Nothing Then Set oFolder = Nothing
    If Not oSearchFolders Is Nothing Then Set oSearchFolders = Nothing
    If Not oStore Is Nothing Then Set oStore = Nothing
    If Not oStores Is Nothing Then Set oStores = Nothing
    If Not oNameSpace Is Nothing Then Set oNameSpace = Nothing
    If Not oOutlook Is Nothing Then Set oOutlook = Nothing
End Sub

Open in new window

or even better

' EnumerateSearchFolderItems "Emails Per Day"
Sub EnumerateSearchFolderItems(ByVal sSearchFolderName As String)
    Dim oOutlook              As Object    'Outlook.Application
    Dim oNameSpace            As Object    'Outlook.Namespace
    Dim oStores               As Object    'Outlook.Stores
    Dim oStore                As Object    'Outlook.Store
    Dim oFolder               As Object    'Outlook.folder

    On Error Resume Next

    Set oOutlook = GetObject(, "Outlook.Application")
    Set oNameSpace = oOutlook.GetNamespace("MAPI")
    Set oStores = oNameSpace.Session.Stores
    For Each oStore In oStores
        Set oFolder = oStore.GetSearchFolders.Item(sSearchFolderName)
        If Not oFolder Is Nothing Then
            With oFolder.items
                For i = 1 To .Count
                    Debug.Print i, .Item(i).SenderName, .Item(i).Subject, .Item(i).ReceivedTime, .Item(i).Categories
                Next i
            End With
            Exit For
        End If
    Next

    If Not oFolder Is Nothing Then Set oFolder = Nothing
    If Not oStore Is Nothing Then Set oStore = Nothing
    If Not oStores Is Nothing Then Set oStores = Nothing
    If Not oNameSpace Is Nothing Then Set oNameSpace = Nothing
    If Not oOutlook Is Nothing Then Set oOutlook = Nothing
End Sub

Open in new window

Dale James

ASKER

Hello Daniel

Thank you very much for providing the above code.

I placed the code in an Access module with the code to be triggered by a Command Click. When the code is triggered the Complie Error messages appears stating, Argument Not Optional.

I see that there is an argument sSearchFolderName, but I am not sure what I should be providing to this argument, can you please advise?

Sincerely

Dale.

Daniel Pineault

EnumerateSearchFolderItems "Emails Per Day"

assuming "Emails Per Day" is the name of your Search Folder.

Don't forget, my sub just prints a few properties to the VBE immediate window. You'll need to customize it to do what you want. If all you want is the count of items within the folder then you only need to extract the .Count value.

Dale James

ASKER

Thanks again Daniel..

Still getting the compile error so am obviously not applying the code correctly.

So just to recap to see where I am going wrong here.

I have created a custom search folder called "Emails Per Day". Placed the provided code in an Access module and linked it to be triggered by a command button with a click event.

Still not sure how I am to pass the required argument.

My apologies for requesting further assistance.

Dale

ASKER CERTIFIED SOLUTION

Daniel Pineault

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale James

ASKER

Hello Daniel

All is working perfect now.

Thank you so much for all your assistance and patience with this initial request.

Sincerely

Dale

Daniel Pineault

Glad to hear and my pleasure.

Dale James

ASKER

Thank you once again.