Reading contents of .msg file into a text file (1 Viewer)

[JohnLee]

Good morning,

I have been researching how to extract data from an .msg file into a text file and I have found some information which I was able to modify to achieve part of what I want, which is shown below:

Function ReadmsgFile()
Dim OL: Set OL = CreateObject("Outlook.Application")
Dim Msg ':Set Msg= CreateObject("Outlook.MailItem")
Set Msg = OL.CreateItemFromTemplate("G:\Scan - Verify\eFlow\Dynamics\Vaillant\Old No DPA Printed\Product Registration (1).msg")
'MsgBox Msg.Subject
Msg.saveAs "G:\Scan - Verify\eFlow\Dynamics\Vaillant\Old No DPA Printed\test.txt", olDoc
'The above statement will save the contents of .msg file into the designate .txt file
Set OL = Nothing
Set Msg = Nothing
End Function

So I've been able to extract the information from a single .msg file into a text file, which is fine so far, but I'm now at a bit of a standstill as I can't seem to work out how to use this code to run through a large volume of these .msg files [4,500 to be exact] and to extract that information into a text file.

Below is what the above code extracts into my test text file:

"From: noreply@vaillant.com
Sent: 26 June 2015 12:00
To: Naylor, Andy
Subject: Product Registration
First name: OPUS TRUSTEES
Surname:
Street & Nr: F4 128 MOUNT STREET
City: LONDON
Postcode: W1K 3ND,
Tel: 01992 560000
Mobile:
Email:
Do you have a voucher code?:
adddif:
Select a model: ecoTEC plus 624
serial numer (28 didgets): 21143800100116811300239238N5
DD-MM-YYYY Installation Date: 2015-06-11
Name:
Street:
Nr.:
Postcode: W1K 3ND,
Gas Safe Number (5/6 digits): 194105
Vaillant (Vaillant Group UK Limited) has appointed a third party service provider* (Service Provider) to
provide product registration and warranty administration services. The Service Provider may also offer
related protection cover to accompany Vaillant products. Vaillant and the Service Provider will use the
information you have given to provide the requested service and for administration, marketing, and
research purposes. Vaillant and the Service Provider would like to contact you about their respective
products and services by mail, telephone, email and/or other electronic messaging services.:
tc: 1
Vaillant and the Service Provider may also share your information with their other respective group
companies and selected third parties so that they may contact you by mail, telephone, email and/or
other electronic messaging services to tell you about offers, products or services which may be of
interest to you.:


John Lee
Scanning Operations Facilitator
Domestic & General"

Ideally what I would like to do is to extract the following information from each of the .msg files and place them as fixed width data in that text file:

So all those in bold print followed by a colon are the field names and the information to the right of those colons are the data I want to extract into my text file on a seperate line for each email message:

First name: OPUS TRUSTEES
Surname:
Street & Nr: F4 128 MOUNT STREET
City: LONDON
Postcode: W1K 3ND,
Tel: 01992 560000
Mobile:
Email:
Do you have a voucher code?:
adddif:
Select a model: ecoTEC plus 624
serial numer (28 didgets): 21143800100116811300239238N5
DD-MM-YYYY Installation Date: 2015-06-11
Name:
Street:
Nr.:
Postcode: W1K 3ND,
Gas Safe Number (5/6 digits): 194105

The field lengths would be as follows:

First name: 25 Characters
Surname: 25 Characters
Street & Nr: 30 Characters
City: 30 Characters
Postcode: 8 Characters
Tel: 15 Characters
Mobile: 15 Characters
Email: 50 Characters
Do you have a voucher code?: 3 Characters
adddif: 30 Characters
Select a model: 15 Characters
serial numer (28 didgets): 28 Characters
DD-MM-YYYY Installation Date: 10 Characters
Name: 30 Characters
Street: 30 Characters
Nr.: 30 Characters
Postcode: 8 Characters
Gas Safe Number (5/6 digits): 6 Characters

Any assistance would be most appreciated. By the way I am using Access 2000

Regards

John Lee

 

[vbaInet]

How good is your VBA John?

 

[JohnLee]

Hi vbaInet,

It's not as good as I would like it to be, most of the time I'm just dabbling with code that's already been written and then making adjustments to that code to get it to do what I need, if I can get it to do what I need, otherwise I'll search around to see what I can find and ask questions.

I think this code needs some sort of loop, do while/Do until or such like, but not absolutely sure, then there's bit of working out how to locate the data I want to extract, I've never done that before, but think that some code that looks for a string of data and then extracts the required data next to it, but don't know how to construct code like that.

Any assistance appreciated.

Regards

John Lee

 

[vbaInet]

Does each message file always have a "FirstName:" line?

 

[JohnLee]

Hi vbaInet,

Yes every file has a "First name:" line.

Regards

John Lee

 

[vbaInet]

Ok. First thing you need to do is research a method of reading a file line by line. I suggest you look into the File System Object. Feel free to ask if you have any specific questions.