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