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
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.
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
Select some Mail Items from Outlook for copying
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.