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

[Enright]

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!


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

[gmayor]

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.


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

[Enright]

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!