Monitoring E-Mail response VBA

[Shanmugam]

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.

[gmayor]

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

[Shanmugam]

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?

[gmayor]

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

[Shanmugam]

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

[gmayor]

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

[Shanmugam]

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.