This macro collects the fields from each Outlook message in a selection and writes the values of the fields to an Excel worksheet. It's easily adapted to work with any field and any Outlook item type.
In Excel 2016, rCount is finding the last USED line, not the next blank line. Use rCount = rCount + 1 to move down one line.
Option Explicit Sub CopyToExcel() Dim xlApp As Object Dim xlWB As Object Dim xlSheet As Object Dim rCount As Long Dim bXStarted As Boolean Dim enviro As String Dim strPath As String Dim currentExplorer As Explorer Dim Selection As Selection Dim olItem As Outlook.MailItem Dim obj As Object Dim strColB, strColC, strColD, strColE, strColF As String ' Get Excel set up enviro = CStr(Environ("USERPROFILE")) 'the path of the workbook strPath = enviro & "\Documents\test.xlsx" On Error Resume Next Set xlApp = GetObject(, "Excel.Application") If Err <> 0 Then Application.StatusBar = "Please wait while Excel source is opened ... " Set xlApp = CreateObject("Excel.Application") bXStarted = True End If On Error GoTo 0 'Open the workbook to input the data Set xlWB = xlApp.Workbooks.Open(strPath) Set xlSheet = xlWB.Sheets("Sheet1") ' Process the message record On Error Resume Next 'Find the next empty line of the worksheet rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row 'needed for Exchange 2016. Remove if causing blank lines. rCount = rCount + 1 ' get the values from outlook Set currentExplorer = Application.ActiveExplorer Set Selection = currentExplorer.Selection For Each obj In Selection Set olItem = obj 'collect the fields strColB = olItem.SenderName strColC = olItem.SenderEmailAddress strColD = olItem.Body strColE = olItem.To strColF = olItem.ReceivedTime 'write them in the excel sheet xlSheet.Range("B" & rCount) = strColB xlSheet.Range("c" & rCount) = strColC xlSheet.Range("d" & rCount) = strColD xlSheet.Range("e" & rCount) = strColE xlSheet.Range("f" & rCount) = strColF 'Next row rCount = rCount + 1 Next xlWB.Close 1 If bXStarted Then xlApp.Quit End If Set olItem = Nothing Set obj = Nothing Set currentExplorer = Nothing Set xlApp = Nothing Set xlWB = Nothing Set xlSheet = Nothing End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor