VBA 展望。尝试从电子邮件正文中提取特定数据并导出到 Excel

VBA Outlook. Trying to extract specific data from email body and export to Excel

提问by bezj

I have found quite a bit of guides on here that have gotten me to where I am currently at, but I need some help putting the finishing touches on my code (I'm a complete novice at this so bear with me). I am trying to use VBA within outlook to export data from the emails I have in a certain folder of my Outlook to excel. I need to extract data from the message body of numerous emails into an excel sheet. The email template I am extracting from can be found below. I need the 10 digit number after reference number, the 10 digit number after serial number, and the 7 digit number after problem description. (I have bolded the parts I need in case that was not clear)

我在这里找到了相当多的指南，这些指南让我了解了我目前所处的位置，但我需要一些帮助来对我的代码进行最后的润色（我在这方面完全是新手，所以请耐心等待）。我正在尝试在 Outlook 中使用 VBA 从我在 Outlook 某个文件夹中的电子邮件中导出数据，以使其成为 excel。我需要将大量电子邮件的消息正文中的数据提取到 Excel 表中。我从中提取的电子邮件模板可以在下面找到。我需要参考编号后的 10 位数字，序列号后的 10 位数字和问题描述后的 7 位数字。（我把我需要的部分加粗，以防不清楚）

Dear Mr/Ms xxxxxxxx,

亲爱的xxxxxxxx先生/女士，

------------------Not Needed Info-----------------

------------------不需要信息-----------------

Reference number 1234567890.

参考编号1234567890。

STATUS: ----not needed info-----

状态：----不需要信息-----

Serial Number: XXXXXXXXXXProblem Description: ______________(the data here can vary slightly, I am only concered with pulling a 7 digit number from this area but if that can't be done then so be it)_______

序列号：XXXXXXXXXXX问题描述：__________（这里的数据可能略有不同，我只关心从这个区域提取一个 7 位数字，但如果不能这样做，那就这样吧）_______

Use this….

用这个…。

-----------------The rest is not needed-----------------------

-----------------剩下的就不需要了-------------------------

So far I have been able to make a script that will browse the Outlook folder I am currently in, open an Excel sheet, name the headers in excel, and import the data. However, it pulls the entire body not just the segments I need and is putting them into the wrong columns in excel. That is as far as I can get unfortunately since I am a complete novice at this. I was able to find some examples on this site with a similar issue with solutions, but I wasn't able to make much sense of them. Through much trial and error I have resorted to posting myself, and any help would be much appreciated. Here is my code in its current incarnation-

到目前为止，我已经能够制作一个脚本来浏览我当前所在的 Outlook 文件夹，打开一个 Excel 工作表，在 excel 中命名标题，然后导入数据。但是，它不仅会拉动整个身体，而且会拉动我需要的部分，并将它们放入 excel 中错误的列中。不幸的是，这是我所能得到的，因为我在这方面是一个完全的新手。我能够在此站点上找到一些具有类似解决方案问题的示例，但我无法理解它们。通过多次试验和错误，我已经诉诸于张贴自己，任何帮助将不胜感激。这是我当前版本的代码-

    Sub Extract()
    On Error Resume Next
    Set myOlApp = Outlook.Application
    Set mynamespace = myOlApp.GetNamespace("mapi")

    ‘open the current folder, I want to be able to name a specific folder if possible…

    Set myfolder = myOlApp.ActiveExplorer.CurrentFolder
    Set xlobj = CreateObject("excel.application.14")
    xlobj.Visible = True
    xlobj.Workbooks.Add
    'Set Heading

    xlobj.Range("a" & 1).Value = "Case Number"
    xlobj.Range("b" & 1).Value = "HDD Serial Number"
    xlobj.Range("c" & 1).Value = "Sys Serial Number"
    xlobj.Range("d" & 1).Value = "User"


    For i = 1 To myfolder.Items.Count
    Set myitem = myfolder.Items(i)
    msgtext = myitem.Body

    ‘search for specific text
    delimtedMessage = Replace(msgtext, "reference number", "###")
    delimtedMessage = Replace(delimtedMessage, "Problem description:", "###")
    delimtedMessage = Replace(delimtedMessage, "Serial Number:", "###")
    messageArray = Split(delimtedMessage, "###")
    ‘write to excel
    xlobj.Range("a" & i + 1).Value = messageArray(1)
    xlobj.Range("b" & i + 1).Value = messageArray(2)
    xlobj.Range("c" & i + 1).Value = messageArray(3)
    xlobj.Range("d" & i + 1).Value = myitem.To

    Next
    End Sub

References I've used thus far: Using VB/VBA to search Outlook messages and extract specific data into Excel worksheetThere was another I used that I cannot find the link for, and a thread on reddit as well, but I am still stuck. I am not sure if any of this is the best way to achieve the results I want as this is my first attempt at something like this. I am open to changing anything. Thanks in advance

到目前为止我使用过的参考资料： 使用 VB/VBA 搜索 Outlook 消息并将特定数据提取到 Excel 工作表中 我使用了另一个我找不到链接的方法，以及 reddit 上的一个线程，但我仍然卡住了。我不确定这是否是实现我想要的结果的最佳方式，因为这是我第一次尝试这样的事情。我愿意改变任何事情。提前致谢

回答by user3217896

Seems like very nice code for a "Complete Novice"

对于“完全新手”来说似乎是非常好的代码

Your code is very close to working. The thing that you seem to have missed is understanding how the Array variable "messageArray" is working.

您的代码非常接近工作。您似乎错过了了解数组变量“messageArray”的工作方式。

Arrays by default start at zero , and move up from there. so an Array with 3 possible values would be Dimensioned as

默认情况下，数组从零开始，然后从那里向上移动。因此具有 3 个可能值的数组将被标注为

dim messageArray(3) as string  
 -'meaning an array with 3 possible values all of which are strings

and to fill that variable you would then need to change your code from what you have to this

并填充该变量，然后您需要将代码从您拥有的更改为

xlobj.Range("a" & i + 1).Value = messageArray(0)  ' Not 1 
xlobj.Range("b" & i + 1).Value = messageArray(1)  ' Not 2
xlobj.Range("c" & i + 1).Value = messageArray(2)  ' Not 3

The other thing to be aware of , is the use of split function, and the delimiters is very elegant, but your columns of data may end up with not just the customer number, but the customer number plus a whole bunch of text that you may not want. You might need to consider a starting and ending delimiter.

另一件要注意的事情是使用 split 函数，并且分隔符非常优雅，但是您的数据列可能最终不仅仅是客户编号，而是客户编号加上您可能会遇到的一大堆文本不想。您可能需要考虑开始和结束分隔符。

For example, as you have it, your text from the email currently reads

例如，正如您所拥有的，您的电子邮件中的文本当前读取

Reference number 1234567890.
STATUS: ----not needed info-----
Serial Number: XXXXXXXXXXPro.......

参考编号1234567890。
状态：----不需要信息-----
序列号：XXXXXXXXXXPro......

You delimiters will find the key words "reference number" and "Serial number" and leave you with your text looking like this

你的分隔符会找到关键词“参考号”和“序列号”，让你的文本看起来像这样

### **1234567890**.  '
*STATUS: ----not needed info-----* 
### **XXXXXXXXXX** Pro.......

in this way your first column of data will contain everythingbetween the two delimiters '###' meaning your first column will contain all of this:

这样你的数据的第一列将包含一切的两个分隔符“###”意味着你的第一列将包含所有的这间：

> **1234567890**.
> *STATUS: ----not needed info-----*

instead of just this

而不仅仅是这个

1234567890.

1234567890。

The simplest solution would be to add another delimiter at 'STATUS' and add another column called 'status' You would end up with 4 columns and ALL data neatly sub-divided.

最简单的解决方案是在“STATUS”处添加另一个分隔符，并添加另一列名为“status”的列。您最终会得到 4 列，并且所有数据都被整齐地细分。

I can see it was quite a while since you posted this , I hope this helps. Apologies all for the crazy formatting, I haven't posted on StackOverflow before.

我可以看到你发布这个已经有一段时间了，我希望这会有所帮助。为疯狂的格式道歉，我之前没有在 StackOverflow 上发过帖子。