Many users hope that Outlook can automatically export the primary information of incoming emails to an Excel file. This article will look at this requirement and introduce a quick method via Outlook VBA.
Sometimes, for some reasons, such as mail statistics, you may frequently need to export the emails to an Excel file. Generally, for the existing received emails, you can simply utilize the “Import and Export” feature to export them to Excel in one go. However, perhaps you even desire that Outlook can auto export the incoming emails to Excel as soon as they arrive in your mailbox. Without any doubts, there is no such a native feature in Outlook . Therefore, you need to employ other approaches, like a 3rd party add-in or Outlook VBA. Here we will teach you how to make it autoated with Outlook VBA. Read on to get the steps and according codes.
Auto Export Information of Incoming Emails to an Excel File
- In the first place, you ought to create an Excel file.
- Open a new Excel file.
- Then you should type “No.” in Cell A1, “Sender Name” in Cell B1, “Sender Email Address” in Cell C1, and “Email Subject” in Cell D1, and “Received Time” in Cell E1, like the following image.
- Lastly save it in your desired name and desired folder.
- After that, you should launch Outlook.
- Then press “Alt + F11” key buttons, which will bring you to VBA editor.
- Next in the popup window, you should double click the “ThisOutlookSession” on the left side.
- Subsequently, in the “ThisOutlookSession” project window, you need to copy and paste the following VBA codes into it.
Public WithEvents objMails As Outlook.Items Private Sub Application_Startup() Set objMails = Outlook.Application.Session.GetDefaultFolder(olFolderInbox).Items End Sub Private Sub objMails_ItemAdd(ByVal Item As Object) Dim objMail As Outlook.MailItem Dim strExcelFile As String Dim objExcelApp As Excel.Application Dim objExcelWorkBook As Excel.Workbook Dim objExcelWorkSheet As Excel.Worksheet Dim nNextEmptyRow As Integer Dim strColumnB As String Dim strColumnC As String Dim strColumnD As String Dim strColumnE As String If Item.Class = olMail Then Set objMail = Item End If 'Specify the Excel file which you want to auto export the email list 'You can change it as per your case strExcelFile = "E:\Email\Email Statistics.xlsx" 'Get Access to the Excel file On Error Resume Next Set objExcelApp = GetObject(, "Excel.Application") If Error <> 0 Then Set objExcelApp = CreateObject("Excel.Application") End If Set objExcelWorkBook = objExcelApp.Workbooks.Open(strExcelFile) Set objExcelWorkSheet = objExcelWorkBook.Sheets("Sheet1") 'Get the next empty row in the Excel worksheet nNextEmptyRow = objExcelWorkSheet.Range("B" & objExcelWorkSheet.Rows.Count).End(xlUp).Row + 1 'Specify the corresponding values in the different columns strColumnB = objMail.SenderName strColumnC = objMail.SenderEmailAddress strColumnD = objMail.Subject strColumnE = objMail.ReceivedTime 'Add the vaules into the columns objExcelWorkSheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1 objExcelWorkSheet.Range("B" & nNextEmptyRow) = strColumnB objExcelWorkSheet.Range("C" & nNextEmptyRow) = strColumnC objExcelWorkSheet.Range("D" & nNextEmptyRow) = strColumnD objExcelWorkSheet.Range("E" & nNextEmptyRow) = strColumnE '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
- Later you ought to sign this code.
- Subsequently, restart Outlook to activate the new VBA project.
- Eventually, from now on, all the incoming emails will be auto exported to the preset Excel file as soon as they land into your Inbox folder. The excel file will look like the following image:
Avert Painful PST Data Corruption
Outlook has earned much kudos due to its various features. However, it doesn’t mean that is foolproof. It still can get compromised at any time. For instance, if you frequently close your Outlook improperly, it is very likely that your PST data will be put into danger. In this case, if you would like to repair PST data, you have to take recourse to an experienced tool, such as DataNumen Outlook Repair.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover mdf and outlook repair software products. For more information visit www.datanumen.com