VBA only runs if I open the editor or run the script manually

Question

I wrote a small piece of code for Outlook 2016 that takes emails from my Inbox and Sent Items folders and moves them to an archive folder, but even if I use the Application_Startup or Application_Quit methods, the script only works if I open the editor (Alt+F11) or if I run the script manually first (I use a button on the quick access toolbar for it). I can't figure out why only works with those preconditions, even if I don't do anything at all after opening the editor.

This is the code I used:

Private Sub Application_Startup()
    'MsgBox "Hello"
    Call archive
End Sub

Public Sub Application_Quit()
    'MsgBox "Goodbye"
    Call archive
End Sub

Option Explicit
Public Sub archive()
    ' this should run on app startup
    Const MSG_AGE_IN_DAYS = 90

    Dim myFilteredItems As Outlook.Items
    Dim myItem As Object
    Dim myDate As Date
    Dim myNameSpace As Outlook.NameSpace
    Dim myInbox As Outlook.Folder
    Dim mySent As Outlook.Folder
    Dim myDestFolder As Outlook.Folder
    
    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myDestFolder = myInbox.Parent
    Set myDestFolder = myDestFolder.Folders("Archive")
    Set mySent = myInbox.Parent
    Set mySent = mySent.Folders("Sent Items")
   
    myDate = DateAdd("d", -MSG_AGE_IN_DAYS, Now())
    myDate = Format(myDate, "dd/mm/yyyy")
    
    
    Debug.Print "checking " & myInbox.FolderPath
    Debug.Print "for msgs older than " & myDate

    ' you can modify the filter to suit your needs
    Set myFilteredItems = myInbox.Items.Restrict("[Received] <= '" & myDate & "' and [MessageClass] <> 'IPM.Note.SMIME'")
        
    Debug.Print "moving from Inbox " & myFilteredItems.Count & " items"
    If myFilteredItems.Count <> 0 Then
        Set myItem = myFilteredItems.GetFirst
    End If
    
    While myFilteredItems.Count > 0
        Debug.Print "   " & myItem.UnRead & " " & myItem.Subject
                
        myItem.Move myDestFolder
        Set myFilteredItems = myInbox.Items.Restrict("[Received] <= '" & myDate & "' and [MessageClass] <> 'IPM.Note.SMIME'")
        Set myItem = myFilteredItems.GetFirst
    Wend
    
    Set myFilteredItems = mySent.Items.Restrict("[Received] <= '" & myDate & "' and [MessageClass] <> 'IPM.Note.SMIME'")
    Set myDestFolder = myInbox.Parent
    Set myDestFolder = myDestFolder.Folders("Archive Sent Items")
        
    Debug.Print "moving from Sent Items " & myFilteredItems.Count & " items"
    If myFilteredItems.Count <> 0 Then
        Set myItem = myFilteredItems.GetFirst
    End If
    
    While myFilteredItems.Count > 0
        Debug.Print "   " & myItem.UnRead & " " & myItem.Subject
                
        myItem.Move myDestFolder
        Set myFilteredItems = mySent.Items.Restrict("[Received] <= '" & myDate & "' and [MessageClass] <> 'IPM.Note.SMIME'")
        Set myItem = myFilteredItems.GetFirst
    Wend
    
    Debug.Print ". end"

    Set myInbox = Nothing
    Set myFilteredItems = Nothing
    Set myItem = Nothing
End Sub

The script works just fine, even if it is not the most elegant solution, but only if I open the editor or if I run the script manually. If I open the editor and then immediately after I close Outlook, the Application_Quit() method kicks in and works fine, but it does not do anything if the editor was not opened at some point during the session. I played a bit with "public" and "private" on the declaration of the methods, to see if it would change anything, but nothing worked.

Macro settings is set to "Notifications for digitally signed macros, all other macros disabled". I may not change this, because it is set this way through policy on my organization. The script is signed with a self-signing certificate. Before signing the script I was not able to run it at all.

I get no errors at all during any step of these processes.

Could anyone point out why it works the way it does and if I may do anything to modify this behaviour?

Vityata · Answer 1 · 2023-04-10T14:12:26.433

Probably the issue is here - Macro settings is set to "Notifications for digitally signed macros, all other macros disabled", but let's try to debug a bit to confirm. You have already started really well with that commented MsgBox() in the code.

Make your VBA in Outlook look like this (delete everything else):

Just make sure that the code is within ThisOutlookSession. And use the dropdowns, to create the Application_Startup(). (Sometimes, but only sometimes the VBA-editor likes to behave strangely, if the event is pasted and not selected from there.)

Then close Outlook and open it. There should be MsgBox shown. Alternatively, you might have this:
And if so, then you know what to do - enable the macros.

As an alternative, try to sign digitally your macros (probably a better idea) - https://support.microsoft.com/en-us/office/digitally-sign-your-vba-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01

Then work, until you do not have the MsgBox() shown:
Then replace with your code.

score 0 · Answer 2 · answered Apr 10 '23 at 20:37

The script is signed with a self-signing certificate. Before signing the script I was not able to run it at all.

It seems you need to sign your VBA macro with a trusted certificate which comes from the well-known vendor. Or you may try to add a self-signed certificated to the trusted list of certificates, so Outlook could run VBA macros automatically according to the Trust Center settings.

VBA only runs if I open the editor or run the script manually

2 Answers2