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

Do you have all the emails in Excel? Or do you have to go to another application , like Outlook or GMail to look up prior emails? — JimmyNJ, Sep 09 '21 at 12:35
I want to get the email address from excel, and find the last email from that person in outlook. shall clear up the question — Mark, Sep 09 '21 at 14:32
You should update your question with more details like that. And include the approach/tools you are using. Are you using VBA in Excel? Can you write VBA for your Outlook (Alt-F11)? — JimmyNJ, Sep 09 '21 at 15:57
For received mail, one possibility is `.Restrict` on `SenderEmailAddress`. https://stackoverflow.com/a/42547062/1571407. For late binding you have to declare everything Outlook as `Object` and change olFolderInbox to 6 and olMail to 43. — niton, Mar 07 '22 at 17:00

niton · Answer 1 · 2022-02-18T15:34:05.283

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

Not sure how this helps initiate a reply to the last email from contact x — Mark, Feb 16 '22 at 11:43
The proposed solution may be excessive but since you did not specify a specific folder to look for mail, this code should create a search folder with all email items from "someone@internet.com". Try it with an appropriate email address. The "Question 2" link demonstrates how to reference the search folder by name. You can `.Sort` the items then `Reply` to the most recent mailitem. — niton, Feb 16 '22 at 15:29
There is a simpler solution, with many examples on the site, if you know the mail will be in a specific folder. You can reference that folder to then filter for "someone@internet.com". — niton, Feb 16 '22 at 15:35
I'm trying to make a start, but every example I've tried already runs into an issue at Dim objOutlook As Outlook.Application, compile error User-defined type not defined — Mark, Feb 18 '22 at 11:48
See `Dim objOutlook As Object` In your question. https://stackoverflow.com/questions/50798486/user-defined-type-not-defined-when-creating-outlook-object — niton, Feb 18 '22 at 15:30
I fear I am about to give up. Dim objSearch As Search gives error because Search does not exist, and then set objSearch gives an error because it needs an object. — Mark, Feb 22 '22 at 14:30
Edit the question with your attempt and indicate whether the code is in Outlook or Excel. If in Excel, indicate whether you set the reference to Outlook. — niton, Feb 22 '22 at 18:26

score 1 · Answer 2 · answered Mar 16 '22 at 22:39

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

answered Mar 16 '22 at 22:39

niton

8,771
21
32
52

Thank you for helping me again Niton, I tried your new code, and at first, it worked, but then I found out it only works if the email address I am looking for is the most recent one in my inbox. For any other email address that I have received mail from, nothing happens. Any idea what goes wrong? How can I see what olItems contains after the 'Restrict(filter)' and why won't it find any other emails other than the top 1 in my inbox? – Mark Mar 21 '22 at 12:13
I added a msgbox(olItems.Count) to see what it finds before the filter, and it shows 21. I suspect this can't be correct as I have a few thousand emails in my outlook – Mark Mar 21 '22 at 12:23
This method processes one folder. Unless you learn `.AdvancedSearch` from other sources you could apply brute force, to search every folder separately. Technique to get to every folder under the inbox demonstrated here https://stackoverflow.com/questions/2272361/can-i-iterate-through-all-outlook-emails-in-a-folder-including-sub-folders. This will be relatively slow. – niton Mar 22 '22 at 17:48
There is only one main inbox folder I want to search. Anyway your code caused critical errors in my excel and I spend 2 days rebuilding a replicate of my file as nothing could be clicked anymore – Mark Mar 22 '22 at 17:57

Reply to last email from specific sender in Excel

2 Answers2