Consulting

Results 1 to 7 of 7

Thread: Monitoring E-Mail response VBA

  1. #1

    Monitoring E-Mail response VBA

    Hi,

    I am looking for a VBA code to monitor whether a response has been sent to all e-mails that has received in my inbox (or any specific folder). I do get huge amount of e-mails (say approx. 500 e-mails in a day) and out of that, I/my team have to reply at least 80 to 100 e-mails. As a large e-mail receiver, I am not too sure whether the required e-mails have been responded.

    It would be helpful if a VBA code could help export 'all' inbox items like sender, date/time received, mail subject and add 1 more column to see whether a response has been sent with Sender name, date/time sent (whether me or somebody from my team). I am not looking for the body of the e-mail because I know it will be too complex to put in the excel column. This will help me in monitoring daily e-mail response and avoid certain abrasions from the sender and also going to reduce huge amount of manual search process for e-mails.

    I would really appreciate your help on this.
    Thank you,
    Shan

  2. #2
    Maybe something like the following Outlook macro, which will give you an Excel compatible CSV file that gives the current state of play.
    Option Explicit
    Sub IsAnswered()
    'Graham Mayor - www.gmayor.com
    'Change the path as appropriate
    Const strMasterPath As String = "C:\Path\Today's Messages.csv"
    Dim olItem As MailItem
    Dim oFSO As Object
    Dim oFile As Object
    Dim strLine As String
    Dim sReplySent As String
    MsgBox "Please wait until 'Processed Finished' message"
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFile = oFSO.OpenTextFile(strMasterPath, 8, True, 0)
        For Each olItem In Session.GetDefaultFolder(olFolderInbox).Items
            If Format(olItem.ReceivedTime, "yyyymmdd") = Format(Date, "yyyymmdd") Then
                If olItem.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10810003") = 0 Then
                    sReplySent = "REPLY OUTSTANDING"
                Else
                    sReplySent = ""
                End If
                strLine = Format(olItem.ReceivedTime, "yyyymmdd") & Chr(44) & _
                          Format(olItem.ReceivedTime, "HH:MM") & Chr(44) & _
                          olItem.SenderEmailAddress & Chr(44) & _
                          olItem.subject & Chr(44) & sReplySent
                oFile.Write strLine & vbCrLf
            End If
            DoEvents
        Next olItem
        oFile.Close
        MsgBox "Process Finished"
    lbl_Exit:
        Set oFSO = Nothing
        Set oFile = Nothing
        Exit Sub
    End Sub
    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
    Thank you GMayor. I have moved all e-mails to one of the sub folder but Macro runs only if it in the inbox. Can this macro be dynamic to select any or current folder so that the similar results can be displayed?
    Thank you,
    Shan

  4. #4
    You could access any available folder. The following variation will process the current folder
    Option Explicit
    Sub IsAnswered()
    'Graham Mayor - www.gmayor.com
    'Change the path as appropriate
    Const strMasterPath As String = "C:\Path\Today's Messages.csv"
    Dim olFolder As MAPIFolder
    Dim olItem As MailItem
    Dim oFSO As Object
    Dim oFile As Object
    Dim strLine As String
    Dim sReplySent As String
    
        Set olFolder = ActiveExplorer.CurrentFolder
    
        MsgBox "Please wait until 'Processed Finished' message"
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFile = oFSO.OpenTextFile(strMasterPath, 8, True, 0)
        For Each olItem In olFolder.Items
            If Format(olItem.ReceivedTime, "yyyymmdd") = Format(Date, "yyyymmdd") Then
                If olItem.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10810003") = 0 Then
                    sReplySent = "REPLY OUTSTANDING"
                Else
                    sReplySent = ""
                End If
                strLine = Format(olItem.ReceivedTime, "yyyymmdd") & Chr(44) & _
                          Format(olItem.ReceivedTime, "HH:MM") & Chr(44) & _
                          olItem.SenderEmailAddress & Chr(44) & _
                          olItem.subject & Chr(44) & sReplySent
                oFile.Write strLine & vbCrLf
            End If
            DoEvents
        Next olItem
        oFile.Close
        MsgBox "Process Finished"
    lbl_Exit:
        Set olFolder = Nothing
        Set oFSO = Nothing
        Set oFile = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    I am sorry GMayor, the new code doesn't show and provide results the current folder and it is still searching from the inbox.
    Thank you,
    Shan

  6. #6
    Did you open the folder you wish to process in Outlook to display its messages before running the revised macro?

    The following will let you pick the folder to process instead
    Option Explicit
    Sub IsAnswered()
         'Graham Mayor - www.gmayor.com
         'Change the path as appropriate
        Const strMasterPath As String = "C:\Path\Today's Messages.csv"
        Dim olFolder As MAPIFolder
        Dim olNS As NameSpace
        Dim olItem As MailItem
        Dim oFSO As Object
        Dim oFile As Object
        Dim strLine As String
        Dim sReplySent As String
         
        Set olNS = GetNamespace("MAPI")
        Set olFolder = olNS.PickFolder
         
        MsgBox "Please wait until 'Processed Finished' message"
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFile = oFSO.OpenTextFile(strMasterPath, 8, True, 0)
        For Each olItem In olFolder.Items
            If Format(olItem.ReceivedTime, "yyyymmdd") = Format(Date, "yyyymmdd") Then
                If olItem.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10810003") = 0 Then
                    sReplySent = "REPLY OUTSTANDING"
                Else
                    sReplySent = ""
                End If
                strLine = Format(olItem.ReceivedTime, "yyyymmdd") & Chr(44) & _
                Format(olItem.ReceivedTime, "HH:MM") & Chr(44) & _
                olItem.SenderEmailAddress & Chr(44) & _
                olItem.subject & Chr(44) & sReplySent
                oFile.Write strLine & vbCrLf
            End If
            DoEvents
        Next olItem
        oFile.Close
        MsgBox "Process Finished"
    lbl_Exit:
        Set olFolder = Nothing
        Set oFSO = Nothing
        Set oFile = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    Hi GMayor,

    I am sorry for the delayed response. I have selected the specific folder which I am looking forward the status, but still the new code didn't work. No problem, kindly close this request, I found that is request is no longer required. Thank you for the assistance provided.
    Thank you,
    Shan

Posting Permissions

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