Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export specific Outlook emails via VBA to Excel

Posted on 2011-10-02
11
Medium Priority
?
545 Views
Last Modified: 2012-09-09
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).
0
Comment
Question by:rayluvs
  • 6
  • 5
11 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36901570
Where do you want to execute the code from ... the outlook client or the worksheet?

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36901582
I guess also is the subject exactly VISA or does the subject contain Visa somewhere in it?

Chris
0
 

Author Comment

by:rayluvs
ID: 36901679
Would like to execute from Excel.  The subject is exactly Visa.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 36902358
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
 

Author Comment

by:rayluvs
ID: 36904035
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36904168
Try


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

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

Chris
0
 

Author Comment

by:rayluvs
ID: 36904227
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
 

Author Comment

by:rayluvs
ID: 36904304
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
 

Author Closing Comment

by:rayluvs
ID: 36904513
Thanx
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36905964
Sorry been out for a while ... are you ok the now?

Chris
0
 

Author Comment

by:rayluvs
ID: 36906403
Yes !!! You've been Great!!!

Thanx!!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
As a Microsoft Exchange user, you must have known the importance of an Offline storage table (OST) file. It is nothing new for an Outlook user to be dependent on a .ost file during a server break down or a problematic Internet connection. In such a…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question