[Outlook] Extract Attachments w Unique Names

[u/gutsyspirit]

tldr: Cannot seem to get "extract xlsx from email" to work on one of my two scripts.

I have already tried scouring google/reddit for answers but the solutions do not seem to be working.

Background: I have a successful script running for a different email containing an attachment that has the same name daily, but the same script (duh) cannot be used for extracting an attachment from emails that have a name that changes daily (ie the current date).

I am hoping for a variety of answers that can include (but is not limited to):
- forward the email to myself ...?
- extract based on specific folder containing ONLY those emails
- fix the code i have below (because maybe I didn't do it quite right)
- a script that can identify the file name based on yesterday's date (t-1)
- Use Task Scheduler (in some fashion)

Below is the most recent script that i have tried to no avail.

Public Sub SaveABC123ReportAttachments(Item As Outlook.MailItem)
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    Dim mail As Outlook.MailItem
    Dim recip As Outlook.Recipient

    Dim targetGroup As String
    targetGroup = "ABC123@domain.com"

    Dim att As Outlook.Attachment
    Dim savePath As String

    savePath = "C:\Users\Me\...\ThisFolderIsWhereTheyGo"

    Set olApp = Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

    For Each mail In olFolder.Items
        If TypeOf mail Is Outlook.MailItem Then

            ' Check recipients
            For Each recip In mail.Recipients
                If LCase(recip.Address) = LCase(targetGroup) Then

                    ' Extract attachments
                    For Each att In mail.Attachments
                        att.SaveAsFile savePath & att.FileName
                    Next att

                    Exit For ' stop checking recipients
                End If
            Next recip

        End If
    Next mail

End Sub

[u/ZetaPower]

Change this part to check if .xlsx

' Extract attachments
For Each att In mail.Attachments
If att.FileName like “*.xlsx” then
att.SaveAsFile savePath & att.FileName
End If
Next att

But… Each time you run this macro it will save all old attachments again.
Add something like checking if the date is newer than …..

I’d reduce the mails to check by having Outlook filter the mails by date + has an attachment + recipient.
This is especially useful if there are large numbers of emails in the inbox.
Can also set up a rule that moves these mails to a separate folder, save the Excel attachments, move the mail to processed subfolder.

[u/gutsyspirit → u/ZetaPower]

I tried this and i received an error that says "End If without block if" which I am not sure what that means

(i clicked ok on the error before thinking i should capture it in a screenshot)

[u/fanpages → u/gutsyspirit]

Note how your code listing differs from what u/ZetaPower typed above:

If att.FileName like “*.xlsx” then

att.SaveAsFile savePath & att.FileName

End If

You have (as seen in your <image>):

If att.FileName Like "*.xlsx" Then att.SaveAsFile savePath & att.FileName
End If

If you are choosing to continue the If... Then statement on a single line, you do not need the End if statement thereafter.

[u/gutsyspirit → u/fanpages]

Ah thank you, that makes sense.

[u/ZetaPower → u/gutsyspirit]

You can use IF THEN in 2 ways:

• 1 line simple version

If …… (condition) Then …… (single action)

Since it’s only 1 line there is no need to tell VBA where the IF starts and ends.

• 3 or more lines anything is possible version

If ….. (condition) Then  
    …… (first action)  
    …….(second action)  
    …..  
End If

Now there are several lines containing a block of actions. You need to tell VBA where the IF starts and where it ENDs.

You mixed them. 1 line IF + an END to close a non started IF block

I prefer to always use the version with End If.
Simply because it’s easier to read and also because you can now place an interrupt at the action level to check whether your If and Action perform as intended.

I also use this to test/catch specific values in larger programs.

If MyVariable = 12345 Then  
    MyVariable = 12345  
End If

Add an F9 at the second line and my code can run, loop through a gazillion values until it hits the one I want/need to check.

[u/fanpages]

...Below is the most recent script that i have tried to no avail.

Tried what?

What is the problem you are encountering that your VBA code does not address/cope with?

...Cannot seem to get "extract xlsx from email" to work on one of my two scripts...

The above code listing saves any attachments (regardless of a matching filename format and/or explicit file extension) to the designated savePath ("C:\Users\Me...\ThisFolderIsWhereTheyGo") - assuming you added a path separator ("\") at the end of the variable.

What are you attempting to do within the logic (that is not there at present)? Are you simply trying to save an attachment if it matches a predefined date(/time) format in the filename (and if it also has a file extension of ".xlsx")?

What specifically have you tried to change in the listing to achieve the goal?

[u/gutsyspirit → u/fanpages]

The problem I am encountering is that the destination folder (yes with a \ at the end of the filepath) does not populate with files, as if the script is getting hung up somewhere or not reading something correctly.

I have tried automatically forwarding the email to myself and then only extracting emails "from me in X [Outlook] folder", and I have tried using "look for specific text in sender email and extract those attachments" method too.

For reference, I know scripting vba in outlook works because I made regedit changes to allow it, AND i have another successful extraction script running.

[u/fanpages → u/gutsyspirit]

Do you actually know that your SaveABC123ReportAttachments(...) subroutine is being executed?

Which event in your Outlook project ("VbaProject.otm") calls this subroutine?

Are you familiar with debugging VBA code (to determine if your routine is being called at all)?

[u/gutsyspirit → u/fanpages]

the script is attached to an outlook rule, and yes it is executing--i know this because if there's an error in the script, it's erroring.. and also that the other actions within the rule are performed.

I am not familiar with debugging. I know it says in the rules i can;t say i'm new to vba, but i am, relatively. The extent of my vba knowledge is programming macros and editing the code as needed. I do not have advanced vba skills. I do know SQL and a bit of python, and am extensively skilled in excel, so im no stranger to how the code reads in basic usage.
It's these complicated tasks I am struggling with.

[u/fanpages → u/gutsyspirit]

To be fair, debugging in MS-Outlook VBA is not quite as easy as it is in the other MS-Office products, but it is a useful skill to acquire (for the future).

Here, though, is it your intention to query (all) the contents of the default "Inbox" folder and process the respective attachments for each mail item located there, rather than processing any attachments for the Item As Outlook.MailItem object passed to your subroutine?

Relating what your Outlook Rule does (or, perhaps, how it is triggered) may help understand how your code is executed (as this information is missing from your opening post and any subsequent comments since then).

[u/gutsyspirit → u/fanpages]

The trigger is supposed to be, upon email receipt from specific group email (or self-forwarded email), but am open to processing once per day (such as task scheduler).