Import multiple emails (800 file) in one excel spreadsheet

[Deepak]

Hi,

Pls check this..

I haven't tested much but might be useful.

Test it & share the remarks & i will validate the same tomorrow.

Sub get_from_eml_3()

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
Dim strDate As String, i As Integer
Application.ScreenUpdating = False

strDir = Application.ThisWorkbook.Path & "\"
strfile = Dir(strDir & Rfile)
r = 2

Do Until strfile = ""
    txt = CreateObject("scripting.filesystemobject").OpenTextFile(strDir & strfile).readall
        For i = 2 To 8
            x = Split(txt, Cells(1, i))
            If Not InStr(txt, Cells(1, i)) > 0 Then GoTo n1
                Cells(r, i) = Trim(Split(Split(x(1), Chr(10))(0), ":")(1))
n1:    Next

On Error Resume Next
If Len(Cells(r, 3)) > 0 Then Cells(r, 9) = Split(Cells(r, 3), " For ")(1)
On Error GoTo 0
Cells(r, 10) = Trim(Mid(Cells(r, 4), 6, 12))
If Not IsDate(Cells(r, 10)) Then Cells(r, 10) = ""
Cells(r, 11) = strfile

r = r + 1

L6: strfile = Dir
Loop
Cells(1, "K") = "File Name"

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]

do you mean that the macro will import all 791 email one time

can i use it every month, irrespective of how many emails i have,

all i want is that it should import all lead details like name, location, product, date, enqury in its respective field, and if any thing is missing it shoule leave that cell blank.

thanks man

 

[skyh3ck]

Ok pasted macro in one file, then pasted the file in the same folder with all emails in it, i ran the macro and then got "Run time error 9" subscript out of range.

am i doing anything wrong here

 

[Deepak]

Just tested & found working..

 

[skyh3ck]

Thanks man, thats what i wanted to do.

Every month i get data like this, can i use the same file again and again to import data ?

from the file, i have created a pivot table, now i want to show chart beside it, that shows the top 5 products only, i have tried pie chart, but it includes all data,i want to show only top 5 products

 

[skyh3ck]

look how bad it looks, how can i hide certain entries in chart, so that it can only show based on its value

 

[Deepak]

loop here!

http://chandoo.org/wp/2009/11/12/topx-chart/

 

[skyh3ck]

Thanks i have figured out how to do that, thanks a lot man, you helped a lot and saved my time