Export specific Outlook emails via VBA to Excel

We want to export specific emails from our Microsoft Outlook 2007 to an Excel 2007 Workbook.

We want to search just the Inbox folder for all emails whose subject only have the word VISA and the body has the word PROJECTX

From this specific email we want export the first 2 lines from the body section and the emails date.

finally, export the line $.9999.99 (this is a value that is always different). 

The info of he Excel workbook is: name BOOK1.xlxs and the worksheet we want to export the data to is named SHEET1.

Within the worksheet we want write to:
 
   Column B, the emails date
   Column D, the first line exported
   Column E, the second line exported from Outlook 
   Column G, $.9999.99. Value exported

The email format that we received is as follow (and always is the same):

        Subject:  Visa

        Body:
        TYPE
        DESCRIPTION
        PROJECTX xx blahblah:

        Xx xx xxxxxxxxx xx xxxxxx x xx
        xxxxxx xxx xxx xxxxxxx xx
        xxxxxxxx xx
        $.9999.99.

        ---------------
        Xxxxx xxxxxx xxxxxx xxxxx
        xxxxx xxxxxx xxxxxx xxxxx
        xxxxx xxxxxx xxxxxx xxxxx

We have developed via VBA for Outlook before.  When we get to the office we'll upload the script we have so far (which we successfully developed with EE assistance).
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
Where do you want to execute the code from ... the outlook client or the worksheet?

Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
I guess also is the subject exactly VISA or does the subject contain Visa somewhere in it?

Chris
0
rayluvsAuthor Commented:
Would like to execute from Excel.  The subject is exactly Visa.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chris BottomleySoftware Quality Lead EngineerCommented:
The following will append to the sheet using column B as the last line populated and therefore always append data to the worksheet.
Sub Q_27376197()
Dim olkApp As Object
Dim olkns As Object
Dim myfolder As Object
Dim mai As Object
Dim olMailItems As Object
Dim strFilter As String
Dim sh As Worksheet
Dim arr As Variant
Dim ln As Variant
Const olFolderInbox As Integer = 6
    
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set olkApp = CreateObject("Outlook.Application")
    Set olkns = olkApp.GetNamespace("MAPI")
    Set myfolder = olkns.GetDefaultFolder(olFolderInbox)
    strFilter = "[subject] = " & "VISA"
    Set olMailItems = myfolder.items.Restrict(strFilter)
    With sh
        For Each mai In olMailItems
            With sh.Range("B" & sh.Range("B" & sh.Rows.Count).End(xlUp).Row).Offset(1, 0)
                .Value = Format(mai.receivedtime, "dd mmm yyyy")
                arr = Split(mai.body, vbCrLf)
                .Offset(0, 2).Value = arr(0)
                .Offset(0, 3).Value = arr(1)
                For Each ln In arr
                    If Left(ln, 1) = "$" Then
                        .Offset(0, 5).Value = ln
                    End If
                Next
            End With
        Next
    End With
    
Set olMailItems = Nothing
Set olkns = Nothing
Set olkApp = Nothing
Set myfolder = Nothing
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
It worked perfectly.  But the example we gve at top, there is a difference.

Instead of the following:

   Xx xx xxxxxxxxx xx xxxxxx x xx
   xxxxxx xxx xxx xxxxxxx xx
   xxxxxxxx xx
   $.9999.99.

It's really like this:

Xx xx xxxxxxxxx xx xxxxxx x xx xxxxxx xxx xxx xxxxxxx xx xxxxxxxx xx $.9999.99.

We were seeing the email in our iphone, not the Outllook; so its one line, not 4 as displayed in the question.

So what we need is to read in that line and extract just $.9999.99 which is alwasy located at the end of the line.  Sinc the value changes, we'll need to first search "$" calculate the length of the value from that point.

Whats
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Try


?"$" & split(arr(2), "$")(1)

or arr(3/4/5) until you find the right one.

Chris
0
rayluvsAuthor Commented:
We're going to try yours, but we did the following:

                    located = InStr(ln, "$.")
                    If located <> 0 Then
                       Length = Len(ln)
                       LValue = Mid(ln, located + 2, Length - (located + 2))
                        .Offset(0, 6).Value = LValue
                    End If

What you think?
0
rayluvsAuthor Commented:
Would like to try your recommendations on:

?"$" & split(arr(2), "$")(1)

or arr(3/4/5) until you find the right one.

I keep getting error
0
rayluvsAuthor Commented:
Thanx
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry been out for a while ... are you ok the now?

Chris
0
rayluvsAuthor Commented:
Yes !!! You've been Great!!!

Thanx!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.