Execute batch script once for multiple emails

Question

I have multiple emails coming in (Each day I get 3 emails for Orders for 3 Categories). The emails subject are in the format:

"ORDERS EXTRACT - [Category] - [Date]".

Where [Category] can be Category 1, Category 2 or Category 3. [Date] is the date the email was sent in the format DD/MM/YYYY.

I have a rule setup to search for 'Orders' then call the below code.

I want to run Complete.bat after all the email attachments have been saved and I only want to call it once.

I've tried to do this by creating another sub called saveAttachtoDisk_CATEGORY1(itm) that only gets called when it finds "Category 1" in the subject. It then saves the attachment but also searches for a category 1 in the subject AND also searches for yesterday date.

I want a better solution that is not date dependent. A global variable could work where I set the variable to be 1 then run Complete.bat is sent and then in future if variable = 1 then don't run Complete.bat. Not sure where to put this variable (Global variable?) As both the sub modules seem the wrong place to put this and reference it.

Both these two modules are saved under the 'Modules' section of Microsoft Outlook VBA.

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim SaveFolder As String
    SaveFolder = "D:\Orders\"
    For Each objAtt In itm.Attachments
         objAtt.SaveAsFile SaveFolder & "\" & objAtt.DisplayName
         objAtt.Delete
    Next
    itm.Save
End Sub

Other module:

Public Sub saveAttachtoDisk_CATEGORY1(itm As Outlook.MailItem)
    Dim objAtt As Outlook.Attachment
    Dim SaveFolder As String
    SaveFolder = "D:\Orders\"
    For Each objAtt In itm.Attachments
        objAtt.SaveAsFile SaveFolder & "\" & objAtt.DisplayName
        objAtt.Delete
    Next
    itm.Save
    If InStr(1, itm.Subject, "ORDERS EXTRACT - Category 1 -" & Format(Date, "dd/mm/yyyy")) Then
        Shell "D:\Orders\Complete.bat"
    End If
End Sub

I would call [SetTimer](http://stackoverflow.com/q/20269844/3959875) with a timeout of say 1 second at the end of the procedure and save its ID with `SaveSetting`, and at the start of the procedure load it and clear the timeout ID. Thus if saving of each item takes less than 1 second only the last item's timeout won't be cleared and your timer callback function will be invoked, where you will call your batch file. — wOxxOm, Dec 04 '15 at 22:14
"I want to run 'Complete.bat' after all the email attachments have been saved and I only want to call it once." Once per mail, or per day? It's not that clear what your real problem is. Can you elaborate a bit? — Tim Williams, Mar 03 '16 at 16:47
Potential issue is that there will be same attachment names. Should you prefix/append Received Date code to the filename - or something make it more unique? Since you remove the attachments from the mail item, there is no trace back on your end and we don't know what the `Complete.bat` does. Also on "Category 1" found and look for yesterday one, then what? — PatricK, Mar 03 '16 at 22:30
@Tim the OP waits for tomorrow to runs the batch file on today's three mails. The question is how to know when all three mails have been received and run immediately "after all the email attachments have been saved and I only want to call it once." — , Mar 03 '16 at 23:16
@Patrick the OP is already successfully running the batch file without complaining about "same attachment names". All that is needed is recognizing when the batch file can be run once. — , Mar 03 '16 at 23:20
Kind of difficult to "answer" this when the OP is not here (?) to address any requests for clarification. — Tim Williams, Mar 04 '16 at 16:36

score 3 · Accepted Answer · answered Mar 05 '16 at 06:25

Assumptions

OP will receive exactly three emails per day (though that is customizable in the code)
The subjects will always begin with "ORDERS EXTRACT -" and no other emails will begin with that code
OP would like to run Complete.bat once per day upon receipt of the third ORDERS EXTRACT email.
OP aready has a rule set up to run SaveAttachtoDisk upon receipt of an ORDERS EXTRACT email. This rule can be changed to run CategorySaveAndComplete
OP is using Outlook 2013 or later

Proposed Solution

The below code will save the attachments for each Orders Extract email and then check to see if all three have been received. I elected not to use .Find and .FindNext as those methods cannot use wildcards and would therefore require hardcoding the category names. I also elected not to use .Restrict as there are only three items for which we are searching.

That said, solutions with .Find and .Restrict would be valid as well and would work better than the below under certain conditions, such as a user with many items consistently in their Inbox.

Please note that the expected count of Orders Extract emails, subject string to match against, and previous dates to check can all be set via constants. I implemented the previous date check in case OP wanted to check each prior day as well.

Option Explicit

Public Const C_ExpectedOrderCount As Integer = 3 'Set number of expected emails for categories
Public Const C_SubjectFormat As String = "ORDERS EXTRACT - *"
Public Const C_PrevDatesToCheck As Integer = 0 'If the Outlook app may not be open every day, set this to the number of prior days the script should also check.

Public Sub CategorySaveAndComplete(itm As Outlook.MailItem)

    'Do not take any action if this is not an ORDERS EXTRACT email.
    If itm.Subject Like C_SubjectFormat Then

        Dim objAtt As Outlook.Attachment
        Dim SaveFolder As String
        SaveFolder = "D:\Orders\"
        For Each objAtt In itm.Attachments
            objAtt.SaveAsFile SaveFolder & "\" & objAtt.DisplayName
            objAtt.Delete
        Next
        itm.Save

        'Check all emails in Inbox for ORDERS EXTRACT - * - DATE
        Dim Item As Object
        Dim objNS As Outlook.NameSpace
        Set objNS = GetNamespace("MAPI")
        Dim olFolder As Outlook.MAPIFolder
        Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
        Dim iLoop As Integer
        Dim iCount As Integer
        Dim DateCheck As Date

        For iLoop = 0 To C_PrevDatesToCheck
            'Reset DateCheck and iCount if we are looping through days
            DateCheck = DateSerial(Year(Date), Month(Date), Day(Date)) - iLoop
            iCount = 0
            'Loop through mail items
            For Each Item In olFolder.Items
                If Item.Class = 43 Then
                    'This is an email. Check if it matches our criteria.
                    If Item.Subject Like C_SubjectFormat And CDate(CLng(Item.ReceivedTime)) = DateCheck Then iCount = iCount + 1
                End If
            Next
            'If we have met the expected targets, then run the batch file.
            If iCount = C_ExpectedOrderCount Then
                'We have exactly the expected number of items. Run the batch file.
                Shell "D:\Orders\Complete.bat"
            ElseIf iCount > C_ExpectedOrderCount Then
                'More items than expected. Check if user is OK with running batch file; if so, run it now.
                If MsgBox("More order extracts than expected were received. Expected " & _
                C_ExpectedOrderCount & "; received " & iCount & " for " & Format(DateCheck, "mmm d, yy") & _
                ". Would you like to run the Complete.bat file now?", vbYesNo) = vbYes Then Shell "D:\Orders\Complete.bat"
            End If
        Next iLoop
    End If
End Sub

Execute batch script once for multiple emails

1 Answers1