Mondays Mission: Macro to extract text from an email and paste it into an EXCEL Sheet

[maxwello]

Good Morning All,
I don’t know if this is possible but have hundreds of emails that I need to collect the data that is held in the body of the email. All the emails have the same text layout and the fields that I want to extract have the same headings. (See below example)

I would really appreciate any assistance or advice that you are able to provide.

Dear Sir,
Please find my account setup below.</SPAN>

User Name:Joe Blogs</SPAN>
User Firm:Blogs Inc</SPAN>
Email address: Joe.blogs@blogsinc.co.uk</SPAN>
Country:ENGLAND</SPAN>
UUID:1111111</SPAN>
User #:123456</SPAN>
Cust #:123456</SPAN>
Firm #:123456</SPAN>
Serial #:123456</SPAN>
Broker:ABAX</SPAN>
Application:Windows</SPAN>

 

[jowoo]

Comfy and Simon the formatting not being retained is properties like font formatting or font colour and pivot table colour. I have some cells in bold and some in a font colour other than black.

 

[s.ridd]

jowoo, I can't recreate your error in Office 2007, 2010 or 2013 no matter what I try, which suggests that you are using an older version of the software. Unfortunately this means that I can't really help you because I no longer have access to any of those products.

In Outlook 2007 and later Word is the default item editor, but in older versions you can choose your default editor, it is possible that yours is set to Plain Text. This page describes how you can change the default editor if that is the case.

 

[maxwello]

Hi Comfy,
I am looking for some additional help; do you have any experience of extracting data from customised Outlook forms with multiple tabs? I wanted to tweak my current macro for another use but can’t get the data out of the form.
</SPAN>

Your help as always is greatly appreciated.

Regards
Oliver

 

[Comfy]

I don't I'm afraid but if you posted more info I'm sure we could figure something out.

It might be easier if you could send me a copy of the form you are working with??

 

[Comfy]

Thanks for the attachment.

Yep, we can extract data from the mailitem. Do you want me to post examples here? or would you prefer by email? As I'm not sure whether the field names are of a sensitive nature.

 

[maxwello]

Thansk Comfy,
To the rescue as allways.. Happy for it to be posted.

 

[Comfy]

There will be a couple of ways to do it.

I would suggest that you create an array, something like UserPropertiesArray and list each item you want to extract in the order that you want them to appear in your workbook.

If you don't know the name of each control you can get them by doing the following:

In the Developer Tab select "Choose Form"
Select your form
In the forms Developer Tab select "Design This Form"
Right click a control and select "properties"
Select the Value tab
The name you are looking for will be in the text box between the "Choose Field" button and the "New..." button.

So you can then loop through each mailitem in a folder. And then loop through each of the names in your array.

Dim UserPropertiesArray(1 To 1) As String

UserPropertiesArray(1) = "Platform"

Set OutApp = New Outlook.Application
Set oMAPI = OutApp.GetNamespace("MAPI")
Set oParentFolder = oMAPI.Folders("Your MAPI name")
Set oFolders = oParentFolder.Folders("Inbox")
Set oFolder = oFolders.Folders("Test")


i = 2
For Each oMailItem In oFolder.Items
    For j = LBound(UserPropertiesArray) To UBound(UserPropertiesArray)
        Cells(i, j).Value = oMailItem.UserProperties(UserPropertiesArray(j))
    Next i
i = i + 1
Next oMailItem

Something like this. Let me know if you need something more comprehensive.

 

[maxwello]

Perfect, thanks.

One last questions is there anyway to limit the date range that it runs through? As the folder grows months and months of request will be in there and I will want to only pull the data for say a week.

 

[Comfy]

Perfect, thanks.

One last questions is there anyway to limit the date range that it runs through? As the folder grows months and months of request will be in there and I will want to only pull the data for say a week.

Yes, we can use the restrict method to limit the mailitems to be processed.

So something like this:

Sub ExtractData()
Dim UserPropertiesArray(1 To 1) As String
Dim OutApp As New Outlook.Application
Dim oMAPI As Outlook.Namespace
Dim oParentFolder As Outlook.MAPIFolder
Dim oFolders As Outlook.Folder
Dim oFolder As Outlook.Folder
Dim RestrictedItems As Outlook.Items
Dim oMailItem As Outlook.MailItem
Dim sFilter As String
Dim i As Long, j As Long


sFilter = "[LastModificationTime] >= '11/11/2013' AND [LastModificationTime] <= '17/11/2013'"
    
UserPropertiesArray(1) = "Platform"


Set OutApp = New Outlook.Application
Set oMAPI = OutApp.GetNamespace("MAPI")
Set oParentFolder = oMAPI.Folders("Your MAPI name")
Set oFolders = oParentFolder.Folders("Inbox")
Set oFolder = oFolders.Folders("Test")
Set RestrictedItems = oFolder.Items.Restrict(sFilter)


i = 2
For Each oMailItem In RestrictedItems
    For j = LBound(UserPropertiesArray) To UBound(UserPropertiesArray)
        Cells(i, j).Value = oMailItem.UserProperties(UserPropertiesArray(j))
    Next j
i = i + 1
Next oMailItem




End Sub

You might want to implement some type of form or text box to enter the dates rather than hard coding them.

 

[maxwello]

This works perfectly after a few tweeks and it now produces a great powerpoint presentation. I am now trying to pick up infromation from a number of other emial folders but this time I only need the subject line, the date it was recieved, the to field and the catetgory that has been assigned to it.

I have tried amneding the previous scripts and set the Headers and UserPropertiesArray(1) with things like "To:" "msg.to" and the best result came from setting the header as "[msg.subject]" but this then returnd the same results for "to:".

I am sorry to ask you again but can you help again with extracting that info as well as helping to work out how to make it search all sub folders under a folder.

Example structure:
Inbox
bloomberg
tradeweb
markit

Thansk again comfy