1

I want to fetch all the Outlook inbox emails into an Excel sheet with additional columns having the data like This mail was replied on or This mail was forwarded to

Here is the code that I have done so far

Dim Folder As Outlook.MAPIFolder
Dim iRow As Integer
MailBoxName = 'Mailbox Name Goes Here
Pst_Folder_Name = "Inbox"
Set Folder = Outlook.Session.PickFolder 'Folders(MailBoxName).Folders(Pst_Folder_Name)      
If Folder = "" Then
    MsgBox "Invalid Data in Input"
    GoTo end_lbl1:
End If

Folder.Items.Sort "[ReceivedTime]", False
LimitDateTimeValue = 'Date Limit
CellNo = 2
For iRow = 1 To Folder.Items.Count
On Error Resume Next  
If Folder.Items.Item(iRow).ReceivedTime > LimitDateTimeValue Then
    'CellNo = 2
    On Error Resume Next
    ThisWorkbook.Sheets("Inbox").Range("A2").Select

    FullSubjectLine = Folder.Items.Item(iRow).Subject
    If InStr(1, FullSubjectLine, "FE:", vbTextCompare) > 0 Or InStr(1, FullSubjectLine, "FW:", vbTextCompare) > 0 Or InStr(1, FullSubjectLine, "RE:", vbTextCompare) Then
        FilteredSubjectLine = Mid(FullSubjectLine, 5)
        ThisWorkbook.Sheets("Inbox").Cells(CellNo, 2) = FilteredSubjectLine
    Else
        ThisWorkbook.Sheets("Inbox").Cells(CellNo, 2) = Folder.Items.Item(iRow).Subject
    End If

    ThisWorkbook.Sheets("Inbox").Cells(CellNo, 4) = Left(Folder.Items.Item(iRow).Body, 1024)
    If Folder.Items.Item(iRow).UnRead Then

        ThisWorkbook.Sheets("Inbox").Cells(CellNo, 6) = "UnRead"
    Else
        ThisWorkbook.Sheets("Inbox").Cells(CellNo, 6) = "Read"
    End If
        ThisWorkbook.Sheets("Inbox").Cells(CellNo, 1) = Folder.Items.Item(iRow).SenderName
    ThisWorkbook.Sheets("Inbox").Cells(CellNo, 3) = Folder.Items.Item(iRow).ReceivedTime

    CellNo = CellNo + 1

End If

Next iRow
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Pavan Chougule
  • 21
  • 3
  • May I suggest you take a look [here](http://stackoverflow.com/questions/33373771/copying-relative-data-from-one-workbook-to-another#33373771) XD Edit: I am curious if people like this approach strangers and ask for their computer/car/shoe fixed by qualifying it with because I am new/don't know how XD – findwindow Oct 27 '15 at 17:07
  • more to the point then where @findwindow referred you to, please [see this](http://stackoverflow.com/help/how-to-ask) and update your post according to those guidelines to receive meaningful help from this site .. – Scott Holtzman Oct 27 '15 at 17:23
  • Please show your existing code. Where do the messages come from? Selection? Some folder? – Dmitry Streblechenko Oct 27 '15 at 17:23
  • This answer [How to copy Outlook mail message into excel using VBA or Macros](http://stackoverflow.com/a/12146315/973283) of mine may get you started. It creates an Excel workbook and copies selected properties of every email in Inbox to a worksheet. – Tony Dallimore Oct 27 '15 at 17:25
  • thanks for posting your code, now edit your question to tell us where it is not working correctly or where you are stuck. not many people are going to try to sort it out for you without some direction – Scott Holtzman Oct 27 '15 at 18:16

1 Answers1

0

The code is extremely inefficient, this is multiple dot notation taken to its extreme. Cache the Items collection before entering the loop and retrieve the item only once on each iteration - otherwise OOM will have to return a brand new COM object for each ".".

On Error Resume Next 
set vItems = Folder.Items
For iRow = 1 To vItems.Count
  set vItem = vItems.Item(iRow)
  FullSubjectLine = vItem.Subject
  lastVerbExecuted = vItem.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10810003")
  if Err.Number <> 0 Then
    lastVerbExecuted = 0
    Err.Clear
  End If
  ...
next
Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78
  • thanks for the reply, but lastVerbExecuted in each itteration is returning 0. i want the message displaying "you replied to this message on 10/27/2015 5:52 PM." or "you forwarded this message on 10/27/2015 5:52 PM." in front of each mail – Pavan Chougule Oct 28 '15 at 10:16
  • Do you actually see the PR_LAST_VERB_EXECUTED and PR_LAST_VERB_EXECUTION_TIME properties on the message itself? Take a look at the messages with OutlookSpy (click IMessage). The string displayed by Outlook is created dynamically from these two properties. – Dmitry Streblechenko Oct 28 '15 at 17:16