VBA - is this possible?

[Adendum]

Hello Gurus,

I've just been asked if it is possible to create some VBA that sits in a master workbook (Excel 2016) that will collect or merge data from hundreds of identical files that get sent to an Outlook 2016 email account as attachments and then move the emails to a named folder in Outlook or a local folder after processing - is that even possible?

The data itself is reasonably straight forward, but will vary in volume from file to file. They want the data from row 3 to where ever the data ends (could be row 5 or row 55 or row 500) copied over to a master workbook.

So for clarity the steps within the process would probably be something like this:-
1. Open the master workbook, initiate a macro.
2. Email 1 and attached workbook 1 is accessed and the data from row 3 to the last row populated is copied into the master workbook.
3. Email 1 is marked as read and/or moved to an Outlook folder (processed or similar name) or extracted to a local folder using a similar name.
4. Email 2 and attached workbook 2 is accessed and so on.
999. The mailbox is empty and all processed files are no longer in the inbox, the master workbook has all the data from XXX workbooks and everyone is happy.

I haven't seen the data and have no idea of the email address or volumes but I am told "hundreds" of emails/attachments

If Outlook is a barrier it may be possible to automate moving the attachments in to a folder and then access all files from the same folder if that simplifies things...but honestly, I have no idea and would appreciate any insights on this.

Many thanks, Paul.

 

[JamesCanale]

I've done this kind of thing before in two steps.

I have an outlook macro that goes through a bunch of selected emails and copying all of the attachments to a folder. And an excel macro that opens all the files in that directory and pulling data from it to a master sheet.

Here is my outlook macro. Each of the files that I get emailed to me (once each day) have unique filenames.

Public Sub eBLoginReports()
    Dim Session As Outlook.NameSpace
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim currentItem As Object
    Dim currentmail As MailItem
    Dim a As Outlook.Attachments
    pth = "C:\XXXXXX\" ' change me
    pp = "Then following attachments were extracted:" & Chr(13)
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
    Dim r As MailItem

    For Each currentItem In Selection
        If currentItem.Class = olMail Then
        
            Set currentmail = currentItem
            c = currentmail.Attachments.Count
            If c > 0 Then
                Set a = currentmail.Attachments

                For t = c To 1 Step -1
                    f = a.Item(t).FileName
                    
                    a.Item(t).SaveAsFile (pth & f)
 
                Next t

            End If
        End If
    Next
End Sub

The report is a text file that is a list of names and the file name contains the date. Text files are easy. You can do similar things by opening the excel file and pulling the data - it'll be harder.

In excel:

Sub pulllogins()
Application.ScreenUpdating = False
c = 2
pth = "C:\XXXXX\"
fn = Dir(pth) 'the file name contains a date, which is used to put in column A of the excel output.
While fn <> ""
   Open pth & fn For Input As #1
    Input #1, junk ' The first value in the report is the useless header.  I'm only interested in the names.
    yr = Val(Mid(fn, 4, 4))
    mn = Val(Mid(fn, 8, 2))
    dy = Val(Mid(fn, 10, 2))
    d = DateSerial(yr, mn, dy) - 1
    
    While Not EOF(1)
        Input #1, inp
        Cells(c, 2) = inp
        Cells(c, 1) = d
        c = c + 1
    Wend
    Close #1
   fn = Dir()
Wend

End Sub