#Emails data extractor full#
#Emails data extractor code#

#Emails data extractor code#

The cleaned text is then written to “Sheet1” in Column A.įor the HTML table-based information extract, the code obtains a reference to the HTML table tag in the source data through the getElementsByTagName. Surrounding whitespace, line feeds, form feeds, and carriage returns are removed using through a combination of Trim and Replace functions. Now that the Body has been properly delimited, the Split function is used to extract the text found between the delimiters. As an example, the newly delimited Body would appear as follows after the replacing the phrases: Replacing those phrases with a common delimiter makes it easier to extract the text found between the delimiters. It then loops through all of the items in the folder looking for any items with the phrase “Template Email Subject” in the Subject.įor the text-based information extract, the code takes the item’s Body and replaces the text phrases “The account for” and “has been created.” with “#”. Using the example e-mail above, the following code connects to Outlook and obtains a reference to the folder named “Automation” in the default inbox. Again, there are some hardcoded values that would need to be changed, e.g. This code has been tested in Microsoft Office Professional Plus 2019. The text-based extract is performed using a combination of REPLACE and SPLIT functions assuming there is common text surrounding the information to extract while the structured table data is extracted using HTML object library functions.

This example will use two different methods to extract the information. The macro also extracts the additional information about the user such as Last Name, First Name, Name, Department, Company, and Job Title from the structured table. John Dalesandro, and adds it to an Excel worksheet.

#Emails data extractor full#

Using the sample e-mail below, the macro extracts the Full Name, e.g. The e-mail also contains a structured table with a few additional attributes. Each e-mail has the Subject “Template Email Subject” and the content follows the format “The account for has been created.” where represents the full name associated with the created account. I receive account creation e-mails and I need to track the names and attributes associated with those accounts in an Excel worksheet. I’ll use a simple example to demonstrate the code.

a human performing a copy/paste from each e-mail. By using VBA to automate the data extract from Outlook directly into Excel, the data quality/accuracy is improved and it’s significantly faster over large volumes of e-mail vs. In this scenario, assume that well-formatted e-mails containing information to be added to an Excel sheet are received.