Transferring Outlook Mail data to Excel

[RhinoNeil]

I have copied some code from a previous thread I found in this forum and it generally works fine (i.e. produces results).
My problem (apart from having no idea how this macro works) is that the macro only transfers some of the mail from the folder selected.
As an example, I have a folder holding old mail from 2011. It has a toal of 5,925 mail items but has only copied over 57 of them into the CSV file.
I cannot see any pattern of what has or hasn't been copied. I have from and to which have both copied and non-copied mail. I have mail copied from throughout the year and file types do not seem to stop it work (i.e. macro does not stop when it gets to a meeting request).

I know people do not have much to work on as I cannot copy my mail folder but does anybody have any idea why the macro is not copying all mail to the CSV file?

Thanks for any help and a warning to anybody posting solutions I have a very limited VBA knowledge.

Sub SaveItemsToExcel()
   Dim oNameSpace As Outlook.NameSpace
   Dim oFolder As Outlook.MAPIFolder
   'You must set a reference to the Microsoft Scripting Runtime library touse the FileSystemObject
   Dim objFS As Scripting.FileSystemObject
   Dim objOutputFile As Scripting.TextStream
   Set objFS = New Scripting.FileSystemObject
   Set objOutputFile = objFS.OpenTextFile("C:\Export\Export.csv", ForWriting, True)
   Set oNameSpace = Application.GetNamespace("MAPI")
   Set oFolder = oNameSpace.PickFolder
   If oFolder Is Nothing Then
    MsgBox "Folder not valid"
    GoTo ErrorHandlerExit
   End If

    ' Check if folder can contain Mail Items
    If oFolder.DefaultItemType <> olMailItem Then
        MsgBox "Folder does not contain mail messages"
        GoTo ErrorHandlerExit
    End If

   'Write header line
    objOutputFile.WriteLine "From,Subject,Received,Parent,Importance,Unread,To"
    ProcessFolderItems oFolder, objOutputFile
    objOutputFile.Close
    Set oFolder = Nothing
    Set oNameSpace = Nothing
    Set objOutputFile = Nothing
    Set objFS = Nothing
    
    MsgBox "Completed"
ErrorHandlerExit:
    Exit Sub

End Sub
Sub ProcessFolderItems(oParentFolder As Outlook.MAPIFolder, ByRef objOutputFile As Scripting.TextStream)
    On Error Resume Next
    
    Dim oCount As Integer
    Dim oMail As Outlook.MailItem
    Dim oFolder As Outlook.MAPIFolder
    Dim x As Integer
    
    oCount = oParentFolder.Items.Count
    
    For Each oMail In oParentFolder.Items
       
        If oMail.Class = olMail Then
        objOutputFile.WriteLine oMail.SenderEmailAddress & "," & oMail.Subject & "," & oMail.ReceivedTime & "," & oMail.Parent & "," & oMail.Importance & "," & oMail.UnRead & "," & oMail.To
        End If
    Next oMail
    
    Set oMail = Nothing
    'check to see if we have an child folders
    If (oParentFolder.Folders.Count > 0) Then
            For Each oFolder In oParentFolder.Folders
                ProcessFolderItems oFolder, objOutputFile
            Next
    End If
    
End Sub

 

[Jugantarji]

Which version of Outlook/Excel are you using. In 2010 you don't need to use code. You can go to the file tab on the ribbon. Then click "open" on the left-hand side. Then click "Import". You should then get a box open giving you the option to export to file.

HTH

 

[RhinoNeil]

HTH

Thanks but unfortunately I am using 2007 for both Outlook and Excel.