Scraping a specific line # from an email text

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

Using both Outlook 2003 and 2007 (mixed environment).

We have an email that comes in a standard format (some system-generated
data). The data of real interest is on line 3. I can parse out the data on
line 3, but I'm trying to figure out the best way to start with /just/ line 3.

Example:

Core Data
"Status","Location","Raw Score","Max Score","Min Score","Time"
"passed","81","65","79","0","00:17:00"

If I can capture just the 3rd line in a string variable, I'm all set:
"passed","81","65","79","0","00:17:00"

I've also used regex (poorly) in Excel, but this is so straightforward I'm
thinking there has to be an easy way to specify that I only want to grab that
third line for code manipulation. The code below provides some context; I'm
grabbing everything else I need except that third line.

I appreciate any suggestions.
Thank you,
Keith

Full code:
Sub ParseEmailFolderToExcel()

Set objApp = Application
Set olns = Outlook.GetNamespace("MAPI")
Set myinbox = olns.PickFolder
'Set myItems = myinbox.Items
Dim XLApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Dim ExcelWasNotRunning As Boolean

On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set XLApp = New Excel.Application
XLApp.Visible = True
End If

Set wkb = XLApp.Workbooks.Add
Set wks = wkb.Sheets(1)
With wks
StartCount = 0
strEmailContents = ""
For Each outlookmessage In myinbox.Items
StartCount = StartCount + 1
.Range("A" & StartCount).Value = outlookmessage.ReceivedTime
.Range("B" & StartCount).Value = outlookmessage.SenderName
.Range("C" & StartCount).Value = outlookmessage.Subject
.Range("D" & StartCount).value = outlookmessage.content.line(3) ?
'then parse it into the component measures
'will add code here
Next
End With

Set myOlApp = Nothing
Set olns = Nothing
Set myinbox = Nothing
Set myItems = Nothing

End Sub
 
There's no function like content.line(). But why not write such a function
yourself? I'd use the Split function, which returns an array.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>


Am Thu, 14 Jan 2010 16:41:01 -0800 schrieb ker_01:
Using both Outlook 2003 and 2007 (mixed environment).

We have an email that comes in a standard format (some system-generated
data). The data of real interest is on line 3. I can parse out the data on
line 3, but I'm trying to figure out the best way to start with /just/ line 3.

Example:

Core Data
"Status","Location","Raw Score","Max Score","Min Score","Time"
"passed","81","65","79","0","00:17:00"

If I can capture just the 3rd line in a string variable, I'm all set:
"passed","81","65","79","0","00:17:00"

I've also used regex (poorly) in Excel, but this is so straightforward I'm
thinking there has to be an easy way to specify that I only want to grab that
third line for code manipulation. The code below provides some context; I'm
grabbing everything else I need except that third line.

I appreciate any suggestions.
Thank you,
Keith

Full code:
Sub ParseEmailFolderToExcel()

Set objApp = Application
Set olns = Outlook.GetNamespace("MAPI")
Set myinbox = olns.PickFolder
'Set myItems = myinbox.Items
Dim XLApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Dim ExcelWasNotRunning As Boolean

On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set XLApp = New Excel.Application
XLApp.Visible = True
End If

Set wkb = XLApp.Workbooks.Add
Set wks = wkb.Sheets(1)
With wks
StartCount = 0
strEmailContents = ""
For Each outlookmessage In myinbox.Items
StartCount = StartCount + 1
.Range("A" & StartCount).Value = outlookmessage.ReceivedTime
.Range("B" & StartCount).Value = outlookmessage.SenderName
.Range("C" & StartCount).Value = outlookmessage.Subject
.Range("D" & StartCount).value =
Click to expand...
outlookmessage.content.line(3) ?
 
Michael- thank you for your suggestion- I used vbcrlf as the split, then
split on target line again using """,""" to get my individual values (which
were comma deliminated strings within quotes). Everything is working as
expected.

Best,
Keith
 
Back
Top