How to Auto Export Information of Incoming Emails to an Excel File with Outlook VBA

Outlook Data Recovery, Outlook Solutions March 7, 2017

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

  1. 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.Create an Excel file
  • Lastly save it in your desired name and desired folder.
  1. After that, you should launch Outlook.
  2. Then press “Alt + F11” key buttons, which will bring you to VBA editor.
  3. Next in the popup window, you should double click the “ThisOutlookSession” on the left side.
  4. 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

VBA Codes - Auto Export Information of Incoming Emails to an Excel File

  1. Later you ought to sign this code.
  2. Subsequently, restart Outlook to activate the new VBA project.
  3. 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:Excel File Containing Information of Incoming Emails

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.

Author Introduction:

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

Comments are closed.