使用VB / VBA搜索Outlook消息并将特定数据提取到Excel工作表中 [英] Using VB/VBA to search Outlook messages and extract specific data into Excel worksheet
问题描述
所以我使用Outlook / Excel 2007，我每天收到包含固定形式的一些数据的电子邮件。我希望做的是设置一个宏/脚本，它将搜索我的Outlook收件箱，然后基于正确的消息主题，将查看消息的正文，并将某些部分提取到Excel工作表中。
我认为VB可能是基于我的知识做最好的方法，但我不太确定从哪里开始。对代码或其他类似示例的一般结构的任何帮助将不胜感激。只是想着手开始，并希望能够自己解决未来的练习。谢谢！
所以非常感谢您的帮助！我主要是这样工作的，当我收到一条新消息时，我根本无法让它自动更新。我设置了一个规则，将相关电子邮件移动到自己的文件夹中，我可以设置一个可以运行的公共宏，将所有数据（每个电子邮件）拉出，并将其转储到.csv文件中。
我尝试将该宏适应于您上面发布的示例，当我收到一条新消息时应该自动运行，但我还没有成功。电子邮件的解析不应该改变（并且肯定在手动运行的宏中），所以这样做很好，只是让自动更新宏在新消息上运行。我错过了什么吗？这是我已经得到的，基本上与上面的示例相同，除了新的文件夹（并且是一个类模块）：
Public WithEvents myOlItems As Outlook.Items
Public Sub Application_Startup（）
'引用收件箱中的项目。因为myOlItems被声明为
'WithEvents，ItemAdd事件将在下面触发。
设置myOlItems = Outlook.Session.GetDefaultFolder（olFolderInbox）.Folders（FolderX）项目
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
'检查以确保它是Outlook邮件消息，否则
'后续代码可能会失败，具体取决于项目的类型
'。
如果TypeName（Item）=MailItem然后
'数据处理和解析在这里完成
End Sub
VB可能是最简单的语言，为您的问题工作，因为您是新来的所有这些和VBA（应用程序的Visual Basic）是特定问题的最简单和最互操作的语言。
您需要首先创建一个新的Outlook宏每当新邮件到达收件箱时，都会触发。
首先在Outlook（ALT-F11）中创建一个新的类模块，然后复制到以下代码中：
Public WithEvents myOlItems As Outlook.Items
公共子应用程序_Startup（）
'参考收件箱中的项目。因为myOlItems被声明为
'WithEvents，ItemAdd事件将在下面触发。
设置myOlItems = Outlook.Session.GetDefaultFolder（olFolderInbox）.Items
End Sub
Private Sub myOlItems_ItemAdd（ByVal Item As Object）
'检查以确保它是Outlook邮件消息，否则
'后续代码可能会失败取决于项目的类型
'。
如果TypeName（Item）=MailItem然后
如果Item.Subject =我必需的主题行然后
'这里是你想做的一些东东。
如果
结束If
End Sub
下一部分是打开Excel并执行您想要做的任何事情。请确保使用工具：引用...菜单项并选择Microsoft Excel xx.xx对象库来建立对excel对象库的引用。
可能需要以下代码：
Private Sub Do_Excel_Stuff（MyContent As Object）
Dim myXLApp As Excel。应用程序
Dim myXLWB As Excel.Workbook
设置myXLApp =新建Excel.Application
设置myXLWB =新建Excel.Workbook
'你的数据处理在这里
设置myXLWB =没有
设置myXLApp =没有
End Sub
这可能会从您的 myOlItems_ItemAdd 方法中调用。
有些环顾Google或Stack Overflow的用户可以提供足够的指针，说明如何处理Excel方法的实际数据处理部分。
希望这可以让你开始。
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 one but it wasn't quite as specific as I hoped.
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.
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!
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.
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
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.
You'll want to start by creating a new Outlook macro that fires whenever a new mail arrives in your inbox.
Start by creating a new class module in Outlook (ALT-F11) and copy in the following code:
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.
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_ItemAdd method.
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.
Hope this is enough to get you started.
这篇关于使用VB / VBA搜索Outlook消息并将特定数据提取到Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
