Import multiple emails (800 file) in one excel spreadsheet

[skyh3ck]

Hello Friends


Please see attached file or the below mentioned message, i have around 800 separate file like this, the file type is outlook email message.

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 800 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

Download Justdial App and Win an iPad! click here*

Lead Details

Caller Name :

M.satya

Caller Requirement :

Laboratory Testing Services For Water

Call Date & Time :

Wed, 12 Aug 2015 12:12:41

Branch Info :

Goregaon east

City :

Mumbai

Caller Phone :

+917208719243

Caller Email :

wapuaudit3@tatachemicals.com

For any queries, please feel free to call our Customer Support Division
on Tel. No 08888888888 and speak to Customer Support Executive OR send an email to mumbai@justdial.com

Warm Regards,
Team JustDial


Justdial Featured in :

Mint: The Learning's of VSS Mani

Forbes : Justdial E-Gamble

Economic Times : Tracing the journey of New Icons

Business World : Minting Money

INC India : How to Ride When the Going Gets tough

Man's World : Men of the Year 2013 - VSS Mani

 

[eibi]

Hello Sky.

Can you process a few of these emails manually and then post the Excel file so we can see what you want as a final result?

Thanks.

 

[skyh3ck]

I have attached few of the email files in zip attached, and have attached an excel file "pte.xlsx" to show an example on how i want data to get imported in excel.

i am only interested in the Lead Details from the message, it contains caller name, requirement, date, city etc. i just want that details in excel, also i want to prepare a dashboard based on that information, so if you can add some graphs or charts it will be helpful,

please take note there are around 800 messages like this to be imported in excel

 

[skyh3ck]

hello guys, please help me urgent, i am running out of time

 

[Deepak]

Save the excel in the same folder & try this..

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

 

[skyh3ck]

OK i saved the excel file you created in the same folder, i opened it but nothing happend.

what should i do to run your macro or do i have to create my self.

 

[Deepak]

Press alt+f8 then press run to do the task.

 

[skyh3ck]

ok guys i got an error as "Run time error 5", while runing macro, i have attached the image of the error, and two zip file with few emails, it does not allow me to attache more than 3 file here, i have another 3 zip file, will upload later, so you guys know exactly the situation

Again thanks very much for all your effort and help

 

[skyh3ck]

other three zip file attached, so it makes total 5 zip file which makes total count to 790+ emails, all this needs to be exported to excel in a list format

 

[Deepak]

The issue occurred due to layout mismatch in emails.

Check with attached.

 

[skyh3ck]

Tried it brother, still same error "Runtime error 5"

 

[Deepak]

Tried it brother, still same error "Runtime error 5"

If the same then no need to run the macro again,
Just download the attachment & enjoy as it has all data from your 800 emails.

 

[skyh3ck]

No man, see the attachment it has only 6 entry, there should be around 800 unique entries from each of the emails

 

[Deepak]

Sorry! My mistake as it was wrong attachment.

 

[skyh3ck]

First of all Thanks a lot man, you are a genius

however there are 549 entries, where as emails has around 800 entries, how can i add remaining entries,

also how you did this man, show me a way, every month i have spend hours and days just to prepare list like this from each and every email.

 

[skyh3ck]

i tried the macro my self, this time it worked fine, but there are 791 emails, and the macro only pulls 549 data, why it is not taking other emails data ???

 

[Deepak]

i tried the macro my self, this time it worked fine, but there are 791 emails, and the macro only pulls 549 data, why it is not taking other emails data ???

Glad that it worked!!

Only 549 : as other's might not have specific set of data.

 

[skyh3ck]

i appreciate all your efforts and hard work you put to help me, but without all those data i will not be able to submit it to my managers, in that case i have to open each and every email and copy paste manually, i dont want to do that as its time consuming.

is there are any i come to know which file data is not imported into excel, so i can open it and do manually,

can you add a column that will show the name of the file from which that particular data is pulled, so i can work out what is missing and request from the data provider a fresh file.

 

[Deepak]

Check this....

A Columns has been added to show the filename.

 

[skyh3ck]

Check this....

A Columns has been added to show the filename.

you missed to attach the file

 

[Deepak]

Done!!

 

[skyh3ck]

Thanks @Deepak i have separated the files which are not imported in previous file

this one is around 240+ files

i found out that the format of the file is different then other, hence it could not get imported, can you do a macro for this one also, later i can collate data in one file so that i have all 800 data in one file

Thanks again,

 

[Deepak]

Will check soon for a uniform code!!

 

[skyh3ck]

Thanks man, if you can create a macro that can import both type of emails in one single sheet, that will be great for me, and it will save me some time, i searched a lot but there is no easy way to import multiple emails in one single file.


or if not possible to import both in same file, i am ok if i have to run two separate macro file for both type of emails separately, i can collate into one sheet later my self

eagerly waiting for your reply

 

[Deepak]

It will be cover up by a single macro.