Extracting data from a number .eml files into an excel table

[JNC125]

Guys and girls,

I was wondering if someone would be so kind so as to help me with the following task.

I have about 1000 .eml files, all saved in a folder on my PC. I would like to create an excel table, with the following columns: A. file name; B. Subject; C. From; D. To; E. Time; with the data for each column to be extracted from each of the .eml files, one by one. I understand this is entirely possible to achieve with an excel macro. Unfortunately I have very little idea how to write macros - if someone could help me, I would be in their debt.

Many thanks.
JNC

[Marc L]

An .eml file is it a text file ?

[p24leclerc]

try this macro:

Please Login or Register  to view this content.

You can use the attached file and click on the command button to read all files.
Do not forget to change the file path in the VBA code (See comment about that in the code)
Hope this helps

[JNC125]

Marc L,

An .eml file is an Microsoft Outlook file. Its basically an email saved into a folder.

p24leclerc

Thanks so much for replying.

I downloaded the file and changed the file path as you said I should. Unfortunately it does not seem to be working. When I click on the button, or if I click "Run macro", simply nothing happens. No error, just nothing.

I also searched on google and found another suggested solution on another forum, which is as follows:

Please Login or Register  to view this content.

Having changed the path name to an appropriate path there, it is also unfortunately not working.

I get a "Run-time Error '13'. Type Mismatch" Error. The bit of code highlighted by debugger is this:

Please Login or Register  to view this content.

When I hover the mouse over it, it says "Filelist=false". I suspect it is somehtign obvious, but I have no idea how to fix it.

PS: I realise that the second macro is for .msg files rather than .eml. This is not material, I would be happy with a macro that ran with either (I could convert .eml to .msg if necessary). For the avoidance of doubt the folder the path of which I inserted into macros has both .eml and .msg files at the moment (just a few dummy emails I created to run the test), so I am sure it is not the file type that is causing these macros to fail.

Guys, any thoughts on the above?

[Marc L]

As I know what's a standard .eml file, my question was : your .eml file is it a text file ?

And post #3 code is well working by following its directive : update each path in the code …

[JNC125]

Marc L,

I'm not sure how to answer your question.

In my test folder, there are about 10 files. The files are currently set to open with Outlook. I suppose you could open them with a notepad, and all of them just have just a few lines of text. Does that make them text files?


However, I would like to run the macro on a larger number of emails. Some of those email have attachments (including word files, pdf files, jpeg files, and other emails), so I am not sure whether all of them would be capable of being opened with a notepad, and as such whether they can be considered as "text" files.

[Marc L]

Attach at least a sample .eml file or two …

[JNC125]

For the sake of preserving anonymity, I'd prefer not to.

[p24leclerc]

Here is the EML sample file I used to program the macro.
I had to change the extension in order to be able to attached it to this thread. But you can revert it to .eml.
Macro is looking for specific words in specific order. See in your eml files if they are similar to that sample I got from the internet.
Maybe all eml files are not the same. I don't know.

As stated by Marc, you have to change the path in 2 or 3 places in the macro.
You could edit one of your eml files and change sensitive informations so we can see your format. You can edit them with NotePad.
Hope this helps