VBA前景。尝试从邮件主体中提取特定的数据并导出到Excel中

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: XXXXXXXXXX Problem 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)_______

序号:XXXXXXXXXX问题说明:______________(这里的数据可能略有不同，我只关注从这个区域拉出一个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 worksheet There 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上的一个线程，但我还是被卡住了。我不确定这是否是实现我想要的结果的最好方法，因为这是我第一次尝试这样的东西。我愿意改变任何事情。谢谢提前

1 个解决方案

#1

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

数组默认从0开始，然后向上移动。所以一个有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: XXXXXXXXXX Pro.......

参考号码1234567890。----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -

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 everything between 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 * before.

我可以看到自从你发布这篇文章已经有一段时间了，我希望这能有所帮助。为这些疯狂的格式道歉，我之前没有在*上发布过。

#1