Send an alert email based on incoming outlook email using VBA

Question

If told how, I can attach the emails that I talk about in this question.

I need to monitor my outlook inbox for two specific emails. One arrives every night at 7pm. The other arrives at 7:20pm (give or take a couple minutes either side).

The first email subject:"Automatic1 08092013". The word Automatic1 is always the same and the numbers represent the date.

The second email subject:"Automatic2 - Aug 09, 2013". The Automatic2 is always the same but the date changes.

The email body of Automatic1 contains a string with a value that represents currency.

Example of 7pm EmailBody: CustomerCount: 11 VisitorNumber: 121 Amount: 811070

I need CustomerCount, VisitorNumber and the Amount: 811070 compared to the value that comes in the second email. The amount, “811070” represents a currency value equal to $8,110.70.

After the 7:20 email arrives if the values do not match send alert email to me.

This is the tricky part. The 7:20pm email has values contained in more than one table.

Example of 7:20pm EmailBody:

(This is table #1 it contains 2 rows and 2 columns)

Process1 Totals (This is string text that appears in the email body above the first table)

Count1...Amount1

11..........8110.70

(This is table #2 it contains 3 rows and two columns)

Process2 Totals (This is string text that appears in the email body above the second table)

Count2 ..Amount2

121........811070

(This is table #3 it contains a bunch of stuff I don't care about)

Breakdown

ID Number Amt ID2

296 15 737.33 0000113821

079 1 737.33 000938

34 1 737.33 0007000

746 10 737.33 0056200

741 8 737.33 0009733

089 6 737.33 0034664

636 1 737.33 007500

07 71 737.33 0000976

296 14 737.33 0023231

34 51 737.33 0000100

788 23 737.33 7100

Scenerios:

If 7:20 email arrives and 7pm email does not exist (from the same date), send email alert with "Warning Text".

If 7:20 email arrives and 7Pm email does exist (important: from the same date), compare

CustomerCount: 11

With 7:20pm email Table 1 Count1 value contained in Column 1 Row 2

Count1 Amount1 11 8110.70

If they don't match send email with "CustomerCount not equal"

Also, Compare 7pm VisitorNumber: 121

With Table 2 Count2 Value contained in Column 1 Row 2

Count2 Amount2 121 811070

If they don't match send email with "VisitorNumber not equal"

Also, Compare 7pm Amount: 811070

With Table2 Amount2 (see above) value contained in Column 2 Row 2

If they don't match, send email with "Amounts don't match"

Each of the emails should contain the actual math involved in determining there was a problem. Each email should contain all the figures. When it comes to the currency there should be a diff between the two included in the email body. Doesn't matter if the value returns a positive or negative number. I don't have to have a separate email for each issue. Just one email will do as long as it contains all the compares and difference with currency. No email will be sent at all if the email at 7:20 does not come in (that's okay) and/or the numbers all match up and there's no diff.

Example:

Subject: Alert

Email body: "Nightly Totals do not match"

Your Customer Count = 11

Original Recipients Customer Count = 12

Known problems:

The cells in the 7:20pm email contains spaces for some reason.

The 7pm email is one long string.

My vision of how it should work.

The inbox needs to be monitored for the subject line of the 7:20 email.

When the 7:20 email comes in search my inbox for the 7pm email, search by subject.

If the 7pm email exists then get the values from each of the emails, compare and decide if an alert email needs to be sent.(Way over simplified)

I would prefer not to involve excel but understand if it's needed.

Answer 1

I presume you are asking for Outlook VBA codes. This sounds like something possible as I wrote myself Outlook VBA to do basic Active Directory operations to Users (unlock, change/reset password) - I send email to myself with username and instruction then seconds later the outcome will be emailed to me.

Note that even with completed VBA codes, you need to setup Outlook Rules to run the VBA code and you must have an Outlook session open as it cannot be done in server. This also means you need a computer running Outlook 24/7 to accomplish this.

It won't be possible to produce complete VBA code if no actual .msg files can be tested.

But something to get you started... (have not completely tested)

Private Const sLookUp = "Automatic1 "

Public Sub Rules_Automatic2(oMailAuto2 As MailItem)
    Dim sDate As Date, oMailAuto1 As MailItem, sSubject As String, sBody As String

    ' Exit if "Automatic2" is NOT the first word in subject
    If InStr(1, Left(oMailAuto2.Subject, 10), "Automatic2", vbTextCompare) = 0 Then Exit Sub
    ' Get the date value in subject and get the corresponding Automatic1 mail item
    sDate = DateValue(Split(oMailAuto2.Subject, "-"))
    oMailAuto1 = GetAuto1Email(sLookUp & Format(sDate, "mmddyyyy"))

    If oMailAuto1 Is Nothing Then
        ' corresponding Automatic1 email not found
        sSubject = "Warning Text"
        sBody = "Corresponding email for """ & oMailAuto2.Subject & """ is not found!"
        SendEmail sSubject, sBody
    Else
        CompareAutomatics oMailAuto2, oMailAuto1
    End If
End Sub

Private Function GetAuto1Email(sTxt As String) As MailItem
    Dim oOlkFDR As Outlook.Folder, oMail As MailItem, oMailAuto1 As MailItem

    Set oMailAuto1 = Nothing
    Set oOlkFDR = Application.Session.GetDefaultFolder(olFolderInbox)
    For Each oMail In oOlkFDR.Items
        If InStr(1, oMail.Subject, sTxt, vbTextCompare) Then
            Set oMailAuto1 = oMail
            Exit For
        End If
    Next
    GetAuto1Email = oMailAuto1
End Function

Private Sub CompareAutomatics(oMailAuto2 As MailItem, oMailAuto1 As MailItem)
    Dim sBody2 As String, sBody1 As String, sSubject As String, sReply As String

    sBody2 = oMailAuto2.Body
    sBody1 = oMailAuto1.Body
    sSubject = ""
    sReply = ""
    ' Do Comparisons and setup email body and subject
    ' ...
    ' ...
    SendEmail sSubject, sReply
End Sub

Private Sub SendEmail(sSubject As String, sBody As String)
    Dim oMail As MailItem

    Set oMail = Application.CreateItem(olMailItem)
    With oMail
        .Subject = sSubject
        .BodyFormat = olFormatPlain
        .Body = sBody
        .Send
    End With
    Set oMail = Nothing
End Sub

Hope you get the idea of how things works in Outlook VBA...