Need to save CSV from specific email/subject to network folder

[vahju]

Hi Everyone. Just like to preface this by saying I am a complete noob at VBA.

Scenario

• Email comes in every day (between 7:30am - 8am EST) from same group email with same subject and with CSV with same file name
• Everyday the receiver of the email needs to manually save the CSV file to network share
• Someone else runs a power shell script to process the contacts of the CSV file

Goal

• Create VBA code or some other method to move file
• Check new emails for specific sender, specific subject line, and specific CSV file name
• If above criteria matches, do the following:
  
  • Copy the CSV file to a specific network share keeping the same file name
  • Move the email in Outlook from inbox to "Done" folder to prevent file from being processed again

Things I have done so far

• Checked into using Power Automate but the company I work for currently has it disabled
• Did some google searches and found some examples but the code found is really complicated and I am a noob

Any help would be appreciated and thanks in advance.

[gmayor]

The way to do this is to create a rule that identifies the incoming message and runs a script. The only problem is that your employer may have disabled scripts and that will need a registry change to address - https://www.slipstick.com/outlook/ru...-script-rules/. You would need to liaise with your company IT support to address that. In any case you may need to create a certificate to run Outlook code - https://www.gmayor.com/create_and_em...gital_cert.htm.
The basic code would be

Sub SaveAttachments(olItem As MailItem)
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Apr 2022
Dim olAttach As Attachment
Dim strFname As String
Dim i As Long
Const strSaveFldr As String = "D:\Path\Attachments\"    'the path where the csv is to be saved
    On Error Resume Next
    If olItem.Attachments.Count > 0 Then
        If InStr(1, olItem.Categories, "Processed") = 0 Then
            For i = 1 To olItem.Attachments.Count
                Set olAttach = olItem.Attachments(i)
                If olAttach.FileName Like "*.csv" Then
                    strFname = olAttach.FileName
                    olAttach.SaveAsFile strSaveFldr & strFname
                    olItem.Categories = "Processed"
                    Exit For
                End If
            Next i
        End If
        olItem.Save
    End If
lbl_Exit:
    Set olAttach = Nothing
    Set olItem = Nothing
    Exit Sub
End Sub

If for reasons indicated, you cannot run the script from a rule then while you could create an event to process the message as required, I find that this method is less reliable and in the absence of a rule I would instead create a rule to divert the messages as they arrive into a sub folder of inbox and then select the message and run the following macro to process the message, calling the code above.

Sub ProcessMessage()
Dim olMsg As MailItem
    On Error Resume Next
    Select Case Outlook.Application.ActiveWindow.Class
        Case olInspector
            Set olMsg = ActiveInspector.currentItem
        Case olExplorer
            Set olMsg = Application.ActiveExplorer.Selection.Item(1)
    End Select
    SaveAttachments olMsg
lbl_Exit:
    Exit Sub
End Sub

[vahju]

gmayor thanks for providing the code and advice.

I will definitely check with IT support on Monday.

Hopefully Monday or Tuesday I will be able to test out the code but I do have a few questions.

= = =

Just to confirm, setting up the Outlook rules based on FROM and SUBJECT line should catch the specific emails and trigger the VBA script (assuming its allowed)?

For the file name, it looks like I can create a variable to hold the true file name and use that in the LIKE statement. Is that right?
Goal is to be able to quickly change the file name if needed.
Based on your network path variable this seems doable.

When the rule is triggered and the move is complete, is the code adding a flag to the email called "Processed"?
Goal is to make sure the same email is not processed more than once.

= = =

Thanks again for taking the time to help out a vba beginner.

[vahju]

So I had to used both sets of code because running a script via a rule is disabled by the company I work for.

I was able to run the code on selected emails 1 at a time and everything works.

How would I go about tweaking the second part of the code so that it could loop through multiple messages that are selected. I realized this morning that we get the same email every day including Saturday and Sunday. So on Monday you could end up with 3 messages that need to be moved to the file share.

Sub ProcessMessage()
Dim olMsg As MailItem
    On Error Resume Next
   Select Case Outlook.Application.ActiveWindow.Class
      Case olInspector
         Set olMsg = ActiveInspector.currentItem
      Case olExplorer
         Set olMsg = Application.ActiveExplorer.Selection.Item(1)
   End Select
   SaveAttachments olMsg
lbl_Exit:
   Exit Sub
End Sub