Scraping a specific line # from an email text

Status
Not open for further replies.
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

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 =

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
 
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

"Michael Bauer " wrote:



> 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
>

>

> 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 =
> 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
> .
>
 
Status
Not open for further replies.
< Programmatically scheduling WebEx meetings through Outlook | How to get MailItem.Body without security warning in Outlook 2010 >
Similar threads
Thread starter Title Forum Replies Date
K Scraping outlook emails (2 questions) Outlook VBA and Custom Forms 1
N Reply to Outlook messages by moving messages to a specific Outlook folder Outlook VBA and Custom Forms 1
K vba code to auto download email into a specific folder in local hard disk as and when any new email arrives in Inbox/subfolder Outlook VBA and Custom Forms 0
K Add an entry to a specific calendar Using Outlook 1
G Adding a contact to a specific folder Using Outlook 0
L Specific Incoming Email Address Immediately Deleted Using Outlook 2
W OL giving basic auth when logged in to Win as specific user Using Outlook 0
D Outlook 365 Forward Meeting Related Messages to Specific Meeting Organizer Outlook VBA and Custom Forms 0
richardwing Auto forward email that is moves into a specific outlook folder Outlook VBA and Custom Forms 5
P Emails assigned with a certain category (within a shared inbox) to be copied to a specific folder. Outlook VBA and Custom Forms 2
Z Copy specific email body text Outlook VBA and Custom Forms 0
D Forwarding email based on the attachment file type and specific text found on the attachment file name Outlook VBA and Custom Forms 1
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
J Autoreply email recieved from specific sender after deleting some text from body. Using Outlook 0
S Macro or plug-in to see if specific person was included in this email Outlook VBA and Custom Forms 4
J Diane's Delay Delivery at Specific Times Questions Outlook VBA and Custom Forms 7
R How to get the Items object of the default mailbox of a specific account in a multiple account Outlook? Outlook VBA and Custom Forms 0
R Assign Categories "Round Robin" style but in a shared mailbox but on specific emails only Outlook VBA and Custom Forms 8
A VBA macro for 15 second loop in send and received just for 1 specific mailbox Outlook VBA and Custom Forms 1
A How to open a specific link automatically with outlook 2016 Outlook VBA and Custom Forms 6
W Remove specific contacts from contact list Outlook VBA and Custom Forms 3
9 Outlook 2016 How to save an Outlook attachment to a specific folder then delete the email it came from? Using Outlook 1
S Outlook to check for specific text Outlook VBA and Custom Forms 3
RBLampert Assigning a newly (re)created e-mail account to a specific .pst data file Using Outlook 2
Nadine Rule to move attachments with specific name Outlook VBA and Custom Forms 1
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
R Can not create folder to store specific emails in in Outlook for Mac Using Outlook 1
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
O Rule to move (specific) messages from Sent folder to Specific folder Using Outlook 1
e_a_g_l_e_p_i Can you set reminder to specific times? Using Outlook 7
H Select Specific Account When Sending Email, Based on Current Folder Outlook VBA and Custom Forms 1
L Restrict accepted appts to specific calendar Using Outlook 2
P Import an .ics file to a specific calendar Using Outlook 4
D How to handle filing of emails from an Inbox Subfolder to a specific Public folder Using Outlook 1
J Save E-mail attachments in a specific folder Outlook VBA and Custom Forms 0
P Auto scroll to specific folder in Folder Pane Outlook VBA and Custom Forms 3
A How to open a specific link automatically with outlook Outlook VBA and Custom Forms 13
V VB script code to save a specific email attachment from a given email Outlook VBA and Custom Forms 14
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
K How to find specific header and copy the mail body Using Outlook 0
O How to paste website content using a specific font and removing URLs Using Outlook 2
M How to remove a list of specific contacts from Outlook Using Outlook 18
I Application_NewMailEx for a specific Email account Outlook VBA and Custom Forms 4
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
J Saving attachments from specific sender (phone number) to specific folder on hard drive Using Outlook 3
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
Diane Poremsky Delay Delivery of Messages Sent at Specific Times Using Outlook 0
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
A Block user to send emails to specific set of email ids Using Outlook 1

Similar threads

Back
Top