Reply to last email from specific sender in Excel

Question

In Excel, I am looking up the email address of a person, and then I want to find the last email (send or received) and trigger a reply to this email. This reply is triggered by a button in Excel.

Dim a As Integer
Dim objOutlook As Object
Dim objMail As Object
Dim rngBody As Range
Dim rngAttach As Range

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)


EmailStr = "sombody@gmail.com" (dummy replacement for my find the email adress in excel spreadsheet

then I would need to find emails to/from this address in my outlook, and then reply to the latest one.

What I did manage is start a new email to this person, but no idea how to find and reply

 With objMail
        .To = EmailStr
        .CC = AMEmail
        .Subject = TitleMail
           .HTMLBody = BodyStr & Signature
        .ReadReceiptRequested = True
    
        .Display 'Instead of .Display, you can use .Send to send the email _
                    or .Save to save a copy in the drafts folder
        

    Set objOutlook = Nothing
    Set objMail = Nothing
    Set rngBody = Nothing
    Set rngAttach = Nothing

Update: still struggling, but no more crashes for now. Wher I am stuck now it here:

Private Sub CommandButton2_Click()

Dim olApp As Object
Dim olNs As Object
Dim olFldr As Object

Dim olItems As Object
Dim olItemReply As Object
Dim i As Long

Dim emailStr As String
Dim filter As String

Set olApp = CreateObject("Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(6) ' olFolderInbox
Debug.Print "olFldr: " & olFldr

emailStr = "sombody@gmail.com" '(email address in Excel spreadsheet)
Debug.Print "emailStr: " & emailStr

Set olItems = olFldr.Items
Debug.Print olItems.Count
'finds all 19 items in my inbox with msgbox(olItems.count)


filter = "[SenderEmailAddress] = '" & emailStr & "'"
Debug.Print filter

Set olItems = olFldr.Items.Restrict(filter)
Debug.Print olItems.Count

'finds 0 items now ??? why....

End sub

Answer 1

To reply to most recently received mailitem in known folder.

Option Explicit' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Private Sub replyToSenderEmailAddress()

' Reply to most recently received mailitem in specified folder

' Late binding - reference to Outlook Object Library not required

Dim olApp As Object
Dim olNs As Object
Dim olFldr As Object

Dim olItems As Object
Dim olItemReply As Object
Dim i As Long

Dim emailStr As String
Dim filter As String

Set olApp = CreateObject("Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(6) ' olFolderInbox
Debug.Print "olFldr: " & olFldr

emailStr = "sombody@gmail.com" '(email address in Excel spreadsheet)
Debug.Print "emailStr: " & emailStr

Set olItems = olFldr.Items
Debug.Print olItems.Count

filter = "[SenderEmailAddress] = '" & emailStr & "'"
Debug.Print filter

Set olItems = olFldr.Items.Restrict(filter)
Debug.Print olItems.Count

olItems.Sort "[ReceivedTime]", True

For i = 1 To olItems.Count
    Debug.Print olItems(i).ReceivedTime
    If olItems(i).Class = 43 Then
        Set olItemReply = olItems(i).Reply
        olItemReply.Display
        Exit For
    End If
Next

End Sub

Answer 2

This demonstrates how to create a searchfolder of items received from an email address.

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant


Private Sub mailFromEmailAddress()

' Early binding
' Requires reference to Microsoft Outlook XX.X Object Library
Dim objOutlook As Outlook.Application

Dim strSearch As String

Dim strDASLFilter As String
Dim strDASLFilter_option As String
    
Dim strScope As String
Dim strScopeEdit As String
     
Dim objSearch As Search

Dim fldrNm As String

strSearch = "someone@internet.com"

Set objOutlook = CreateObject("Outlook.Application")

' create a searchfolder
Debug.Print
    
'strScope = "'Inbox', 'Deleted'"
strScope = "'Inbox'"
    
Debug.Print "strSearch...........: " & strSearch
    
' https://learn.microsoft.com/en-us/previous-versions/office/developer/exchange-server-2007/aa579702(v=exchg.80)
' ***** use "fromemail" for "senderemailaddress" *****
strDASLFilter_option = "fromemail"
Debug.Print "strDASLFilter_option: " & strDASLFilter_option
                
'fldrNm = strDASLFilter_option & " " & strSearch
fldrNm = strSearch
Debug.Print "fldrNm..............: " & fldrNm
                
'strDASLFilter = "urn:schemas:httpmail:" & strDASLFilter_option & " LIKE '%" & strSearch & "%'"
strDASLFilter = "urn:schemas:httpmail:" & strDASLFilter_option & " LIKE '" & strSearch & "'"
Debug.Print "strDASLFilter.......: " & strDASLFilter
                
Debug.Print "strScope............: " & strScope
Set objSearch = objOutlook.AdvancedSearch(scope:=strScope, filter:=strDASLFilter, SearchSubFolders:=True, Tag:="SearchFolder")
                
Debug.Print fldrNm
                
'Save the search results to a searchfolder
objSearch.Save fldrNm
Debug.Print fldrNm & " saved."

' Question 2
'  Reference the saved searchfolder
'  https://stackoverflow.com/questions/55363286/how-do-you-set-a-folder-variable-for-a-search-folder

' Question 3
'  Sort the collection of items in the searchfolder
'  Reply to most recent and appropriate item
    
End Sub