Search for question Text in incoming email then display message box on response

[pjawynn]

Good Afternoon, would someone be able to help with a VBA question I have concerning a outlook mail item. We receive a automated mail from our online store which includes purchases for items. One of the questions is "Are you a resident in a halls of residence?:" to which the response is either "Yes" or "No" defined by the website. I need for the macro to identify if the response to the question is "Yes" then a message box appears asking if the user wants to proceed.
I have been working on the following code and tweaking it, but every time it just comes up with the response message box "Did not Work", yet if I trim the response into an excel cell "Yes" is displayed correctly.

Any help would be much appreciated as I cannot find a solution when searching.

-----------------
Dim olItem As Outlook.MailItem
Dim vText As Variant
Dim sText As String
Dim vItem As Variant
Dim Hallsitem As String
Dim HRItem As String
Dim i As Long


If Application.ActiveExplorer.Selection.Count = 0 Then
MsgBox "No Items selected!", vbCritical, "Error"
Exit Sub
End If


For Each olItem In Application.ActiveExplorer.Selection
sText = olItem.Body
vText = Split(sText, Chr(13))
HRItem = "Yes"


If InStr(1, vText(i), "Are you a resident in a halls of residence?:") > 0 Then
vItem = Split(vText(i), Chr(58))
Hallsitem = Trim(vItem(1))
End If


If Hitem = HRItem Then
MsgBox ("Worked")
Else
MsgBox ("Did not Work")
End If


Next olItem




'PermitAppFrm.Show
End Sub
------------------

Thank you

Pjawynn

[gmayor]

You might do a bit better is you changed

If Hitem = HRItem Then

to

If Hallsitem = HRItem Then

beyond that we would need to see a copy of the message you are testing.

[pjawynn]

Thank you for the suggestion gmayor I made the alteration but it still navigated to the "did not work" message here is a cut and past of the mail item. I cannot supply a .CSV due to data protection.

From: HYPERLINK "email address"email address [email address]
Sent: 05 January 2015 15:25
To: HYPERLINK "emaiol address"parking.somewhere.com
Subject: Sale Notification Email

The following product(s) has been purchased:

Forename: Fake
Surname: Name
Email Address: someone at rtrt . co
Qty: 1 Staff 2014/5 - Annual Permit Salary below 31342 pa 2014/15

Delivery Address: Somewhere,Cheltenham, Gloucestershire, United Kingdom
Post Code: GL6 666

Your item was included as part of the following order:
Order date: 05/01/2015 15:13:46
Order Number: GRERE72496
Item
Quantity
Unit Price
VAT Rate
Total Cost

Total Before VAT
£41.67
Delivery Before VAT
£0.00
VAT
£8.33
Total
£50.00

Answers to questions:
Product: Annual Permit
Please enter your Student or Staff Number: S121212121212
Are you a resident in a halls of residence? : Yes
Please confirm you have read and accepted the Parking Policy (Webpage): Yes
Please confirm vehicle registration(s): NNNN NNN, TTTT TTT,
Please confirm each vehicle details in the following order: make,model and colour: BMW 1 Series M Sport - Blue BMW 1 Series -

Thank you for your support

Kind Regards

Pjawynn

[skatonni]

Debugging would have shown vText(i) was always only vText(0)

Option Explicit
 
Sub HallQuestion()

    Dim olItem As Object
    
    Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
    Dim Hallsitem As String
    Dim HRItem As String
    Dim i As Long

    If Application.ActiveExplorer.Selection.count = 0 Then
        MsgBox "No Items selected!", vbCritical, "Error"
        Exit Sub
    End If
    
    For Each olItem In Application.ActiveExplorer.Selection
    
        If TypeOf olItem Is MailItem Then
    
            sText = olItem.body
            vText = Split(sText, Chr(13))
            HRItem = "Yes"
            
            Debug.Print UBound(vText)
            
            For i = 0 To UBound(vText)
                Debug.Print vText(i)
                If InStr(1, vText(i), "Are you a resident in a halls of residence? :") > 0 Then
                'If InStr(1, vText(i), "Are you a resident in a halls of residence?:") > 0 Then
                    vItem = Split(vText(i), Chr(58))
                    Hallsitem = Trim(vItem(1))
                    Exit For
                End If
            Next i

            If Hallsitem = HRItem Then
                MsgBox ("Worked")

                Hallsitem = ""

            Else
                MsgBox ("Did not Work")
            End If
            
        End If

    Next olItem
    
End Sub

[pjawynn]

Thank you skatonni

This has resolved the issue

Kind Regards

Pjawynn