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/rules/outlook-run-a-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_employ_a_digital_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