title: VBA Outlook. Trying to extract specific data from email body and export to Excel
body: <p>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)</p>
  
  <p>Dear Mr/Ms xxxxxxxx,</p>
  
  <p>------------------Not Needed Info-----------------</p>
  
  <p>Reference number <strong>1234567890</strong>.</p>
  
  <p>STATUS: ----not needed info-----</p>
  
  <p>Serial Number: <strong>XXXXXXXXXX</strong> Problem Description: <strong>______________(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)_______</strong></p>
  
  <p>Use this….</p>
  
  <p>-----------------The rest is not needed-----------------------</p>
  
  <p>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-</p>
  
  <pre><code>    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" &amp; 1).Value = "Case Number"
      xlobj.Range("b" &amp; 1).Value = "HDD Serial Number"
      xlobj.Range("c" &amp; 1).Value = "Sys Serial Number"
      xlobj.Range("d" &amp; 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" &amp; i + 1).Value = messageArray(1)
      xlobj.Range("b" &amp; i + 1).Value = messageArray(2)
      xlobj.Range("c" &amp; i + 1).Value = messageArray(3)
      xlobj.Range("d" &amp; i + 1).Value = myitem.To
  
      Next
      End Sub
  </code></pre>
  
  <p>References I've used thus far:
  <a href="https://stackoverflow.com/questions/5826158/using-vb-vba-to-search-outlook-messages-and-extract-specific-data-into-excel-wor">Using VB/VBA to search Outlook messages and extract specific data into Excel worksheet</a>
  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  </p>