VBA展望尝试从电子邮件正文中提取特定数据并导出到Excel [英] VBA Outlook. Trying to extract specific data from email body and export to Excel

问题描述

我在这里找到了相当多的指南，让我到目前的位置，但是我需要一些帮助，使我的代码完成（我是一个完整的新手，所以忍受我） 。我正试图在Outlook中使用VBA从我Outlook中某个文件夹中的电子邮件中导出数据。我需要从许多电子邮件的邮件正文中提取数据到Excel表格中。我从中提取的电子邮件模板可以在下面找到。参考号码后需要10位数字，序列号后10位数字，问题描述后7位数字。 （如果不清楚，我已经粗略了我需要的部分）

尊敬的Mr / Ms xxxxxxxx，

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

参考号 1234567890 。

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

序号： XXXXXXXXXX 问题描述： ______________（这里的数据可能略有不同，我只想从这个区域拉一个7位数字，但如果那么这样做是不行的）_______

使用此...。

-----------------其余不需要-----------------------

到目前为止，我已经能够制作一个脚本来浏览我目前所在的Outlook文件夹，打开一个Excel工作表，在excel中命名头文件，并导入数据。但是，它不仅使整个身体不仅仅是我需要的细分，而且还将其放入excel中的错误列中。这是我可以不幸的是，因为我是一个完整的新手在这个。我可以在这个网站上找到一些与解决方案相似的问题的例子，但是我没有多大意义。通过多次尝试和错误，我已经发布了自己，任何帮助将不胜感激。
这是我现在的代码中的代码 -

  Sub Extract（）
 On Error Resume Next 
设置myOlApp = Outlook.Application 
设置mynamespace = myOlApp.GetNamespace（mapi）
 
'打开当前文件夹，如果可能，我想要命名一个特定的文件夹... 
 
设置myfolder = myOlApp.ActiveExplorer.CurrentFolder 
设置xlobj = CreateObject（excel.application.14）
 xlobj.Visible = True 
 xlobj.Workbooks 。添加
'设置标题
 
 xlobj.Range（a& 1）.Value =案例编号
 xlobj.Range（b& 1） .Value =HDD序列号
 xlobj.Range（c& 1）.Value =Sys序列号
 xlobj.Range（d& 1）.Value =用户
 
 
对于i = 1 To myfolder.Items.Count 
设置myitem = myfolder.Items（i）
 msgtext = myitem.Body 
 
'搜索特定文本
 delimtedMessage = Replace（msgtext，reference number， ###）
 delimtedMessage =替换（delimtedMessage，问题描述：，###）
 delimtedMessage =替换（delimtedMessage，序列号，###） 
 messageArray = Split（delimtedMessage，###）
'写入excel 
 xlobj.Range（a& i + 1）.Value = messageArray（1）
 xlobj.Range（b& i + 1）.Value = messageArray（2）
 xlobj.Range（c& 1）.Value = messageArray（3）
 xlobj.Range（d& i + 1）.Value = myitem.To 
 
下一个
 End Sub 
  

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

解决方案

似乎是一个完整新手的非常好的代码

你的代码非常接近工作。你似乎错过的是了解Array变量messageArray如何工作。

默认情况下，数组从零开始，从那里向上移动。所以一个有3个可能的值
的数组将被Dimensioned为

  dim messageArray（3）as string 
 - 一个包含3个可能值的数组，它们都是字符串
  

并填充该变量那么需要将你的代码从你所拥有的那个更改为

  xlobj.Range（a& i + 1）。 Value = messageArray（0）'Not 1 
 xlobj.Range（b& i + 1）.Value = messageArray（1）'不是2 
 xlobj.Range（c& + 1）.Value = messageArray（2）'不是3 
  

另一件要注意的事情，是使用分割功能，分隔符非常优雅，但是您的数据列可能不仅仅是客户编号，而且客户编号加上一大堆文本可能不需要。您可能需要考虑一个起始和终止分隔符。

例如，根据您的具体情况，电子邮件中的文字目前为

参考号 1234567890 。

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

序列号： XXXXXXXXXX Pro .......

您的分隔符将找到关键词参考号和序列号，并留下您的文本看起来像这样

  ### ** 1234567890 **。 '
 *状态：----不需要信息----- * 
 ### ** XXXXXXXXXX ** Pro ....... 
  

以这种方式，您的第一列数据将包含两个分隔符###'$ b $之间的所有 b表示您的第一列将包含所有这些：

 > ** ** 1234567890。 
> *状态：----不需要信息----- * 
  

而不是这个

1234567890 。

最简单的解决方案是在STATUS中添加另一个分隔符，并添加另一列status
你最终会有4列和所有的数据被细分。

我发现这是一段时间，希望这有助于您。
抱歉，所有的疯狂的格式，我没有发布在StackOverflow之前。

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)

Dear Mr/Ms xxxxxxxx,

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

Reference number 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)_______

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-

    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

解决方案

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.

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

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.

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

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

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.

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.

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.

这篇关于VBA展望尝试从电子邮件正文中提取特定数据并导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！