+ Reply to Thread
Results 1 to 9 of 9

VBA Macro to import data from multiple eml files into Excel sheet

  1. #1
    Registered User
    Join Date
    08-05-2020
    Location
    Hampshire,UK
    MS-Off Ver
    MS Office 365
    Posts
    5

    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.

    Any help you can provide is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: VBA Macro to import data from multiple eml files into Excel sheet


    Hi,

    according to your sample text file you forgot to attach the workbook expected result …

  3. #3
    Registered User
    Join Date
    08-05-2020
    Location
    Hampshire,UK
    MS-Off Ver
    MS Office 365
    Posts
    5

    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.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: VBA Macro to import data from multiple eml files into Excel sheet


    Questions :

    1. All files from source folder must be imported to Excel or only some of them should be manually selected ?

    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 ?

  5. #5
    Registered User
    Join Date
    08-05-2020
    Location
    Hampshire,UK
    MS-Off Ver
    MS Office 365
    Posts
    5

    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.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this demonstration !


    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 » !

  7. #7
    Registered User
    Join Date
    08-05-2020
    Location
    Hampshire,UK
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Try this demonstration !

    Thanks Marc for taking some time out to pull this together.

    I'm getting a Compile Error, "Invalid use of Me Keyword" coming from the 2nd line of code "Me.UsedRange.Clear"

    Can you help to resolve? Attached is the file I have been using.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: VBA Macro to import data from multiple eml files into Excel sheet


    'Cause you didn't follow the dark red underlined direction : code must be in the worksheet module rather than a standard module …

  9. #9
    Registered User
    Join Date
    08-05-2020
    Location
    Hampshire,UK
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: VBA Macro to import data from multiple eml files into Excel sheet

    Hi Marc,

    Thanks for explaining. That has worked now for me. Problem solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to import multiple delimited text files to one Excel sheet
    By Olai in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2018, 04:00 PM
  2. [SOLVED] Import named sheet from multiple excel files into a single sheet in defined excel file
    By LovesEurope in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-16-2016, 10:45 AM
  3. Import datas form multiple excel files to master sheet for consolidation
    By laxmananm in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-06-2014, 11:11 AM
  4. Macro to import multiple text files into one excel (2007) sheet
    By schthi3 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-08-2013, 11:35 AM
  5. Replies: 4
    Last Post: 05-17-2013, 05:29 AM
  6. Import tables from multiple Word files into a sheet of Excel
    By magdy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 03:53 AM
  7. [SOLVED] Import data from multiple excel files
    By Arlan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2005, 04:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1