Home / Outlook / Copy Email Items to Excel Workbook

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
 
    Next
 
    myWorkbook.Close SaveChanges:=True
    myExcelApp.Quit
 
    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.

About Excel Help Desk

5 comments

  1. Hi, I got an error on the first line, it says that User-defined type not defined.

    • Camille,
      What version of Windows and Office are you running ?
      Have you created the workbook inside in Excel
      Regards
      ExcelHelpDesk Support Team

  2. Hello!

    This has been super helpful to me, thank you so very much! I’ve used this several times, but had to modify it to get it working.
    When I copied the code in and ran the macro, the debugger stopped me at the ‘myitem.SenderEmailAdress’ line with the message “Run-time error ‘438’: Object doesn’t support this property or method”. When I got rid of the line, it was perfect. But it would be great to have this working. I’m currently on Office 2002, would you happen to know what this means or have a work around?

    Thanks again, you’ve helped so much already!

    Alex.

    • Alex,
      We don’t have an Office 2002 environment in which to replicate your error. But we can suggest the following.
      When running the macro in debug mode set a breakpoint on this line that results in the error. When you get to that line investigate what attributes are available for myitem by putting that object in the watch window.
      If you don’t know how to do that then simply comment out the line that has the errror and replace it with a value like “test@emailaddress.com”….see if the macro then proceeds…
      If it does and this is the only line with an issue you will need to investigate how to resolve the error….google is a great place to start…
      Regards
      ExcelHelpDesk

  3. solar power brisbane

    I wanted to construct a small message to thank you for these amazing suggestions you are sharing on this website. My extended internet look up has at the end of the day been paid with extremely good knowledge to share with my co-workers. I would repeat that many of us website visitors are truly endowed to be in a very good network with so many awesome people with very helpful opinions. I feel quite grateful to have discovered the web site and look forward to many more fabulous times reading here. Thank you once more for everything.