Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA to Reply All To Latest Email Thread

  1. #1
    Junior Member
    Join Date
    Aug 2018
    Posts
    12
    Rep Power
    0

    VBA to Reply All To Latest Email Thread

    Hello,

    I'm trying to build a macro that searches my inbox for a subject line (in this example "REQUEST FOR OVERTIME"), opens the most recent email, and reply all. My macro is a little more nuanced, but borrowed from the example I found online below.

    The issue is that it's opening ALL emails with the subject line "REQUEST FOR OVERTIME," not just the most recent. What should I add to this in order to just open the latest message?

    Thanks!


    Code:
    Sub ReplyMail_No_Movements()
    
    Dim olApp As Outlook.Application
    Dim olNs As Namespace
    Dim Fldr As MAPIFolder
    Dim olMail As Variant
    Dim i As Integer
    
    
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)
    i = 1
    
    For Each olMail In Fldr.Items
    
    If InStr(olMail.Subject, "REQUEST FOR OVERTIME") <> 0 Then
            
                Set replyall = olMail.replyall
                With replyall
            StrBody = "Hello " & "<br>" & _
            "<p>Following up with the below. May you please advise?" & _
            "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _
            "<p>" & Session.CurrentUser.Name
            .HTMLBody = StrBody & .HTMLBody
                    emailReady = True
                .Display
    
    i = i + 1
    End If
    
    Next olMail
    
    Set olMail = Nothing
    Set olApp = Nothing
    
    End Sub


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
    https://www.eileenslounge.com/viewtopic.php?p=311844#p311844
    https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe
    https://www.eileenslounge.com/viewtopic.php?p=311826#p311826
    https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783
    https://www.eileenslounge.com/viewtopic.php?p=310916#p310916
    https://www.eileenslounge.com/viewtopic.php?p=310720#p310720
    https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171
    https://www.eileenslounge.com/viewtopic.php?p=310110#p310110
    https://www.eileenslounge.com/viewtopic.php?p=310024#p310024
    https://www.eileenslounge.com/viewtopic.php?p=309121#p309121
    https://www.eileenslounge.com/viewtopic.php?p=309101#p309101
    https://www.eileenslounge.com/viewtopic.php?p=308945#p308945
    https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 04-07-2024 at 12:53 PM. Reason: Added Code Tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Hi,

    You could loop through the sent items that have the specific subject and look for the latest date time. Once you figure that out at the end of the loop, you reply to that particular one.

    Can modify the code if you haven't figured it out already. If you have, please post it here.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Aug 2018
    Posts
    12
    Rep Power
    0
    Thank you! Sorry, I'm sort of new to VBA so I haven't figured that out yet. Any help is GREATLY appreciated!

    Also, I don't necessarily need to only look in sent items since I CC myself on my emails, so I only need to look through my inbox for the newest email.

    Thanks!

    -Patrick

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Patrick,

    Yes, this is one way to do it. I haven't tested it extensively, but seems to work

    Code:
    Sub ReplyMail_No_Movements()
    
        Dim olApp As Outlook.Application
        Dim olNs As NameSpace
        Dim Fldr As MAPIFolder
        Dim olMail As MailItem
        Dim objReplyToThisMail As MailItem
        Dim lngCount As Long
        Dim objConversation As Conversation
        Dim objTable As Table
        Dim objVar As Variant
        
        Set olApp = Session.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        
        
        lngCount = 1
        
        For Each olMail In Fldr.Items
            If InStr(olMail.Subject, "REQUEST FOR OVERTIME") <> 0 Then 'Text To Check
                Set objConversation = olMail.GetConversation
                Set objTable = objConversation.GetTable
                objVar = objTable.GetArray(objTable.GetRowCount)
                Set objReplyToThisMail = olApp.Session.GetItemFromID(objVar(UBound(objVar), 0))
                With objReplyToThisMail.ReplyAll
                    StrBody = "Hello " & "<br>" & _
                                "<p>Following up with the below. May you please advise?" & _
                                "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _
                                "<p>" & Session.CurrentUser.Name
                    .HTMLBody = StrBody & .HTMLBody
                    emailReady = True
                    .Display
                End With
            Exit For
            End If
        Next olMail
        
        Set olApp = Nothing
        Set olNs = Nothing
        Set Fldr = Nothing
        Set olMail = Nothing
        Set objReplyToThisMail = Nothing
        lngCount = Empty
        Set objConversation = Nothing
        Set objTable = Nothing
        If IsArray(objVar) Then Erase objVar
        
    End Sub
    Last edited by Excel Fox; 12-01-2018 at 09:43 AM. Reason: Appended The Missing .ReplyAll
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Aug 2018
    Posts
    12
    Rep Power
    0
    Thanks so much!

    Problem though - when I copy/paste this code into VBA, I get the "run-time error '13': Type mismatch error" and the "Next olMail" gets highlighted. Any idea why?

    When I ran into this problem last time, I changed Dim olMail As MailItem to Dim olMail As Variant and it seemed to work. This time if I do that, I get a different error "Run-time error '2147352567 (80020009)': Could Not Send The Message"

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Yeah it is probably because you have item types other than mailitems in your inbox. I think I will need to check for the item type and then proceed with the loop. Give me some time, and I will send a modified version
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Aug 2018
    Posts
    12
    Rep Power
    0
    I got it! Had to Frankenstein some different codes together, but I got it to work. Check it out below (I used the full code which also pulls from a master file in excel to populate the data).

    Thanks for the help!


    Code:
    Sub Follow_Up()
    
    Dim c As Range, f As Range, source As Worksheet, master As Worksheet, s As String, StrBody As String
    
    Dim Fldr As Outlook.Folder
    Dim olMail As Variant
    Dim olReply As Outlook.MailItem
    Dim olItems As Outlook.Items
    Dim i As Integer
    Dim IsExecuted As Boolean
    
    Set source = Worksheets("Request List")
      Set master = Worksheets("Master List")
      Set olApp = New Outlook.Application
      
      For Each c In source.Range("A2", source.Cells(source.Rows.Count, "A").End(xlUp))
        With c
          If IsEmpty(.Offset(, 3).Value) = True Then GoTo NextC
          If .Offset(, 4).Value <> True Then GoTo NextC
          If .Offset(, 6).Value <> "NO" Then GoTo NextC
          
                Set olApp = New Outlook.Application
          Set olNs = olApp.GetNamespace("MAPI")
          
        Set Fldr = Session.GetDefaultFolder(olFolderInbox)
        IsExecuted = False
        Set olItems = Fldr.Items
        olItems.Sort "[Received]", True
        
        For i = 1 To olItems.Count
            Set olMail = olItems(i)
            If InStr(olMail.Subject, "Document Request: " & c.Offset(, 1).Value) > 0 Then
                If Not olMail.Categories = "Executed" Then
                    Set olReply = olMail.replyall
                    With olReply
            StrBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello " & "<br>" & _
            "<p>Following up with the below. May you please advise?" & _
            "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _
            "<p>" & Session.CurrentUser.Name
            .HTMLBody = StrBody & .HTMLBody
                    emailReady = True
                .Display
                c.Offset(, 6).Value = "YES"  'Source sheet sent, YES.
                c.Offset(, 7).Value = Date   'Source sheet, Date sent.
                c.Offset(, 8).Value = Session.CurrentUser.Name
                    End With
                    Exit For
                    olMail.Categories = "Executed"
                   IsExecuted = True
                End If
            End If
        Next i
        End With
        
    NextC:
      Next c
      
      On Error Resume Next
      Set olMail = Nothing
      Set olApp = Nothing
    End Sub
    Last edited by pkearney10; 12-19-2018 at 11:26 PM.

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    As long as it working

    I was modifying my code. For my interest, could you test this and let me know

    Code:
    Option Explicit
    
    Sub ReplyMail_No_Movements()
    
        Dim olApp As Outlook.Application
        Dim olNs As NameSpace
        Dim Fldr As MAPIFolder
        Dim objMail As Object
        Dim objReplyToThisMail As MailItem
        Dim lngCount As Long
        Dim objConversation As Conversation
        Dim objTable As Table
        Dim objVar As Variant
        Dim strBody As String
        
        Set olApp = Session.Application
        Set olNs = olApp.GetNamespace("MAPI")
        Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
        
        
        lngCount = 1
        
        For Each objMail In Fldr.Items
            If TypeName(objMail) = "MailItem" Then
                If InStr(objMail.Subject, "REQUEST FOR OVERTIME") <> 0 Then 'REQUEST FOR OVERTIME
                    Set objConversation = objMail.GetConversation
                    Set objTable = objConversation.GetTable
                    objVar = objTable.GetArray(objTable.GetRowCount)
                    Set objReplyToThisMail = olApp.Session.GetItemFromID(objVar(UBound(objVar), 0))
                    With objReplyToThisMail.ReplyAll
                        strBody = "Hello " & "<br>" & _
                                    "<p>Following up with the below. May you please advise?" & _
                                    "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _
                                    "<p>" & Session.CurrentUser.Name
                        .HTMLBody = strBody & .HTMLBody
                        .Display
                    End With
                    Exit For
                End If
            End If
        Next objMail
        
        Set olApp = Nothing
        Set olNs = Nothing
        Set Fldr = Nothing
        Set objMail = Nothing
        Set objReplyToThisMail = Nothing
        lngCount = Empty
        Set objConversation = Nothing
        Set objTable = Nothing
        If IsArray(objVar) Then Erase objVar
        
    End Sub
    Last edited by Excel Fox; 12-04-2018 at 09:24 PM. Reason: Removed unnecessary variable
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Aug 2018
    Posts
    12
    Rep Power
    0
    Thanks! This one works too it seems (and is definitely cleaner than mine ).

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Thanks for confirming. Cheers
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 185
    Last Post: 05-22-2024, 10:02 PM
  2. Replies: 19
    Last Post: 04-20-2019, 02:38 PM
  3. Table Tests. And Thread Copy Tests No Reply needed
    By DocAElstein in forum Test Area
    Replies: 1
    Last Post: 11-20-2018, 01:11 PM
  4. Replies: 8
    Last Post: 04-13-2014, 02:38 PM
  5. Moving Current Latest Data To New Workbook
    By Terry in forum Excel Help
    Replies: 1
    Last Post: 01-19-2013, 12:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •