Outlook VBA - Detect when inbox is empty? Run Macro when inbox is empty?

Question

I have some outlook VBA code for work that will automatically allocate my team members one email at a time to respond to from our customers, and as it does this it also scans for and gives them any emails that have come in later from the same email address, so the customer can be dealt with in one go.

I want this to run when their own folder becomes empty (i.e. they've dealt with one client, and it automatically runs the above to allocate them another when they move the current mail to an archive, leaving their main inbox folder empty).

Is there any way to do this? I know I can set the macro to check for it every 5 mins, but this will slow Outlook down massively. Any way to trigger the macro only when the user's folder is emptied?

Cheers

Chris

David Rushton · Accepted Answer · 2016-07-14 13:46:51Z

0

Events are perfect for this.

Events are triggered by the application, when key changes are made. This allows you to avoid using a timed loop.

You can use the WithEvents statement to create a variable that can handle event calls.

In this example the variable f points to the inbox. Whenever an item is deleted from this folder the f_BeforeItemMove procedure is called. It displays the number of items left, minus one. We subtract one because the event is fired before the deletion (this gives you a chance to cancel it, should you wish).

Because we are using an object variable we need to create and destroy it. This occurs when the app is started & exited.

Private WithEvents f As Folder          ' Inbox folder, used to monitor events.


Private Sub Application_Startup()
' Register for events.

    Set f = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
End Sub

Private Sub Application_Quit()
' Unregister.

    Set f = Nothing
End Sub



Private Sub f_BeforeItemMove(ByVal Item As Object, ByVal MoveTo As MAPIFolder, Cancel As Boolean)
' Called when an item is moved out of the inbox.

    ' Display the number of items left, after delete.
    MsgBox (f.Items.Count - 1)
End Sub

This code must be added to the ThisOutlookSession class. The startup and quit events will not fire if pasted into another module.

EDIT

The original solution, above, was trigger just before an item was deleted from the inbox. The OP wanted code that fired just after. This new solution does that.

Private WithEvents f As Folder          ' Inbox folder, used to monitor events.
Private WithEvents i As Items           ' Items within folder above


Private Sub Application_Startup()
' Register for events.

    Set f = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set i = f.Items
End Sub

Private Sub Application_Quit()
' Unregister.

    Set i = Nothing
    Set f = Nothing
End Sub

Private Sub i_ItemRemove()
' Called each time an item is moved out of the inbox.
' This can be triggered by moving an item to another folder
' or deleting it.

    ' Display the new inbox item count.
    MsgBox i.Count
End Sub

As before; this code should be placed inside ThisOutlookSession. You will need to restart Outlook, or manually execute Application_Startup.

edited Jul 14, 2016 at 13:46

answered Jul 8, 2016 at 9:10

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Chris Wakefield Over a year ago

Thanks for your reply. I've tried this and unfortunately it doesn't fire anything. I've placed the code in the ThisOutlookSession and pointed it to the folder I want to monitor, but it's not doing anything when I delete or move emails out of the inbox.

David Rushton Over a year ago

Sorry I should have added; to get this code working you will need to either manually run Application_Startup or close and reopen Outlook. To manually run just click inside the sub and press F5. Why? Because the f object variable needs to be created, via the SET statement. Once that is done it will start responding to events.

Chris Wakefield Over a year ago

This works great now that I've restarted Outlook. Thanks very much. One question though. I need this to run after the mail item has been moved, not just before, as my next code will check the mailbox count and only run if it equals 0. As this runs before hand, it thinks there is still an email in the box and my next part doesn't run.

David Rushton Over a year ago

You do this using the ItemRemoved event of the items object. Next time I'm on my laptop I'll update my answer with an example.

Chris Wakefield Over a year ago

Thanks very much. I will have a play around but if you could, that's be great.

|

Eric Legault · Accepted Answer · 2016-07-08 18:25:56Z

0

You can trap the Items.ItemRemove event to monitor for when the Items.Count property evaluates to 0. Call the Initialize_handler() method during the Application_Startup() event:

Public WithEvents myOlItems As Outlook.Items 
           
Public Sub Initialize_handler() 
 
 Set myOlItems = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items 
 
End Sub           
 
Private Sub myOlItems_ItemRemove() 
 
 If myOlItems.Count = 0 Then
    'Inbox is empty!
 End If
  
End Sub

answered Jul 8, 2016 at 18:25

Eric Legault

5,8743 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

Outlook VBA - Detect when inbox is empty? Run Macro when inbox is empty?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related