How To Reply To Most Recent E-mail for a Specific Subject

Hello!

So far, I have the code below that will look for a specific subject name in a specific folder "Refund Correspondence".

However, it seems that it opens any random e-mail with the designated subject name.

I can't figure out what to add so that it will only reply to the the most recent e-mail with the specified subject name.

Thanks!

Code:

---

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim I As Integer
Dim IsExecuted As Boolean
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox).Folders("Refund Correspondence")
IsExecuted = False
For Each olMail In Fldr.Items
If InStr(olMail.Subject, Me.Vendor_Client & " Tax Refund Request - " & Me.Vendor_Name) > 0 Then
    If Not IsExecuted Then
            With olMail.ReplyAll
                .BodyFormat = olFormatHTML
                .Display
                .To = Me.Vendor_E_mail
                .Subject = Me.Vendor_Client & " Tax Refund Request - " & Me.Vendor_Name
            End With
        IsExecuted = True
    End If
End If

---

If this is an Outlook macro why are you creating an new Outlook application? Use the one you are running the macro in.

You need to add the messages to a collection (here olItems) so that you can sort the collection. You can then process the collection sequentially until you find the first item that matches.
It is not clear (as you have not shown all the macro) what the point of the boolean isExecuted is. Better to add a category that you can read e.g. as follows and exit the loop when the message is found. As I don't have access to your userform, I cannot test the code, but it should point the way.

Code:

---

Dim Fldr As Outlook.Folder
Dim olMail As Outlook.MailItem
Dim olReply As Outlook.MailItem
Dim olItems As Outlook.Items
Dim i As Integer
    'Dim IsExecuted As Boolean
    Set Fldr = Session.GetDefaultFolder(olFolderInbox).folders("Refund Correspondence")
    '    IsExecuted = False
    Set olItems = Fldr.Items
    olItems.Sort "[Received]", True
    For i = 1 To olItems.Count
        Set olMail = olItems(i)
        If InStr(olMail.subject, Me.Vendor_Client & " Tax Refund Request - " & Me.Vendor_Name) > 0 Then
            '            If Not IsExecuted Then
            If Not olMail.categories = "Executed" Then
                Set olReply = olMail.ReplyAll
                With olReply
                    .BodyFormat = olFormatHTML
                    .Display
                    .To = Me.Vendor_E_mail
                    .subject = Me.Vendor_Client & " Tax Refund Request - " & Me.Vendor_Name
                End With
                Exit For
                olMail.categories = "Executed"
                '                IsExecuted = True
            End If
        End If
    Next i

---

This works great. It calls the Outlook Application because it's being run off an access form where we track our vendor correspondence.

Thanks Graham!