Import multiple eml files data to single excel file

[lucky5968]

Hello Friends

Please see attached file or the below mentioned message, i have around 884 separate .eml file.
I want to prepare a report in excel with all these messages, now its very difficult to open and copy paste all message one by one, can you guys help me how can i import all 880 messages in excel in a proper format.

the attached is a sample file.

Please help me with this, i need to submit it by saturday morning to my boss

i have tried one from your site only but it is not working for my files.

 

[Marc L]

Hi !

At least explain us how manually you import this kind of file within Excel !
'Cause if it works under Excel, just activating the Macro Recorder
you will get your own free base of code !

Where is your try (code) ?
​

 

[lucky5968]

i have uploaded email files

i have tried following code in excel macro

Sub get_from_eml_1()

Const Rfile As String = "*.eml"
Dim strDir As String, strfile As String, txt As String
Dim t1 As Double, t As Double, x, n As Integer, r As Integer, p As Variant

Application.ScreenUpdating = False

strDir = Application.ThisWorkbook.Path & "\"
strfile = Dir(strDir & Rfile)
r = 2
Do Until strfile = ""
    txt = CreateObject("scripting.filesystemobject").opentextfile(strDir & strfile).readall
        t = InStr(1, txt, "Lead Details", vbTextCompare)
        t1 = InStr(1, txt, "For any queries,", vbTextCompare)

x = Split(Trim$(Mid$(txt, t, t1 - t)), vbCrLf)

For n = LBound(x) To UBound(x)
    x(n) = Trim(Mid(x(n), InStr(1, x(n), ":") + 2))
Next

Cells(r, 1).Resize(, UBound(x)).Value = x
Cells(r, 9) = Mid(x(2), InStrRev(x(2), "For") + 4)
Cells(r, 10) = Mid(Trim(x(3)), 7, 11)

r = r + 1
strfile = Dir
Loop


With Range("A2:A" & r - 1)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
  
Application.ScreenUpdating = True
End Sub

MOD EDIT: CODE TAGS ADDED.

 

[Marc L]

Please, edit your post and embed your code with code tags
or using code icon …

Then explain the issue with your code, technically what's wrong, what …
And as per forum rules, join a result workbook according
to the first two sources text files attached in your initial post
like you should create it manually.
​

 

[lucky5968]

I am new to vb
I got code from your site gave by deepak for similar issue of importing data from
Emails to excel file
I tried it but I am unable to extract data so I asked help from you people
Kindly help it as there is no time for me
If you want i post the link from where I got the vb or macro

 

[Chihiro]

As Marc stated in his post. Without you supplying us with what the output from these files should look like... we can't really help you.

Take 2 files and create manually what you want as result in Excel and attach. From there we can guide you in best approach.

 

[Marc L]

« New to VB » or not, forum rules must be respected !

1)

Please, edit your post and embed your code with code tags
or using code icon …

So easy so do it !

2)

And as per forum rules, join a result workbook according
to the first two sources text files attached in your initial post
like you should create it manually.

As here it is very not a mind readers forum,
we can't guess expected result ‼
So easy to understand at least with a result workbook joined …

The better initial presentation, the better & quicker solution.

And do not forget the Macro Recorder when you are in a hurry !
'Cause all you can do manually within Excel can be automatized by
macro (end 20 years ago !) a VBA code and here it's a beginner level
text files import procedure. Yes .eml files are just nothing than text files
and if you tell me it is not text files so I will not take a glance and move away.
Easy to check just opening an eml file under Excel ! (basics)

Tomorrow I will not have much time to spend on any forum …
​

 

[lucky5968]

My output excel file should contain 3 fields of that eml files
Project name, name ,mobile & email, i didn't get your question in previous query sorry for that
I will uplod excel file also shortly.

 

[lucky5968]

This is out put file format i needed

 

[Marc L]

Paste next code to the result worksheet module ! Then save the workbook …

As a starter, like a beginner code 'cause its base was created via the
Macro recorder as it's just about directly opening in Excel a eml file
like any html text file or any workbook :​

Sub emlImport()
      Me.UsedRange.Offset(1).Clear
      Application.ScreenUpdating = False
      R& = 1
      P$ = ThisWorkbook.Path & "\"
      F$ = Dir(P & "*.eml")
While F > ""
      Application.StatusBar = "        Opening eml text file #" & R
      DoEvents
      R = R + 1
      Workbooks.Open P & F
      Cells(R, 1).Resize(, 4).Value = Application.Transpose(ActiveSheet.[C3:C6].Value)
      ActiveWorkbook.Close False
      F = Dir
Wend
      Range("C2:C" & R).NumberFormat = "#"
      Me.UsedRange.Columns.AutoFit
      Application.ScreenUpdating = True
      Application.StatusBar = False
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[lucky5968]

while i am running the macro provided it is showing compilation error as "invalid use of me keyword".

 

[Marc L]

Bad paste ‼ See why by reading Me VBA inner help …

At least read and apply the first red sentence of my previous post !​

 

[lucky5968]

Sorry to say, i did same but i am getting same error

 

[Marc L]

No error on my side !

Same error means bad reading and bad pasting !
So just well apply the red advise before the code
(working only in the worsheet module as written, not in a standard one)
or like I did, activate Macro recorder, open a eml file like any workbook,
copy the cells to the destination worksheet and stop the recorder :
you will get a base code and with codes of deepak & mine
and just reading VBA inner help you can easily achieve a mock up !
​

 

[Chihiro]

To make it easier to follow...

Here's screen shot of where you should have Marc's code.


Your result sheet may be different. But whichever sheet it is, double click in VBE and paste the code there.

As well make sure to have ".eml" files in same folder as where the workbook is saved.

 

[lucky5968]

I have pasted workbook macro only but I didn't got, i will the procedure what you have mentioned now
Thanks for suggestion

 

[lucky5968]

Kindly send me the excel on which you have created a macro so that it will work for me please

 

[lucky5968]

please send the file on which you have tested

 

[Marc L]

As I'm not on a computer at least for a day (just a pad)
- don't need a specific workbook, can work with a new one ! -
if you do not succeed to follow easy child level direction
and illustrated by Chihiro, so as yet written & for the last time
activate the Macro recorder and operate manualy
for a couple of text files, follow post #14.
Then if you are not able to mod the generated code by the recorder
- at beginner level and with the material of both given codes -
at least post here this generated code and again as yet asked
embedded between code tags (follow tip or just use the code icon)
then someone may mod it …
​

 

[lucky5968]

I have tried i didn't get that... I have combined and edited both code kindly send that fine when you have access to system...
If it is on Monday also i have no problem... Kindly upload the file...
Deepak also posted file when that person told it is not working...
So I will try that one please post the file

 

[Marc L]

Just well read again posts #10, 14 & 15 ! (child level)

Or, again if you just follow this (a 10 years old child can easily do it !)

so as yet written & for the last time
activate the Macro recorder and operate manually
for a couple of text files, follow post #14.
At least post here this generated code and again as yet asked
embedded between code tags (follow tip or just use the code icon)
then someone may mod it …

any helper - even a beginner - can mod your generated code
once posted here according to your basic need !

Monday it would be difficult for me during the day,
maybe at noon if off duty …
​