Run a script is running on old mail before new email moved to the target folder

Question

I created a rule to move a daily email to a specific folder and run a VBA script to save the table from this email's body.

When the email is received, VBA starts running and grabbing previous email with the same subject and only after does the new email appear in my target folder.

I tried sleep.

Is there any way to first move new email to a target folder then run a script?

Sub ExportOutlookTableToExcel()`

    Dim oLookInspector As Inspector
    Dim oLookMailitem As MailItem

    Dim oLookWordDoc As Word.Document
    Dim oLookWordTbl As Word.Table

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook 
    Dim xlWrkSheet As Excel.Worksheet

    'Grab Email Item
     Set oLookMailitem =Application.ActiveExplorer.CurrentFolder.Items("Apples Sales")

     Set oLookInspector = oLookMailitem.GetInspector

     Set oLookWordDoc = oLookInspector.WordEditor

Does this answer your question? [Pause an outlook vba code for 5-10 seconds and after run the script](https://stackoverflow.com/questions/70669224/pause-an-outlook-vba-code-for-5-10-seconds-and-after-run-the-script) — Eugene Astafiev, Jan 11 '22 at 16:38
Because Sleep doesn't work. The question is saying is it anyway to move email first and only after run vba script. Even with sleep command in vba, the fact is that macro is running already and email is not transferred yet to a target folder, so the script grabs previous email. VBA starts to runbefore outlook recognize this new email even in inbox folder — Camilla, Jan 11 '22 at 17:11
Same issue,when I add move this specific email to another folder, vba shows an error that this email is not found in inbox folder, because for some reason macro starts to run and only after this email appeared in inbox(Run script is added to this email rule). The whole issue is that I can't find a solution to run a script as a part of this email rule only after outlook recognizes this email. I turn off the rule and this email is received without any rules into my inbox folder, i run macro manually then it works, but once i add this script to a rule, macro runs before outlook shows this email — Camilla, Jan 11 '22 at 18:35
Your workflow is too much complicated. There is no need to create a rule, handle new items in a folder and etc. — Eugene Astafiev, Jan 11 '22 at 21:30
Well I need to extract tables automatically from the email once it received, it would be nice to let it run on itself as an outlook rule, instead of running vba code manually, because sometimes you omit a particular email, because of receiving over 200 emails daily — Camilla, Jan 11 '22 at 21:59

niton · Answer 1 · 2022-01-11T22:29:32.927

Re: I created a rule to move this email to a specific folder and run a VBA script
You are not the first to fall into this trap. Put the move as the last action in the code.
Consider not using "run a script" code in rules. There is ItemAdd for any folder or NewMailEx for the Inbox.

Re: Set oLookMailitem =Application.ActiveExplorer.CurrentFolder.Items("Apples Sales")
The most recent mail with subject "Apples Sales" can be found like this:

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Sub mostRecentlyReceivedMail_Subject_DemoOnly()

Dim oLookFolder As Folder
Dim oLookFolderItems As Items

Dim srchSubject As String
Dim i As Long

Dim oLookMailitem As MailItem

Set oLookFolder = ActiveExplorer.CurrentFolder
Set oLookFolderItems = oLookFolder.Items

' sort the collection not the folder
oLookFolderItems.Sort "[ReceivedTime]", True

srchSubject = "Apples Sales"

' This is demonstration code only.
' Without introducing methods to reduce the number of items to look through
'  it shows the use of an index rather than subject.
' In this case the required item is supposed to be first in the collection.
For i = 1 To oLookFolderItems.Count

    ' first verify object in folder is a mailitem
    If oLookFolderItems(i).Class = olMail Then
    
        ' Index not subject
        Set oLookMailitem = oLookFolderItems(i)
        
        If oLookMailitem.subject = srchSubject Then
            Debug.Print oLookMailitem.ReceivedTime
            oLookMailitem.Display
            Exit For
        End If
    End If
    
Next

End Sub

Although subject is valid in

Set oLookMailitem =Application.ActiveExplorer.CurrentFolder.Items("Apples Sales")

it probably has little to no practical use.

Bad example. Iterating over all items in a folder is not really a good idea. — Eugene Astafiev, Jan 11 '22 at 20:52

score 0 · Answer 2 · answered Jan 11 '22 at 21:29

I creted a rule to move this email to a specific folder and run a vba script to save the table from this new emails body.

There is no need to create a rule and run a VBA script. Instead, to handle incoming emails immediately you need to handle the NewMailEx event which is fired when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. You should not assume that after these events fire, you will always get a one-item increase in the number of items in the Inbox. Also you may consider handling the ItemAdd event on the folder where your items are moved. But it has a known disadvantage - the event is not fired if more than sixteen items are moved at the same time. This is a known issue when dealing with OOM.

In the NewMailEx event handler you may get an instance of the incoming email and move it to the required folder programmatically where you could run any other actions.

Run a script is running on old mail before new email moved to the target folder

2 Answers2