在 Outlook 中使用 VBA 收集收到的电子邮件的统计信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16404224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA in Outlook to gather statistics on received emails
提问by Jordan Wall
At work we use a shared Outlook mailbox to receive emails from our users, and at the moment we have a rota of who's turn it is to monitor the mailbox and reply etc. Our working hours have been 7am-5pm since we started the email address.
在工作中，我们使用共享的 Outlook 邮箱接收来自用户的电子邮件，目前我们有轮到谁来监视邮箱和回复等。我们的工作时间是从我们开始使用电子邮件地址开始的早上 7 点到下午 5 点.
For the next 2 months we're trailing a change in hours where we (or should I say, myself only) will be monitoring the mailbox up until 11pm.
在接下来的 2 个月中，我们将跟踪一个小时数的变化，我们（或者我应该说，只有我自己）将监控邮箱直到晚上 11 点。
What I'd like to do is gather some statistics on the emails we receive to see if it is worth it from a business view to keep the later shift on after the trail.
我想要做的是收集一些关于我们收到的电子邮件的统计数据，看看从业务角度来看是否值得在跟踪之后继续轮班。
What I was thinking of doing is using some VBA to check the emails in the inbox, and then break the data down into some stats for management, eg:
我想做的是使用一些 VBA 来检查收件箱中的电子邮件，然后将数据分解为一些统计数据以进行管理，例如：
Monday 06/05/12:
49 emails received, 34 were replies, 15 were new
At 7am received: 0 emails
At 8am received: 1 emails
------
At 11pm received: 0 emails
etc
等等
To work out if an email is an original or reply I think it's easiest to see if the subject starts with RE:? (I know it's not foolproof but I think it'll work for basic stats)
要确定电子邮件是原始邮件还是回复邮件，我认为最容易查看主题是否以 RE:? （我知道这不是万无一失的，但我认为它适用于基本统计数据）
Has anyone done anything like this before? Is there an easy/right way of doing it?
有没有人做过这样的事情？有没有简单/正确的方法来做到这一点？
Anyone got any tips/code samples that would be useful?
有人有任何有用的提示/代码示例吗？
采纳答案by Dick Kusleika
You could start with something like this
你可以从这样的事情开始
Sub EmailStats()
Dim olMail As MailItem
Dim aOutput() As Variant
Dim lCnt As Long
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim flInbox As Folder
Set flInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
ReDim aOutput(1 To flInbox.Items.Count, 1 To 4)
For Each olMail In flInbox.Items
If TypeName(olMail) = "MailItem" Then
lCnt = lCnt + 1
aOutput(lCnt, 1) = olMail.SenderEmailAddress 'maybe stats on domain
aOutput(lCnt, 2) = olMail.ReceivedTime 'stats on when received
aOutput(lCnt, 3) = olMail.ConversationTopic 'group based on subject w/o regard to prefix
aOutput(lCnt, 4) = olMail.Subject 'to split out prefix
End If
Next olMail
Set xlApp = New Excel.Application
Set xlSh = xlApp.Workbooks.Add.Sheets(1)
xlSh.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
xlApp.Visible = True
End Sub
If you want to add more data, just change the Redimstatement to accommodate more columns and add them in the If block. Once in Excel (or maybe Access or SQLServer if it's a lot of data), you can add a few calculations, like
如果要添加更多数据，只需更改Redim语句以容纳更多列并将它们添加到 If 块中。一旦在 Excel 中（或者可能是 Access 或 SQLServer，如果它有很多数据），你可以添加一些计算，比如
=IF(LEFT(@Subject,3)="RE:","Reply",IF(LEFT(@Subject,3)="FW:","Forward","Original"))
Then pivot like crazy.
然后像疯了一样旋转。
You need a reference to the Excel object library for the above code to work.
您需要引用 Excel 对象库才能使上述代码正常工作。
回答by Phylogenesis
I'd take a step back further and use the log files from the mail server to answer this question.
我会更进一步，使用邮件服务器中的日志文件来回答这个问题。
Once a day, you could simply pull a report of all emails received by the mailbox. If you don't personally have access to them, then your mail administrator should.
每天一次，您可以简单地提取邮箱收到的所有电子邮件的报告。如果您个人无权访问它们，那么您的邮件管理员应该有权访问它们。
回答by computingfreak
There is this nice tool called OutlookStatViewmade by the awesome guys at NirSoft.
有一个叫做OutlookStatView 的好工具，由 NirSoft 的出色人员制作。
screenshot sample:
截图示例：
if you are not so keen on using VBA, please try using the advanced filtering options available for the same, using it you can select specific folders, and start datetime and end datetime for your monitoring.
如果您不太热衷于使用 VBA，请尝试使用可用的高级过滤选项，使用它您可以选择特定文件夹，并开始日期时间和结束日期时间进行监控。
Note: this won't be realtime or automated, it is all a manual way to demonstrate the possibilities.
注意：这不会是实时的或自动化的，它是一种演示可能性的手动方式。
mailbox scan options:
邮箱扫描选项：
Runs on Windows 2000/XP/Vista/2003/2008/7/8/10.
在 Windows 2000/XP/Vista/2003/2008/7/8/10 上运行。
Supports Any version of Microsoft Outlook, including Outlook 2016.
支持任何版本的 Microsoft Outlook，包括 Outlook 2016。
I'm looking for Mac & Linux alternatives.
我正在寻找 Mac 和 Linux 的替代品。
this answer is similar but not same as https://superuser.com/a/1226613/249975
此答案与https://superuser.com/a/1226613/249975相似但不相同
