CODEWITHSUNDEEP
vbaoutlook
Camilla
Camilla

Reputation: 131

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

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

Upvotes: 0

Views: 209

Answers (2)

niton
niton

Reputation: 9179

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.

Upvotes: 1

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

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.

Upvotes: 0

Related Questions