VBA save attachment by rule

[gazmoz17]

Hi,

Not sure if this a general VBA section or excel only....apologies in advance as this is an outlook question! ?

In an effort to become paperless, anything to do with purchase ledger, I want to make a script rule which sends to a sub folder, marks as certain category and saves attachment (90% of time PDF) in certain g:drive folder with a file naming convention.

Sub folders:

1. Direct
2. PO
3. Confirmation
4. Delivery Notes
5. Invoices
6. Statements

Categories:

1. PO
2. Confirmation
3. Delivery Note
4. Invoice
5. Statement

;don't require a "Direct Category"

I'm able to use normal outlook rules for move to folder and assign category.....there's about 30 indiv rules based on supplier name etc that achieve this.

Had a look at some vba scripts but would I have to have 30 diff scripts to tag onto my 30 current move to folder/assign cat scripts. To assign the separate file name pathways?

Or can I adapt a script that knows if a email hits "Invoice" it saves to Gmail\blah\blah\Invoices.

Email hits Delivery Notes it saves to Gmail\blah\blah\Delivery Notes

File naming Convention; not thought too much into yet

Date & Time received email and who supplier is. Joe Bloggs Ltd 17/09/21 11.34am

Also a way of marking if any duplicate files Joe Bloggs Ltd 17/09/21 11.34am (2)

Many Thanks
Gareth

 

[gazmoz17]

Hi, thanks for this ??

Followed the steps but have this error message:

This is where I have copied the code:

I've never inserted a Class Module (just googled diff between this & normal module & understand very loosely!)

I'll try and resolve myself now but new to VBA.

Thanks in advance

 

[gazmoz17]

Arghhh thought it was the renaming of the Class Module as you specified but wouldn't let me rename using F4 as I do a normal module or by right clicking. Managed to rename it now!

 

[GWteB]

Indeed, I could have been more explicit about renaming the Class1 module to FolderEvents. Is everything working now or are you still having problems?

 

[gazmoz17]

No you were clear, I read it that i had to change the class name but it just wouldn't let me so then doubted that you could and if that was the prob.

Works superb the run once macro....didn't realise how many attachments there was so took a while....but at least I have a historical listing ?...been trying to find something like this forever.

Appreciate I'm taking up a lot of your time so if your unable to help further to tweak I'll try & investigate myself:

• Is there a way to stop the saving of image/signature attach's maybe by file type or size?....seen this somewhere, I'll have a go myself.

Might need some help if ok for changing the save filename format as well to include date received and *who from....

...*who from, probably supplier from rather than multiple indiv's from same supplier. Is there a way to strip out the below in red after the "@" and before "." (this most logical way of extracting the supplier name I think?)

charlotte.fish@gandbnw.co.uk

So that the below becomes:

gandbnw-"Attach Filename"- 22.09.21-10.18

Or something similar.

Or even a way to distinguish between PO's. , Confirmations, Delivery notes, Invoices within the filename e.g PO-gandbnw-"Attach Filename"- 22.09.21-10.18

Reason being will need to manually (cant see any other way?) append the 4 indiv attach's files PO/Confirmation/Deliv Note/Invoice all with same PO number on and then save down the appended file.

So would be handy rather than clocking what subfolder I'm in if the filename itself had an identifier what type of doc it is deliv nore or inv etc.

So my gandb order acknowledgment/Confirmation is for raised PO 3891

So need to append that Confirmation pdf attach with the PO, Delivery Note & Invoice for PO 3891.

These appended files are getting printed stapled and manually ticked at the moment. So idea is pdf editor will tick prices & qtys off and insert signature of whos done it (as we do manually) & append extracted attachments from each subfolder.

Obvs in an ideal world it would read the indiv pdf attch and extract the PO number somehow and save into filename. I've researched using Total commander to search these G:drive subfolders theres a total commander pdf addon that will search for text within pdf......so then can search PO number and append the results.

Ultimately

Appended pdf saved in own g:drive folder prob just saved as PO number "3891"

Most importantly this file either dragged into or imported (but dont think possible) into our Sage 50 Accountancy software. As complete supporting evidence for everything to do with that Purchase Invoice we have posted to the ledger ref PO number to posting.

Sage Accountancy below:

Thanks

 

[gazmoz17]

Hi,

Don't think it's quite working but prob to do with my Outlook Rules moving to folders.

Not had many inbound emails today to see if its working live. The below email has just dropped in and my outlook rule has moved it to P.O.'s subfolder.

However, its not saving in my g:drive subfolder.

But does if drag this email back into main Inbox folder then back into P.O.'s subfolder then its shows in g:drive.

Not sure if does the same for other subfolders will test tomorrow.

I think most if not all subfolder rules are the same, per the below:

Thanks

 

[GWteB]

Did some more extensive testing and it turns out that it doesn't work for me either. The MS documentation about using Outlook's BeforeItemMove event handler notes:
"This event fires when the item is about to be moved to another folder (including the Deleted Items folder) or when the item is about to be permanently deleted. It does not fire during auto-archiving or synchronizing operations."
Although this is not the case in the strict sense (since the items are moved on the basis of a rule) we gain nothing further with this, the event handler is simply not called by Outlook at all.

An alternative seemed to be using Outlook's FolderChange event handler, but its documentation is a bit more explicit: "The FolderChange event fires when a folder in a Folders collection object is changed, either through user action or program code." Neither is the case, so this also comes to a dead end.

I'm afraid it takes a different strategy to get what you want (and it's quite a wish list...). I am thinking of a second set of Outlook folders to which you move the items after the attachments have been processed / saved on disk. I'm not sure whether this can all be done automatically when Outlook starts or whether it requires the push of a button, but I suspect the latter. Outlook's background processes, such as downloading mail, spam filtering and moving files according to rules, run asynchronously. This means that it is very likely that the automatic processing of attachments through VBA has already been completed while Outlook itself is not yet ready, which will result in only a part of the items / attachments being processed.

 

[gazmoz17]

Hi sorry i didn't reply yesterday was swamped at work. Today should have time to test that this happens to all my move item shortcuts. May just abandon the move to folder step of the rules. Then just filter main inbox each day on categ assigned and drag into the subfolder, the script should still work then I think, I'll try today.

Thanks