Copy Email Items to Excel Workbook


Have you ever wanted to copy Email Items from Outlook to an Excel Workbook. Once inside Excel you can then analyse the information in those emails such as the sender, subject and message details. Here is an example of a routine that you can use to copy details of a formatted email to an Excel workbook. It is started from within Outlook after selecting the Mail Items that need to be processed.

Note : You will need to specify your own Excel Workbook and Mail Item formats in order to process the records. In some case a warning message generated from Outlook will be presented on execution of the macro. The warning is an check from Outlook that a process is trying to access Email Addresses. To successfully run the macro simply continue and accept this warning.

Read and Save Email Item Details

Step 1

Create a workbook “Test Workbook.xls” in your “c:\Temp” folder. The workbook will be opened by the macro and all fields specified will be copied into the workbook.

Step 2

Open Outlook and then start the Visual Basic Editor (Alt + F11). In the project explorer window of the editor right click and select insert new module. Then copy the following macro into the editor.

Sub ReadAndSaveDetails()
'This routine will read each of the selected emails and then update those values to a Workbook
Dim myItem As Object
Dim myOlApp As New Outlook.Application
Dim myOlExp As Outlook.Explorer
Dim myOlSel As Outlook.Selection
Dim myExcelApp As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myRange As Range
Dim i As Integer

'Work on Selected Items
Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection

'Create an Excel Application
Set myExcelApp = CreateObject("Excel.Application")
myExcelApp.Application.Visible = True

'Now Open the Workbook for Update
myExcelApp.Workbooks.Open FileName:="c:\temp\Test Workbook.xls"
Set myWorkbook = myExcelApp.ActiveWorkbook

'First lets set the range for the Workbook ready to take the new values
Set myRange = myWorkbook.Worksheets("Sheet1").Range("A1")
i = 1

'Now for each Item
For Each myItem In myOlSel
myRange.Offset(i, 0).Value = myItem.SentOn
myRange.Offset(i, 1).Value = myItem.Subject
myRange.Offset(i, 2).Value = myItem.Body
myRange.Offset(i, 3).Value = myItem.SenderEmailAddress
i = i + 1

MsgBox "Email Details – " & myItem.SentOn & vbNewLine & myItem.Subject & vbNewLine & myItem.Body


myWorkbook.Close SaveChanges:=True

Set myWorkbook = Nothing
Set myExcelApp = Nothing

'Free Storage
Set myItem = Nothing
Set myOlApp = Nothing
Set myOlExp = Nothing
Set myOlSel = Nothing

End Sub

Step 3

Select some Mail Items from Outlook for copying

Step 4

Go back to the Visual Basic Editor and run the macro “ReadAndSaveDetails”

Note:At this point you may encounter a warning from Outlook saying that a process is trying to access your mail file. Continue through this warning and for each Email Item you will see a Message Box indicating the information being copied into the workbook.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.