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
- 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.
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
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..