Link to home
Start Free TrialLog in
Avatar of Martin Wasiak
Martin Wasiak

asked on

Automation error when executing VBA code within Outlook

I am trying to run a macro to kick off a simple Outlook rule. When I try to run it I get an automation error. Below is the VBA code along with a couple of screenshots showing what happens when I run it.

Sub RunSynology()
Dim st As Outlook.Store
Dim myRules As Outlook.Rules
Dim rl As Outlook.Rule
Dim runrule As String
Dim rulename As String
Dim cf As Folder

rulename = "Synology"

Set st = Application.Session.DefaultStore
Set myRules = st.GetRules
Set cf = Application.ActiveExplorer.CurrentFolder

For Each rl In myRules

If rl.RuleType = olRuleReceive Then
If rl.name = rulename Then
rl.Execute ShowProgress:=True, Folder:=cf
runrule = rl.name
End If
End If
Next

Set rl = Nothing
Set st = Nothing
Set myRules = Nothing

' Display the number of items that were moved.
    MsgBox "Synology emails have been Moved"

End Sub

Open in new window


Every time I run it I get an "automation error" and debug always stops at the 'Next' line after 'End if'.

I have been searching high and low trying to figure out what is going on. I've posted this problem on a few DEV boards but have not had any luck in finding a solution. My hope is that someone here can point me in the right direction because I am ready to lose my mind.

Thanks
automation-error.jpg
debug.jpg
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

Odd indeed. I'm no Outlook VBA expert but any time I've seen automation errors in other Office apps it's been due to the fact that the object in question was locked, usually due to begin in use by another process. Might be worth confirming VBA security settings for Outlook too. When the debugger kicks in, what is the value of myRules.count? Maybe a VBA bug too so what about doing a For X = 1 To myRules.Count instead of the Each method.
Have you tried to checkout the variable status in the watch window with particular reference to r1 and myrules?
Avatar of Martin Wasiak
Martin Wasiak

ASKER

Thank you both for your replies. Unfortunately, I am not a programmer so I am really not sure how to follow your instructions. :(
OK, if you're not a programmer then where did you source the macro from? Perhaps the source included some dependencies (things that are needed to make it work)? I set up a test system with Outlook 2016 (PC) and created two rules, one of which was called "Synology" and your unmodified code looped successfully through both rules without error so it's probably being stopped by some kind of IT policy or lock out based on your environment setup.
If I remember correctly, I found it on slipstick.com. One thing I should add is that I have this macro working on an older Windows 7 machine. I have been migrating over to a Windows 10 (same version of office) and the code is not working. What is interesting and also frustrating is that I tried to run the same code on a brand new Windows 7 test PC and it doesn't work. I have compared the settings within Office/Outlook and the components withing VB and they are identical between the machine where the code works and where it doesn't work. The working and non-working machines are part of a domain and have the same set of group policies applied (we control certain aspects of Office via GPO). This is so frustrating.
Useful input. I assume this forum post on SlipStick is yours! It also looks like you're not the first person to come across the issue:

https://forums.slipstick.com/threads/92493-outlook-2013-vba-to-run-a-rule/

So if you step through your code using F8 once the initial error stops the code, does it work or do you get the same error?

In the above thread, it looks liek it was fixed with a rule correction "nothing wrong with the VBA script it was an error in the rule it was attempting to run..."

So, when the error occurs, open the Immediate Pane in the VBE (Ctrl+G) and type this followed by return:

?rl.name

Open in new window


That should return the name of the current rule in your list. Then check the next rule in Outlook and make sure it runs manually.
Thanks Jamie for this follow-up. Indeed that was my post on slipstick. The other post was dealing with the For loop so I didn't think it was relevant. I did as you suggested and opened the Immediate Pane. Running ?rl.name returned the name of a different rule. What the heck is going on?
I deleted the rule referenced in the Immediate pane and now ?rl.name returns yet another rule name.
Is it possible to start on a test machine with only the Synology rule to eliminate the others as possible reasons why the error is being generated?

Alternatively, you could force the macro to ignore errors (not good programming practice but hey ho, it exists) by adding this at the beginning of your code:

On Error Resume Next ' Disable error handling

Open in new window


That way, if the error is being triggered by a rule that is not the one you're interested in e.g. anything other than the Synology rule, you don't actually care and can safely ignore the error.

Don't forget to reset error handling at the end of your code with this:

On Error Goto 0 ' Enable error handling

Open in new window

I moved the code to the test box. When I included error handling the that code executed very quickly and produced the message box from the end of the code. I don't believe that the rule actually ran because of how quickly the message box popped up. When I removed the error handling code and ran it again, the ?rl.name code again returned the name of a different rule.
After this line:

rl.Execute ShowProgress:=True, Folder:=cf

Open in new window


I've added this debug line which will send the name of the rule it's running to the Immediate Window during run time:

Debug.Print "Executing " & rl.Name

Open in new window


If you see the rule name, it was executed, unless of course there was an error which we've just masked so we could re-enable the error handling just at that point like this:

On Error Goto 0
rl.Execute ShowProgress:=True, Folder:=cf
On Error Resume Next

Open in new window


Regarding the message window you see at the end, the original code on SlipStick output the names of the rules executed so in your code, it's not actually telling the truth anymore (and there is no counter implemented). I changed that too:

Sub RunSynology()
On Error Resume Next

Dim st As Outlook.Store
Dim myRules As Outlook.Rules
Dim rl As Outlook.Rule
Dim runrule As String
Dim rulename As String
Dim cf As Folder
Dim RuleRun As Boolean

rulename = "Synology"

Set st = Application.Session.DefaultStore
Set myRules = st.GetRules
Set cf = Application.ActiveExplorer.CurrentFolder

For Each rl In myRules
  If rl.RuleType = olRuleReceive Then
    If rl.name = rulename Then
      On Error Goto 0
      Debug.Print "Executing " & rl.Name
      rl.Execute ShowProgress:=True, Folder:=cf
      On Error Resume Next
      runrule = rl.name
    End If
  End If
Next

Set rl = Nothing
Set st = Nothing
Set myRules = Nothing

' Display a message if a rule was run
If runrule <> "" Then MsgBox runrule & " rule was executed."

On Error Goto 0
End Sub

Open in new window

And to answer your question about the next rule name, that is expected at the Next line and I was suspecting if the error was coming from an issue with the rule following the Synology one.
Thank you so much for providing the updated code. When I run it as you provided it, nothing happens and I don't see the Immediate Window.
"I don't see the Immediate Window" - you need to enable the Immediate Window with Ctrl+G or did you mean to see you don't see anything in the Immediate Window?
I do not see anything in the window.
I have an update...

On a whim, I moved the Synology rule to the top of my rule list (first rule in the list) and it ran. It still had the error handling code in it so no errors were produced. I then commented out the error handling code and it ran again. This run produced the same error but at the end of the run. I then moved another rule to the top of the list and the behavior was identical. I did it the third time and got the same result.

could there be something wrong with rule enumeration?
ASKER CERTIFIED SOLUTION
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It looks like you want a macro that executes a specific rule called Synology.

Correct. I currently have several macros that run either a single rule or multiple rules. I use macros in order to be able to assign them to custom buttons.

Your new code is working fine but only if I move the rule to the top of the rule list. It seems that I only have to move the rule once in order to make ti work via the macro. If I don't move the rule, it fails to run and the debugger stops on the second line. At this point I don't think there is anything wrong with the code, It's something with the way Outlook is enumerating the rules. Perhaps there is also some kind of a security issue in play here. So frustrating...

I will have to move the rest of the rules around to see if they run.

I don't want to take up any more of your time. I very much appreciate your assistance.

Thanks

Martin
Well, I fixed the problem. As I turned out there was nothing wrong with the code. The problem was with the rules. After digging around I found that my rule repository had three completely blank rules. I used Sperry Software's Power Rules Manager to figure it out (Outlook did not show those rules). Seems to me that there was some sort of a corruption within the rule set. The fix was to delete them all and recreate them from scratch. I have recreated about 60 out of the 100 rules and so far everything is working fine. I changed all my code to the simplified one you provided and that has actually made things a lot more manageable, especially for the macros that run several rules.

Thank you for all your help. :)
That's great news and it tallies with the earlier comment that erroneous rules can upset the VBA. Feel free to close the question.
The problem turned out to be some sort of corruption in my rule set. Jamie's simplified code helped me troubleshoot the issue and also streamlined my code going forward.

Thank you :)