How to Auto Record File Name & Path When Saving Email Attachment in Your Outlook

When saving email attachment in Outlook, you may desire to record the file name, path and some other related information in an Excel file. This article will offer you a piece of VBA code that can automate recording.

In order to better manage email attachments, you may want to record them in an Excel spreadsheet when saving email attachment to local drive. However, by default, Outlook doesn’t offer such a feature. Fortunately, you can use the following VBA code to realize it like a breeze.Auto Record File Name & Path When Saving Email Attachment in Your Outlook

Auto Record File Name & Path When Saving Email Attachment

  1. For a start, launch your Outlook program.
  2. Then press “Alt + F11” key shortcut to access Outlook VBA editor.
  3. Next you will get into the “Microsoft Visual Basic for Applications” window.
  4. Subsequently, you ought to open a blank module and copy the following VBA codes into this module.
Sub SaveAndRecordInExcel()
    Dim strExcelFile As String
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim nRow As Integer
    Dim objAttachmentSelection As AttachmentSelection
    Dim objAttachment As Attachment
    Dim strPath, strFilePath As String
 
    'Change the path to your own Excel file
    strExcelFile = "E:\Outlook\Attachment Records.xlsx"
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Open(strExcelFile)
    Set objExcelWorksheet = objExcelWorkbook.Sheets("Sheet1")
 
    strPath = SelectLocalFolder("")
 
    Set objAttachmentSelection = Outlook.Application.ActiveExplorer.AttachmentSelection
 
    For Each objAttachment In objAttachmentSelection
 
        strFilePath = strPath & "\" & objAttachment.FileName
        objAttachment.SaveAsFile strFilePath
   
        nRow = objExcelWorksheet.Range("B" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
        'Fill in the file and path info
        objExcelWorksheet.Range("A" & nRow) = nRow - 1
        objExcelWorksheet.Range("B" & nRow) = objAttachment.FileName
        objExcelWorksheet.Range("C" & nRow) = Round(objAttachment.Size / 1024, 1) & " KB"
        objExcelWorksheet.Range("D" & nRow) = strFilePath
        objExcelWorksheet.Range("E" & nRow) = objAttachment.parent.parent.Name & " - " & objAttachment.parent.Subject
    Next
 
    'Fit the columns from A to E
    objExcelWorksheet.Columns("A:E").AutoFit
  
    'Save the changes and close the Excel file
    objExcelWorkbook.Close SaveChanges:=True
End Sub

Function SelectLocalFolder(varStartingFolder As Variant) As String
    Dim objShell As Object
    Dim objFolder As Object
 
    On Error Resume Next
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.BrowseForFolder(0, "Select the destination folder for saving attachment:", 0, varStartingFolder)
 
    SelectLocalFolder = objFolder.self.Path
End Function

VBA Code - Auto Record File Name & Path When Saving Email Attachment

  1. After that, you should add the new macro to Quick Access Toolbar or ribbon for later convenient access.
  2. Later you need to check if Outlook has been set to allow macros running.
  3. Eventually you can have a try.
  • Firstly, select the attachments which you want to save and record.
  • Then click the macro button in Quick Access Toolbar.Select Attachments & Trigger Macro
  • Next you will need to select to save the attachments, like the following screenshot:Select Folder for Saving Attachment
  • After saving, you can open the predefined Excel file and will see that the file and path information for the attachments have been recorded, like the following screenshot:Excel File Recording Attachment Info

Remove Large Attachments from Outlook

In order to block PST corruption, you have to always keep your PST file in small size. One of the best and most suggested ways is to save the large attachments to local drive and then remove them from your Outlook file. Otherwise, you’ll regret it a lot when your PST file gets corrupt due to too large size.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.