How do I automatically convert Outlook data to Excel?

I found some code to export an email body to an excel sheet below (modified for my purposes, of course). The issue is, the email body has a lot of line breaks and when it writes to the sheet, Excel automatically wraps the text. I tried to set the range to column A and turn of the wrap text, but it fails every time. I’ve tried different iterations of the range, i.e. A2:A50, etc., but the same thing happens. How can I get the macro to turn off wrap text for either column A or the cell it just wrote to?

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.
    How do I automatically convert Outlook data to Excel?
  • 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
    'Save the changes and close the Excel file
    objExcelWorkBook.Close SaveChanges:=True
End Sub

How do I automatically convert Outlook data to Excel?

  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:
    How do I automatically convert Outlook data to Excel?

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

How do I automatically export from Outlook?

If you want to backup Outlook Exchange emails locally, you can have it done automatically using the AutoArchive feature: File > Options > Advanced > AutoArchive > AutoArchive Settings. Alternatively, you can export your emails to a . pst file, and restore it later by importing.

How do I pull data from Outlook?

Select File > Open & Export > Import/Export. Select Export to a file, and then select Next. Select Outlook Data File (.pst), and select Next. Select the mail folder you want to back up and select Next.

How to auto export information of incoming emails to an Excel file with Outlook VBA?

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.

How do I extract data from the body of an email in Excel?

How to Extract Email to Excel.
Step 1: Create a New Parser Inbox. ... .
Step 2: Send Some Test Emails to the Parser. ... .
Step 3: Teach the Parser How to Read Your Emails. ... .
Step 4: Have Your Emails Automatically Forwarded to the Parser. ... .
Step 5: Start Putting Your Parsed Data to Work..