VBA: How To Auto-save Attachments in Outlook

So this week I wrote a script that will take an email from your Outlook and save all the attachments to a specified file. It was a learning process that took many unexpected twists and turns. There was a fair amount of cursing, cajoling, and a minor panic attack. Ultimately the bastard functions though, so I’m calling it a net gain.

It’s actually pretty easily digestible in 3 steps. First you get your objects all ready to go.

Then you’ll want to set up a user input box to get the name of the file that the attachments will go into. I’m planning to use this at work where multiple people save to the same server. In order to ensure they haven’t already saved the files, I added an If Else Statement that’ll determine whether the file already exists.

Last is the For Loop that deals with the actual attachments. This part has an If Else Statement inside the loop to push the files into different folders depending on the file type.

Presto. Done. Exciting, right?

On a side note:

I’ve decided every so often, I’m going to keep a record of all my failed attempts. It only seems fair to show exactly how many screw-up it takes for me to get one semi-functional script. This week’s total was only 12. Huzzah.

Attempt 1: Missing Object Error. You must create File System Object in order to use CreateFolder.
Attempt 2: Index Error. Selected Mail Items start counting at 1, not 0.
Attempt 3: Syntax error. / instead of \.
Attempt 4: File path does not exist.
Attempt 5: File path does not exist.
Attempt 6: Literally nothing happened when I ran it. Restarted computer.
Attempt 7: File path does not exist. Finally realized there’s a space at the end of the message subject (which I’ve been using in the file path name). Attempted to use Trim function.
Attempt 8: No error. But the script creates two folders of the same name and claims that one of them does not exist. And it won’t let me delete it. This guy on YouTube has video on how to get yourself out of that predicament if you’re curious.
Attempt 9: Added a user input box. Script works, but hitting “Cancel” makes weird things happen.
Attempt 10: Added the GoTo line… And it seems to be working.
Attempt 11: It totally wasn’t working. I accidentally used CreateFile instead of CreateFolder. Yes, those are two different things.
Attempt 12: It’s working for everything except spreadsheets. (Damn you, Excel.) Realized I mistakenly filtered for XLS instead of XLSX.

Yes, by the way, I found out that you can create files that can’t be deleted from your Desktop. You have to go into the dreaded command prompt to get rid of them.

Hoping that next week’s will go a little more smoothly.