export outlook 2007 mail folder and subfolders to csv
What's the best way to export mail from an Outlook 2007 folder to a CSV file? I would like to include mail messages within subfolders as well开发者_StackOverflow中文版. The built in csv exporter does not allow the option to include subfolders but otherwise does exactly what i want.
I would say that Office Automation is the way to go here .. If you have Excel installed you can directly insert the properties into the Cells on a worksheet. You could write a macro in Excel to automate outlook or you could write a macro in outlook to push the data into a worksheet.
Below I have created a quick piece of VBA for outlook and used FSO to do the dirty work instead, It will give you a skeleton to work from, it will need a lot more error handling testing etc.
Sub SaveItemsToExcel()
On Error GoTo ErrorHandlerExit
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:\Temp\Export.csv", ForWriting, True)
Set oNameSpace = Application.GetNamespace("MAPI")
Set oFolder = oNameSpace.PickFolder
If oFolder Is Nothing Then
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,Recived"
ProcessFolderItems oFolder, objOutputFile
objOutputFile.Close
Set oFolder = Nothing
Set oNameSpace = Nothing
Set objOutputFile = Nothing
Set objFS = Nothing
ErrorHandlerExit:
Exit Sub
End Sub
Sub ProcessFolderItems(oParentFolder As Outlook.MAPIFolder, ByRef objOutputFile As Scripting.TextStream)
Dim oCount As Integer
Dim oMail As Outlook.MailItem
Dim oFolder As Outlook.MAPIFolder
oCount = oParentFolder.Items.Count
For Each oMail In oParentFolder.Items
If oMail.Class = olMail Then
objOutputFile.WriteLine oMail.SenderEmailAddress & "," & oMail.Subject & "," & oMail.ReceivedTime
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
Marcus
精彩评论