I’m writing this post primarily for Sen who in a comment to another post asked
How do i Export e-mail messages with the subject, received date & time from Outlook to Excel with the sender address?
The simplest way to carry out this is to use Outlook’s built-in export capability. Using it you can export to a .csv (comma separated values) file which you then open with Excel. However, there are a couple of drawbacks to using export. Export doesn’t allow you to pick specific messages to export so you’ll have to export an entire folder at a time. It also doesn’t allow you to limit the export to certain fields. Instead it will export everything and you’ll have to delete the columns you don’t want.
A better, but more complicated, approach is to use a macro to do the export. With a macro you can select the messages you want, export only the fields you want, and it can write directly to Excel. The code for doing this is both simple and straightforward. It creates a spreadsheet, loops though the selected messages writing the fields you want to that spreadsheet, then saves and closes the spreadsheet. This solution should work in Outlook 2003 and later.
Adding the code to Outlook.
- Start Outlook
- Press ALT+F11 to open the Visual Basic Editor
- If not already expanded, expand Microsoft Office Outlook Objects
- If not already expanded, expand Modules
- Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
- Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
- Click the diskette icon on the toolbar to save the changes
- Close the VB Editor
Sub ExportMessagesToExcel()
Dim olkMsg As Outlook.MailItem, _
excApp As Object, _
excWkb As Object, _
excWks As Object, _
intRow As Integer, _
strFilename As String
strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
If strFilename <> "" Then
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Add()
Set excWks = excWkb.ActiveSheet
'Write Excel Column Headers
With excWks
.Cells(1, 1) = "Subject"
.Cells(1, 2) = "Received"
.Cells(1, 3) = "Sender"
End With
intRow = 2
'Write messages to spreadsheet
For Each olkMsg In Application.ActiveExplorer.Selection
'Only export messages, not receipts or appointment requests, etc.
If olkMsg.Class = olMail Then
'Add a row for each field in the message you want to export
excWks.Cells(intRow, 1) = olkMsg.Subject
excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
excWks.Cells(intRow, 3) = olkMsg.SenderEmailAddress
intRow = intRow + 1
End If
Next
Set olkMsg = Nothing
excWkb.SaveAs strFilename
excWkb.Close
End If
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
MsgBox "Process complete. A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub
Using the Code.
- With Outlook open select one or more messages from any folder.
- Run the macro.
- When prompted enter a filename to save the export to. You can cancel the export by not entering anything.
- The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of messages exported.
Notes.
- This code can easily be modified to export a different set of fields. To do that, change the headings written to the spreadsheet (lines 15-17) and the message fields (lines 25-27).
- If you don’t want the macro to prompt for a filename each time, then you can change line 7 to strFilename = “Path_and_File_Name”
Filed under: Outlook, Scripting Tagged: Outlook, VBA
没有评论:
发表评论