VBA - is this possible?

Adendum

New Member
Joined
Feb 15, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
324
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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:

VBA Code:
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
 

Forum statistics

Threads
1,143,909
Messages
5,721,450
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top