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
- For a start, launch your Outlook program.
- Then press “Alt + F11” key shortcut to access Outlook VBA editor.
- Next you will get into the “Microsoft Visual Basic for Applications” window.
- 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
- After that, you should add the new macro to Quick Access Toolbar or ribbon for later convenient access.
- Later you need to check if Outlook has been set to allow macros running.
- 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.
- Next you will need to select to save the attachments, like the following screenshot:
- 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:
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.
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