Track/Manage outlook emails

[aravindhan_3]

Hi,

Can you guys help me with a macro which tracks/managing outlook inbox?

I have a Generic Mail box where 10 of my team members uses.

If I run a macro from my system in outlook
In an excel file the following details are to be copied in columns

From - Received Date - Recieved Time

I need to get how many email are there in inbox( or selected folder) with , how many were replied and by whom, and howmany were forwarded and by whom? can all these be captured in a an excel?

I have attached the sample excel file with the list of items to be captured from outlook

Cross post : http://www.excelforum.com/excel-programming/754805-track-manage-outlook-emails.html#post2425931

[JP2112]

Do you have any code written at all?

What you want is extremely difficult to provide using VBA.

You won't be able to tell if a message has been replied to, or forwarded -- the UnRead property only tells you that a message has been read. You could check the conversation index, but, it's a lot of work. Are you willing to pay for a solution?

Also, if it's a shared mailbox you won't easily be able to tell who actually sent the message.

[Benzadeus]

I couldn't open the attachment.
The idea is execute a macro that will give you a report in a Excel Worksheet. You can try this code below:
Dim r As Long

Sub GerarLista()

Dim appOutlook As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.Folder

On Error Resume Next
Set appOutlook = GetObject(, "Outlook.Application")
If appOutlook Is Nothing Then
Set appOutlook = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set olNS = appOutlook.GetNamespace("MAPI")
'Change information here to get a different Outlook folder.
'Try changing to 'olFolderSentMail' to get information of
'Sent Items.
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

Cells.Delete
r = 0
DescePasta olFolder

Set olFolder = Nothing
Set olNS = Nothing

End Sub

Sub DescePasta(olFolder As Outlook.Folder)

Dim olSubFolder As Outlook.Folder
Dim olItem As MailItem

r = r + 1
Cells(r, "A") = olFolder.FolderPath
For Each olItem In olFolder.Items
r = r + 1
'Some example of MailItem properties you can read:
Cells(r, "A") = olItem.Subject
Cells(r, "B") = olItem.SenderEmailAddress
Cells(r, "C") = olItem.To
Cells(r, "D") = olItem.ReceivedTime
Cells(r, "E") = olItem.Attachments.Count
Cells(r, "F") = olItem.Size
Next olItem

For Each olSubFolder In olFolder.Folders
DescePasta olSubFolder
Next olSubFolder

End Sub

[JP2112]

I don't see how this code does what the OP wants.

It doesn't return the number of emails, whether the message was replied to or by whom, or whether the messages were forwarded.

I maintain that this is difficult if not impossible using the Outlook object model.

[Benzadeus]

You have a point.
I only gave him a way to read an entire's folder content. He just have to adjust the output in the Excel's spreadsheet and add formulas like COUNTIF or create a PivotTable for a report.

Otherwise, I completely agree with you that it is a complex task.

"and howmany were forwarded and by whom?"
I think I can't monitorate that.
To count items, he just has to use olFolder.Items.Count