Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    Joined
    Jul 2016
    Posts
    6
    Location

    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!

    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

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Jul 2016
    Posts
    6
    Location
    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!

Posting Permissions

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