Outlook

[Pat Hartman]

@Gasman Sorry, got busy. I just tried the code and can't get it to compile. I added the Outlook library but it hangs on the "Session" with - Method or data member not found. Is there some other library I need to reference?

 

[Gasman]

Pat, That code is in the Outlook program, not Access.

I have just noticed that Outlook.rules does not get capitalised, but that code does work, as I have shown some of the results.

 

[Pat Hartman]

Thanks, that may help a little. Do you have any idea how to include the actual "from" email address? The rules are all consistent and based on the sender rather than anything in the contents. I don't get intellisense for the Actions and nothing at the rule level sounds right. Meanwhile, I'll work on exporting the list to a file where I can search.

 

[Gasman]

I would have to dig deeper.

 

[Gasman]

Try adding to the debug.print

olRules.item(iRule).Conditions.From.Recipients.Item(1).Address

so it becomes

            Debug.Print "Rule: " & olRules.item(iRule).Name & strTab & "Folder: " & olRules.item(iRule).Actions(iAction).Folder.FolderPath & strTab & "Sender " & olRules.item(iRule).Conditions.From.Recipients.item(1).Address

It does fail in some condtions as not all have a From, so just use On Error Resume Next

That will give

Rule: New Voicemail                             Folder: \\Personal Folders\Inbox\Voicemail                              Sender voicemail@voicemailserver.org
Rule: Swansea FreeCycle                             Folder: \\Personal Folders\Inbox\Gmail\Freecycle                                Sender digest-21603607-3573@bounces.freecycle.org
Rule: Facebook                              Folder: \\Personal Folders\Inbox\Facebook                               Sender notification@facebookmail.com
Rule: Camera1                               Folder: \\Personal Folders\Inbox\Camra                              Sender events@camra.org.uk

 

[Pat Hartman]

Thanks. That may be enough to be able to get rid of the rules where I want nothing from the company at all. Turns out to be 306 of them. Here's the procedure with the export to .csv code included.

Sub TestRule()

''From Gasman https://www.access-programmers.co.uk/forums/threads/outlook.332069/#post-1934352
''modified by Pat Hartman to export to csv

Dim olRules As Outlook.Rules
Dim olRule As Outlook.Rule
Dim iRule As Integer, iAction As Integer, iException As Integer, iRuleAction As Integer
Dim oAction As Object
'''
Dim CountItems As Integer
Dim FileName As String
Dim FileNumber As Long
Dim strRecord As String
Dim strPrint    As String

On Error GoTo ErrProc

Set olRules = Application.Session.DefaultStore.GetRules
'Set olRule = olRules.item("TestRule")

CountItems = 0
FileName = "C:\Data\UsefulDatabases\Collections\OutlookRules.csv"
FileNumber = FreeFile()     'get next available file number
Open FileName For Output As FileNumber      'Open output file
strRecord = "SeqNum, Rule, Folder, From"
Print #FileNumber, strRecord     ' print column names as first record

For iRule = 1 To olRules.Count 'Each olRule In olRules

'    Debug.Print olRules.item(iRule).Name 'TypeName(iRule)
'    Debug.Print olRules.item(iRule).Enabled
'    Debug.Print olRules.item(iRule).Actions.Count
    For iAction = 1 To olRules.Item(iRule).Actions.Count
        'Set oAction = olRules.item(iRule).Action
        If olRules.Item(iRule).Actions(iAction).Enabled = True And olRules.Item(iRule).Actions(iAction).ActionType = 1 Then
            strPrint = "Rule: " & olRules.Item(iRule).Name & vbTab & vbTab & vbTab & vbTab & "Folder: " & olRules.Item(iRule).Actions(iAction).Folder.FolderPath
            strPrint = strPrint & "Sender " & olRules.Item(iRule).Conditions.From.Recipients.Item(1).Address
            Debug.Print strPrint
            CountItems = CountItems + 1
            strRecord = CountItems & "," & olRules.Item(iRule).Name & "," & olRules.Item(iRule).Actions(iAction).Folder.FolderPath & "," & olRules.Item(iRule).Conditions.From.Recipients.Item(1).Address
            Print #FileNumber, strRecord
        End If
        'Debug.Print "ActionType: " & olRules.item(iRule).RuleActions.item(iAction)
    Next
    'Debug.Print olRules.item(iRule).Exceptions.Count
    'printArray olRules(iRule).Conditions.Body.Text
    'printArray olRules(iRule).Conditions.MessageHeader.Text
Next

ExitProc:
    Debug.Print "Count = " & CountItems
    Close #FileNumber
  
Set oAction = Nothing
Set olRules = Nothing
Set olRule = Nothing
Exit Sub

ErrProc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Next
    End Select
End Sub

What is your source for finding the properties. I'm not getting intellisense below the Item(iRule) level?
Thanks a bunch for all your help.

 

[The_Doc_Man]

Pat, you can go into your VBA window, be sure that Outlook is checked as a desired library, and then open up Object Browser and tell it to look in Outlook. You can see a Rules object that looks like it contains a list of rules as an Item array. Which means that if you could put a breakpoint in a place where you can see the Outlook Rules you could open up the Locals window and browse for the sub-structure.

You also have enumerations such as OlRuleActionType, OlRuleConditionType, OlRuleExecuteOption, and OlRuleType, so it would be possible (with a little tedium) to build something that would look at various rules with essentially a big set of Case ladders to decipher some of this stuff. If I weren't busy with my home concrete driveway replacement project right now (having nothing whatsoever to do with Access), I would try that. But everything you need to see about the structures is there.

 

[Gasman]

Pat, you can go into your VBA window, be sure that Outlook is checked as a desired library, and then open up Object Browser and tell it to look in Outlook. You can see a Rules object that looks like it contains a list of rules as an Item array. Which means that if you could put a breakpoint in a place where you can see the Outlook Rules you could open up the Locals window and browse for the sub-structure.

You also have enumerations such as OlRuleActionType, OlRuleConditionType, OlRuleExecuteOption, and OlRuleType, so it would be possible (with a little tedium) to build something that would look at various rules with essentially a big set of Case ladders to decipher some of this stuff. If I weren't busy with my home concrete driveway replacement project right now (having nothing whatsoever to do with Access), I would try that. But everything you need to see about the structures is there.

@The_Doc_Man That is what I did.
@Pat Hartman You might need to add extra action types to the check?

OlRuleActionType enumeration (Outlook)

Office VBA reference topic

learn.microsoft.com

I only used olRuleActionMoveToFolder as that is what I tend to use.

 

[Gasman]

What is your source for finding the properties. I'm not getting intellisense below the Item(iRule) level?

As mentioned by Doc.
As I have previously mentioned, there appears to be Actions, Conditions and Exceptions within each Rule.
They likely correspond with each checkbox in the rule manager lists?

First a rule must be enabled. I only checked for them. If you have any unticked, then they would not be included, but as you were trying to find a particular rule due to an email being received, I assumed that rule was enabled?
Then you have Conditions which are enabled or not, and then Exceptions working the same.

So if you think about it, it is structured as you would use the RM interface.
Multiple Actions/Conditions/Exceptions for each rule.

Took me a while to get my head around it all.

 

[Pat Hartman]

I looked at the data I get from the code. I find one reference to Express Scripts and it sends to the input folder for some reason. I looked at all three columns to see if I could find something that moved "express" to bulk and there was nothing so there seems to be some rogue rule. I'll try adding more columns.

Thanks again.

 

[Gasman]

Pat,
Check the Enums and add the one for CopyToFolder (5). As I mentioned I only used MoveToFolder (1), as that is what I tend to use.