使用 VB/VBA 搜索 Outlook 邮件并将特定数据提取到 Excel 工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5826158/
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 VB/VBA to search Outlook messages and extract specific data into Excel worksheet
提问by SeanJean
So first things first, I'm a VB newbie working from scratch but have edited some code in the past. The closest question I could find to mine was this onebut it wasn't quite as specific as I hoped.
首先，我是一名 VB 新手，从头开始工作，但过去曾编辑过一些代码。我能找到的最接近我的问题是这个，但它并不像我希望的那么具体。
So I'm using Outlook/Excel 2007 and I receive a daily email that contains some data in a fixed form. What I am hoping to do is set up a Macro/Script that will search my Outlook Inbox, and then based on the correct Message Subject, will look in the body of the message and extract certain portions into an Excel worksheet.
所以我使用的是 Outlook/Excel 2007，我每天都会收到一封电子邮件，其中包含一些固定格式的数据。我希望做的是设置一个宏/脚本来搜索我的 Outlook 收件箱，然后根据正确的邮件主题，查看邮件正文并将某些部分提取到 Excel 工作表中。
I think VB is probably the best way to do this based on my knowledge, but I'm not quite sure where to start. Any help on the general structure of the code or other similar examples would be much appreciated. Just looking to get started and hopefully figure it out on my own for future exercises. Thanks!
根据我的知识，我认为 VB 可能是最好的方法，但我不太确定从哪里开始。对代码的一般结构或其他类似示例的任何帮助将不胜感激。只是想开始，并希望自己弄清楚以备将来的练习。谢谢！
So thanks so much for the help! I've mostly got this working, I just haven't been able to get it to automatically update when I get a new message. I have a rule set up that moves the relevant emails into their own folder, and I was able to set up a public macro that I can run that pulls all the data out (for every email) and dumps them into a .csv file.
所以非常感谢您的帮助！我大部分时间都在工作，只是无法在收到新消息时让它自动更新。我设置了一个规则，将相关电子邮件移动到他们自己的文件夹中，并且我能够设置一个公共宏，我可以运行该宏来提取所有数据（对于每封电子邮件）并将它们转储到 .csv 文件中。
I tried to adapt that macro into the example you posted above that should automatically run when I receive a new message, but I haven't succeeded yet. The parse-ing of the emails shouldn't change (and definitely works in the manually run macro), so that is fine, it's just getting the auto-update macro to run on a new message. Am I missing something? Here is what I've got, which is basically the same as the example above aside from the new folder (and is a class module):
我尝试将该宏应用到您上面发布的示例中，当我收到新消息时，该示例应自动运行，但我还没有成功。电子邮件的解析不应该改变（并且肯定可以在手动运行的宏中工作），所以没关系，它只是让自动更新宏在新邮件上运行。我错过了什么吗？这是我得到的，除了新文件夹（并且是一个类模块）之外，它与上面的示例基本相同：
Public WithEvents myOlItems As Outlook.Items
Public Sub Application_Startup()
' Reference the items in the Inbox. Because myOlItems is declared
' "WithEvents" the ItemAdd event will fire below.
Set myOlItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("FolderX").Items
End Sub
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objMail As MailItem
Dim count As Integer
Dim myTitlePos As Integer
Dim myTitleLen As Integer
Dim myVarPos As Integer
Dim myVarLen As Integer
Dim strPrice As String
Dim strYear As String
Dim myVarCRLF As Integer
Dim myDate As Date
Dim newLineTest As String
' Check to make sure it is an Outlook mail message, otherwise
' subsequent code will probably fail depending on what type
' of item it is.
If TypeName(Item) = "MailItem" Then
' Data processing and parsing is done here
End Sub
回答by Phil.Wheeler
VB is probably the easiest language to work with for your problem since you are new to all this and VBA (Visual Basic for Applications) is the simplest and most interoperable language for the particular problem.
VB 可能是解决您的问题的最简单的语言，因为您对这一切都不熟悉，而 VBA（Visual Basic for Applications）是解决特定问题的最简单且最具互操作性的语言。
You'll want to start by creating a new Outlook macro that fires whenever a new mail arrives in your inbox.
您首先需要创建一个新的 Outlook 宏，每当有新邮件到达您的收件箱时就会触发该宏。
Start by creating a new class module in Outlook (ALT-F11) and copy in the following code:
首先在 Outlook (ALT-F11) 中创建一个新的类模块并复制以下代码：
Public WithEvents myOlItems As Outlook.Items
Public Sub Application_Startup()
' Reference the items in the Inbox. Because myOlItems is declared
' "WithEvents" the ItemAdd event will fire below.
Set myOlItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
' Check to make sure it is an Outlook mail message, otherwise
' subsequent code will probably fail depending on what type
' of item it is.
If TypeName(Item) = "MailItem" Then
If Item.Subject = "My Required Subject Line" Then
' Here's where you want to do some stuff.
End If
End If
End Sub
The next part is to open Excel and do whatever stuff it is you want to do. Be sure to establish the reference to the excel object library by using "Tools:References..." menu item and selecting Microsoft Excel xx.xx object library.
下一部分是打开 Excel 并执行您想做的任何事情。确保通过使用“工具：参考...”菜单项并选择 Microsoft Excel xx.xx 对象库来建立对 Excel 对象库的引用。
You'll probably want some code like the following:
您可能需要一些类似以下的代码：
Private Sub Do_Excel_Stuff(MyContent As Object)
Dim myXLApp As Excel.Application
Dim myXLWB As Excel.Workbook
Set myXLApp = New Excel.Application
Set myXLWB = New Excel.Workbook
' Do your data processing here
Set myXLWB = Nothing
Set myXLApp = Nothing
End Sub
This would likely be called from within your myOlItems_ItemAddmethod.
这可能会从您的myOlItems_ItemAdd方法中调用。
Some looking around on Google or Stack Overflow should give you enough pointers about how you might want to handle the actual data processing part for your Excel method.
一些在 Google 或 Stack Overflow 上环顾四周的人应该会给您足够的指示，说明您可能希望如何处理 Excel 方法的实际数据处理部分。
Hope this is enough to get you started.
希望这足以让你开始。
