Kamram was looking for a way to create a log file of messages and their attachments. While you can simply select all messages in a folder and copy to Excel, this won't include the attachment names.
Fortunately, picking up the attachment names and creating a new message or sending the data to Excel is not difficult to do using VBA.
Both macros below create a log of every message in the selected folder. The Excel version includes the folder name.
To use, add the macro Outlook's VBA editor, select the folder you want to log then run the macro.
Create a Log in a New Message Form
Public Sub LogAttachments() Dim oItem As MailItem Dim log As MailItem Dim folder As folder Dim oAtt As Attachment Dim strAtt As String Dim strMail As String Dim selItems As Items Dim olInspector As Outlook.Inspector Dim olDocument As Word.Document Dim olSelection As Word.Selection Set log = Application.CreateItem(olMailItem) Set mfolder = Application.ActiveExplorer.CurrentFolder Set selItems = mfolder.Items For Each oItem In selItems strAtt = "" strMail = "" If oItem.Attachments.Count > 0 Then For Each oAtt In oItem.Attachments strAtt = oAtt.FileName & "; " & strAtt Next oAtt Else strAtt = "No Attachments" End If strMail = oItem.SenderName & vbTab & oItem.Subject & vbTab & oItem.ReceivedTime & vbTab & " Attachments: " & strAtt & vbCrLf & vbCrLf log.Display Set olInspector = Application.ActiveInspector() Set olDocument = olInspector.WordEditor Set olSelection = olDocument.Application.Selection olSelection.InsertBefore strMail Next Set oItem = Nothing set olInspector = Nothing set olDocument = Nothing set olSelection = Nothing End Sub
Log Messages to Excel
Option Explicit Sub LogToExcel() Dim olItem As Outlook.MailItem Dim xlApp As Object Dim xlWB As Object Dim xlSheet As Object Dim vText, vText2, vText3, vText4, vText5 As Variant Dim rCount As Long Dim bXStarted As Boolean Dim enviro As String Dim strPath As String Dim mfolder As folder Dim oAtt As Attachment Dim strAtt As String Dim strMail As String Dim selItems As Items 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") Set mfolder = Application.ActiveExplorer.CurrentFolder Set selItems = mfolder.Items For Each olItem In selItems strAtt = "" strMail = "" If olItem.Attachments.Count > 0 Then For Each oAtt In olItem.Attachments strAtt = oAtt.FileName & "; " & strAtt Next oAtt Else strAtt = "No Attachments" End If 'Find the next empty line of the worksheet rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row rCount = rCount + 1 vText = olItem.SenderName vText2 = olItem.ReceivedTime vText3 = olItem.Subject vText4 = strAtt vText5 = mfolder.Name xlSheet.Range("B" & rCount) = vText xlSheet.Range("c" & rCount) = vText2 xlSheet.Range("d" & rCount) = vText3 xlSheet.Range("e" & rCount) = vText4 xlSheet.Range("f" & rCount) = vText5 Next xlWB.Close 1 If bXStarted Then xlApp.Quit End If 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