VBA Macro to import data from multiple eml files into Excel sheet
Hi all,
I'm a newbie to this forum, so I am hoping that one of you helpful people out there will be able to help me with a problem that I am trying to resolve. I would like to use a macro that which will take certain data from one or more email (eml message) and output to the Excel spreadsheet An example file is attached if it is helpful.
What I am trying do is take the two lines of data reported under the column headings: Policy Number/Payee Name/Payee Bank Account No/Payment Amount/Dest Code and then output the data into the spreadsheet.
IDC Payments Extract Dummy Data
Company : ONOR Currency : NOK
Date: 01/08/2020 Disability claim
IDC Batch No: 000020301
The following Payments have been passed to the IDC system.
Policy Payee Name Payee Bank Payment Dest
Number Account No. Amount Code
012345678 A Client 01234567890 368.08 IDC
987654321 B Client 98765432109 840.85 IDC
2 Batch totalling 1,208.93
The eml file names will be different for each set of payments but will be dropped into the same folder location. The data to be reported and column headings will be identical across all messages.
Re: VBA Macro to import data from multiple eml files into Excel sheet
Hi Marc,
Thanks for your response. Sorry for not attaching the expected workbook result in the first message. Here is the sample file for information. If the results can be separated into the columns via the text to columns function, that would be great.
Re: VBA Macro to import data from multiple eml files into Excel sheet
Questions :
All files from source folder must be imported to Excel or only some of them should be manually selected ?
As Excel does not recognise .eml file type
- even if this kind of file can be dragged from the Files Explorer and dropped to Excel -
is it possible you can rename source .eml files as .mht ?
Re: VBA Macro to import data from multiple eml files into Excel sheet
Thanks Marc, please see below:
1. All files from source folder must be imported to Excel or only some of them should be manually selected ?
Answer: All files in the folder should be imported.
2. As Excel does not recognise .eml file type
- even if this kind of file can be dragged from the Files Explorer and dropped to Excel -
is it possible you can rename source .eml files as .mht ?
Answer: it is saved in my folders as .eml, but in the work application that it will be used with as .msg. Does this work for you? If it needs to go to .mht webpage to make it work then that's fine as well.
In fact .eml files do not need to be renamed under VBA, at least on my side …
According to your attachment as a VBA beginner starter to paste to the Sheet1 worksheet module :
PHP Code:
Sub Demo1() Dim P$, F$, Rg(1) As Range Me.UsedRange.Clear Application.ScreenUpdating = False P = ThisWorkbook.Path & "\" F = Dir(P & "*.eml") While F > "" With Workbooks.Open(P & F).ActiveSheet.UsedRange.Columns(1) Set Rg(0) = .Find("Number*Account No.*Amount*Code") If Not Rg(0) Is Nothing Then Set Rg(1) = .Find("*Batch totalling*", Rg(0)) If Not Rg(1) Is Nothing Then .Range(Rg(0)(2), Rg(1)(0)).Copy Cells(Rows.Count, 1).End(xlUp)(2) End If .Parent.Parent.Close End With F = Dir Wend If [A2>""] Then Me.UsedRange.Replace Chr(160), " ", xlPart Me.UsedRange.TextToColumns [A2], xlFixedWidth, _ FieldInfo:=Array([{0,2}], [{11,2}], [{37,2}], [{66, 1}], [{75, 2}]), DecimalSeparator:="." End If Application.ScreenUpdating = True Erase Rg End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Bookmarks