Prospects for VBA. Attempt to extract specific data from email body and export to Excel

Question

Here I found quite a few guides that brought me to the place where I am, but I need some help introducing the finishing touches to my code (I'm a complete newbie to this, so bear with me), I'm trying to use VBA in Outlook for export data from emails that I have in a specific folder of my Outlook to succeed. I need to extract data from a message body from multiple emails into an Excel worksheet. The email template I am retrieving can be found below. I need a 10-digit number after the reference number, a 10-digit number after the serial number, and a 7-digit number after the problem description. (I highlighted the parts that I need if it is not clear)

Dear Mr. / Ms xxxxxxxx,

------------------ No information needed -----------------

Reference number 1234567890 .

STATUS: ---- no information needed -----

Serial number: XXXXXXXXXX Description of the problem: ______________ (data here may vary slightly, I only agree to extract a 7-digit number from this area, but if this cannot be done, be it) _______

Use it ....

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

So far, I have managed to create a script that will look at the Outlook folder that I am currently in, open an Excel sheet, name the headers in excel and import the data. However, it pulls the whole body not only to the segments that I need, but also places them in the wrong columns in excel. This is, as far as I can, unfortunately, since I'm a complete newbie to this. I could find some examples on this site with a similar problem with solutions, but I could not fully understand them. After many trial and error, I resorted to sending myself, and any help would be greatly 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 

The links I have used so far: Using VB / VBA to search for Outlook messages and extract certain data into an Excel worksheet There was another that I used, that I can not find the link, as well as the stream on reddit, but I was still stuck . I'm not sure if this is the best way to achieve the results I want, as this is my first attempt to do something like this. I am open to change anything. thanks in advance

Answer 1

Seems like very good code for a "complete newbie"

Your code is very close to work. What you seem to be missing is understanding how the Array "messageArray" variable works.

Arrays by default start from zero and move from there. therefore an array with three possible values ​​will be measured as

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

and to fill this variable, you will need to change the code from what you have,

 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 

Another thing to be aware of is the use of the split function, and the delimiters are very elegant, but your data columns can contain not only the client number, but also the client number plus a whole bunch of text that you might not want. You may need to consider the start and end separator.

For example, as you have, your text from a letter is currently reading

Reference number 1234567890 .
STATUS: ---- no information needed -----
Serial number: XXXXXXXXXX Pro .......

You delimiters will find the keywords "reference number" and "serial number" and leave your text like this.

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

this way, your first data column will contain everything between the two ### 'delimiters, which means that your first column will contain all of this:

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

instead of this

1234567890 .

The simplest solution would be to add another delimiter to 'STATUS' and add another column named 'status' You will have 4 columns and ALL data will be neatly separated.

I see that quite a long time has passed since you posted this, I hope this helps. Sorry for the crazy formatting, I haven't written to StackOverflow before.