#  Other Applications & Softwares  > Outlook Formatting & Functions >  > [SOLVED] Rule to file email in email folder, save the attachment to desk top and update spreadsheet

## JET2011

Summary of Outlook rule(s) needs:
1. Move incoming email to a specific Outlook folder based on subject contents
2. Save the email attachment in a similarly named folder on the desktop
3. Check off in a spreadsheet on the desktop the report was received for that particular month for that particular person which is identified by a 3 digit code in the subject line.
------------------
Details and background:
Every month about 30 different users submit to me via email a PDF attachment report and in the subject line there is an 3 digit ID code (220, 221, 223, etc.) and the Month; Jan, Feb, March etc.
Example subject line: 220 July

First would like the incoming email routed to the respective folder named 220, 221 etc. The folders are subsets of the Inbox.

Next would like the attachments saved in a folder on the desktop named ACE and subfolder named 220.

Finally would like a spreadsheet named ACESHEET that is in folder ACE, opened to record that 220 submitted their July report.  Ideally the word Complete would be populated in the 220 row under column July. Then save and close the file. The construction of ACESHEET is simple; Row 1 is the header row. The first cell A1 is ID header column and the months are listed across in cells B1 to M1. The data is in tab named 2018.
It would be great if this worked whether the user abbreviated the month or not; IE Aug or August and also if used upper or lower case.
-----------------------
Warning  Im not  a programmer but have successfully used some provided code in the past. The simpler the better is best for me. I think others could benefit by seeing this solution. Thanks for any assistance. JJ

----------


## dflak

Take a look at this. It does a lot of what you want and is "fill in the blanks." 

https://www.excelforum.com/tips-and-...subfolder.html

It does not do the sorting into Outlook folders that you want, but you can set up Outlook filter rules to do this.

The way I would use this program is to write a filter rule to collect all the emails in a folder under the Inbox.

Then set up this program to read those emails and extract the attachments to a single Excel folder and then, as an add-on macro, parcel those files out to their final destination folders and write the log you want in the process.

Give the program in the link a chance. It should do about 2/3 of what you want. If you are OK with this approach and want to take the logging step, post back.

----------


## JET2011

Thank you. I may be over my head on this, don't think I can figure out your method. Maybe I should break this down in pieces.  Is there a simple way to automatically save the email attachments on the desktop folder? That would put me in much better shape than where I'm at now, hard copy filing and tracking is very difficult.  I'm trying to convert over to the proposed email process, we are really doing all hard copy now, this was my vision for the future.

Regards,

j

----------


## dflak

Lets take this one step at a time, starting with Outlook.

Immediately under the Inbox folder, create a folder called ACE.

Open the ACE folder and create a sub-folder called Processed.

Create a filter rule in Outlook to move the incoming mail of interest to the ACE folder.

Download the spreadsheet. You can put it anywhere you want and even rename it.

Your mailbox is shown in Outlook. Most likely it is YourName@YourDomain. Enter this value into Cell B1.

Go to the green table in columns I:L.

Remove the old data. Highlight the rows in any column(s) in the table, right click and select Delete -> Table Rows.

Fill it in with the data you want:
Main Folder = ACE
Sub Folder = Processed
Where = full path to where you want to download the attachments. If this is a folder called ACE on the desktop then it is probably C:\users\YourID\Desktop\ACE.
Download Like = *.pdf

In cell B3, use the drop-down to select ACE. Rows 5:11 will fill in with the data the program needs automatically.

Actually, I already did some of this configuration for you in the attached file.

Thats it.

When you click on the Read Mail button, the program reads the mail in the ACE Outlook Folder, downloads all and only PDF attachments to the Excel Folder specified, and moves the mail to the processed folder.

This will get you started. It should work out of the box.

I know that this isnt quite what you want. I can modify the code to move the processed mail to folders called 220, 230  instead of Processed based on the subject. 

The next step in the process is to develop another macro to move the files and log them. I will probably give you a table where you tell the program that files with 220 in the title go to Excel folder ACE\220, etc. This table will be in this workbook. Also the log of who submitted what and when, will also be kept in this workbook.

Does this sound like it will work for you?

----------


## JET2011

Excellent - will work on it over the weekend.

----------


## JET2011

I have not been able to work on it. I did make 30 rules and folders (under Inbox) which files all emails with there respective ID codes in the subject to the respective folder.

----------


## JET2011

I followed all the steps and got an error show below. I needed to change all the group codes, they now all have a prefix ABC. So all the ID codes are ABC201,ABC202, etc. 
So one rule searching on ABC2 collects all emails of interest. I really would love to get all the attachments put in their respective folders. Thanks for everything. Off-line for now.

-J




```

```

----------


## dflak

What kind of error do you get on that line? Make sure that SubFolder is defined and exists.

Also, I noticed an odd occurrence: if you move a mail message from one folder to another manually, this code may fail. I think if you wait long enough for Outlook to "cycle through" to the next mail check, the condition will clear. Or you can close and reopen Outlook. The next time this happens to me, I'll try clicking Send/Receive in Outlook to see if it make the condition go away.

----------


## JET2011

This is the error:

Run-time error '2147221233 (8004010f)' The attempted operation failed. An object could not be found.

I did not move any emails manually. You suggested make sure subfolder exists.. I assume is this in case it is folder Processed a sub-folder of Ace a subfolder of Inbox. Make sure Sub-folder defined, don't know how to do or verify that. However didn't see the words ACE or Processed in the VBA code.

Thanks so much, look forward to getting this slick.

-j

----------


## JET2011

It works!! Very cool.  My path had a typo.

Next would be to get the attachments put in the specific folders (IE ABC201, ABC202, etc.) which are subsets of ACE both in Outlook and on the desktop.

Getting there!

Thanks again.  -J

----------


## dflak

Glad you are getting a handle on it.

Give me the list of file names and what folders you would like them to go to. I think I can come up with something that will take them from the general ACE folder and move them to their proper subfolders. If you still want to do the same with the mail messages, I might be able to do something there as well.

This would be a post-process. First download everything (only because I had that piece already built) then sort it out to where you want it.

----------


## JET2011

I am enjoying this, will save a lot of work. I think others will like and use the final concept. I attached a file with the structure. My original post I think explains the file pretty well, however the original post stated there are 30 users who submit emails of interest but there are 48. It also stated the ID codes are 201, 202, etc but they are actually ABC201, ABC202, etc.

Thanks for everything dflak!

-j

----------


## dflak

Send me a copy of your workbook as you have it configured now. I'll add the code directly to it.

I might even modify it to do the right thing the first time around instead of post-processing it.  :Smilie: 

IF you want to keep it private, leave out the top level Mailbox (the one with your name and domain).

----------


## JET2011

Please see attachments. Please note I've been inconsistent on the ID codes; they begin with ABC220 not ABC201. The are listed in the worksheet. Sorry about that.

Regards,

J

----------


## dflak

I don't want to keep bugging you but could you provide a short list of sample file names and subject names. I want to make sure I parse them correctly.

If I do it right, your mail will wind up in Inbox:ACE:220, Inbox:ACE:221, etc. Likewise the excel files will wind up in Desktop/ACE/220, Desktop/ACE/221, etc.

It is a matter of (a) *you* creating these folders in Outlook and Excel and (b) *me* putting loops in the right parts of the code.

----------


## JET2011

No problem to clarify as much as needed, so grateful for the assist. The folders in Outlook and on desktop all created, however note they all have a prefix ABC220, etc. Note messy sketch attached.

Regards,

-J

----------


## dflak

Looking at the PDF files, I assumed that the only numbers in the subject or file name will be the IDs (220, 221, etc.)

I really do not have a way to test this adequately. The code has been modified so that when the emails in the ACE folder are processed, they don't go to the Processed Outlook folder. Instead, they go to a folder under ACE with the ID number.

Likewise, the attachments go to Desktop/ACE/ID where ID = 220, 221, etc.

It is up to you to make sure that the email subfolders and Excel subfolders exist. The code will check to see if they do and if it can't find them, you'll get a warning message and nothing will happen.

I'll leave it to you to clean up the legacy directories and folders.

Good luck and let me know how it goes.

----------


## JET2011

Reviewing.  We can NOT assume only the ID code and month will be in the subject line. People too unpredictable.
We can only be sure the ID and month will be in the subject line somewhere.
Example subject line: ABC225 This is for August 2018
Example file attachment name:ABC225 Aug (with form 1A)
You never know if people will include the yr or spell the month. We are not filtering on the yr.

Please note the folders and ID's all have a prefix ABC: The ID would not be 220 for example, it would be ABC220.

----------


## dflak

Will the number always be immediately preceded by "ABC?" I can plan to find ABCxxx followed by a space and use that string to get the ID.

----------


## JET2011

Yes, will always have ABC.

----------


## dflak

OK, I will look for "ABC" and take the next three characters after it as the ID.

----------


## dflak

OK, here is the file with the above assumption. Note, if the program can't parse out the string, it will try to move things (mail message and attachments) to folder "000." So make such a folder in Outlook and Windows. These will be your error folders. After looking at them to see why they failed (maybe we need a better parsing algorithm) you can move them manually.

Cross your fingers: this is untested code.

----------


## JET2011

Great, can't wait to try. Make the 000 folders a subset of ACE?
Thanks
-J

----------


## dflak

Yes. Make an outlook folder under ACE called 000 and a windows folder Desktop/ACE/000.

----------


## JET2011

I got an attempted run-time error.

The attempted operation failed. AN object could not be found.

Set FldrIn = olNS.Folders(MailBox).Folders("Inbox").Folders(InFolder)

Also and bye the way. The original file I had working now gets and error. That error is Run-time error '440': Array index out of bounds. Only thing I can think I did to mess it up was strip out the personal information, using the excel feature to inspect document. :EEK!:

----------


## JET2011

UPDATE: I got the very first version to work again! I downloaded it again made the necessary changes and ran it. It pulled the email attachments off from Outlook folder Ace and copied to folder ACE on desk top. That tells me when I ran the document inspection feature it broke something.

Also was looking at the code on version four, second to last last line GetID(ABC334.PDF), is that a typo? There are no IDs starting with 3.

----------


## dflak

GetID(ABC334.PDF) is part of a macro I used to test the function I created to parse out the ID. I put in various strings to see what kind of results I would get. It's not part of the code that runs.

The document Inspection Feature? The only one I know is Files Inspect Document and that will tell you that there is potential personal information (hidden columns, etc.). 

Normally when I see the code fail on this line



```
Please Login or Register  to view this content.
```


With this error message, "The attempted operation failed. AN object could not be found." it means I moved something from one Outlook Folder to another manually. The fix that works for me is to close Outlook and reopen it.

----------


## JET2011

I opened and closed Outlook still getting the same run-time error noted yesterday 4PM. Some how I really believe it has to do with the 'inspect document' and removing personal info (and whatever else is removed.) What I did - Go to file, check issues,inspect doc, inspect then there is an option to remove personal info and whatever else is removed. The very first version worked fine, then I did this process and did not work. I downloaded the original version again without submitting it to this process and worked fine. The latest rev 04 of Read Mail used the version which I put through this process.

 :Confused:

----------


## dflak

The only thing I saw on inspect documents was that there are macros. I had some hidden columns, but they were cosmetic only and did not contain information.

What I will do is set up a test scenario that "shadows" what you have and send myself some "ACE" emails. This way I can test the program, walk through the code, and find out where it is breaking. I should be able to send you something back where all you would probably have to do is put in the Mailbox name.

It is going to take a bit to set this all up and I am expecting to get hit with a storm of reports and audits on Monday and Tuesday. So it might not happen until the middle of the week. There is usually a lull after the storm.

----------


## JET2011

Here are a few sample attachments you can use for testing. I guess there is a 2 attachment max.

Thanks for all your generous assistance.

j

----------


## dflak

I'll duplicate and rename as necessary - contents are not important.

----------


## JET2011

Couple more sample attachments you can use for testing.

----------


## dflak

I made a very slight modification. Your main folders in Outlook and Excel should be labeled "ACE." Then Make sub folders in both places called "ABC222", "ABC223" etc. including "ABC00." Note that I added the prefix ABC into the structure.

In the attached, change cell B1 to reflect the mailbox. Change cell K2 to the ACE desktop folder. It does not matter what's in cell J2; it's not used anymore.

I ran a test on my system and it worked with this setup.

----------


## JET2011

Works great - very slick. It files all the attachments in their respective folders.

Are we going to take a try at phase 2 and figure out how to track which ID's submitted for what month?


Thank you for all your assistance.

Regards,

J

----------


## dflak

I think I can add in a function that reads the ID (I already have that) finds the date of the file creation (I know how to do that) and write it to a log in a table on a sheet in the main program in such a way that it can be handled with a pivot table.

----------


## JET2011

That sounds interesting. Creation data not always reliable, some will be submitted late or early, I'm sure it would be quite accurate however. If we could use the first three letters of the month which is always in the subject and in the attachment that would be ideal. I'm not that great with pivots.  If there is anyway I can help be glad too. Regards, j.

----------


## JET2011

Was doing some brain storming on how to update the tracker file (ACESHEET.) Experimented a little with embedding the attachment into the corresponding cell. Can't figure how to do automatically. -j

----------


## dflak

I don't know why I get the "personal information" notice when I close the file. I have hidden columns, pivot tables, slicers and pivot charts in other documents and they don't give me a warning message.

Here it is with log sheet.

----------


## JET2011

Everything works! This is very impressive. 

The personal information message I'm sure is from the process I did on it which I'll never try that again. 
I think to fix make a new file then cut and paste code. I'll try it. 

Well I'm not handy at all with pivot tables... is there an easy way to change the field names (i.e. row labels) without causing problems?

Also if I add additional information to the the folder names will that cause issues ie Folder now called ABC222 changed to ABC222 Florida operations.

Thanks for sticking with me - great patience.

-j

----------


## dflak

You can change some of the headers in a pivot table with impunity. That is you can simply type over Count of Filename, Row Labels and Column Labels to give them more meaningful titles.

If you change the titles on the green table on the Log Sheet, the old name for the column header will be dropped from the pivot table. However, if you refresh the pivot table, the new name will appear and be available for use.

I don't have a good reference for pivot tables in that it was something I learned way back when and have been adding to my knowledge since then. See what Mr. Google can come up with for you or ask the question in the Water Cooler Forum.

There is a formula in the ID column that finds the ABC number no matter where it is. The same is true for the For Month Column. If you wish, you can add another column to the table for comments or anything else you want. These columns will be available for pivot table use although some may have little meaning. For example, you can't do a lot of calculations on comments, but you can at least count how many you have.

----------


## JET2011

Thanks. Would not be using the additional information added to the folder name.

----------


## JET2011

I change some desktop folder names ie ABC222 changed to ABC222 Florida operations. Then the program did not work. The additional info I'm adding will not be used for any manipulation only to eliminate the need to have a cross reference of the ID code to the actual area it represents. Be good to have.

Also I think I'm messing things up - I deleted things out of the log. I see there are formulas. How do you clear the log? (I re-downloaded)
Regards,

J

John

----------


## dflak

I did not understand the requirement. I thought you were changing file names, not folder names. If you want free-form file names you'll need to establish a manually-maintained table that maps mail subject keywords to folder names. A new ballgame.

----------


## JET2011

It would be not totally free form if that helps; first 6 characters always the ID code, space, 3 character month then free form.

Also not sure how to clear the log of the dummy test data? 

Should I make the pivot table auto update, any reason why not?


Thanks j

----------


## dflak

To clear the old log of data, highlight the rows you want to delete. Right click and choose Delete -> Table Rows.

----------


## JET2011

That worked - did that before but the pivot table didn't update but now I understand now need to refresh the data. Should we use some sort of auto update?

Meeting tomorrow, so great now able to clean up the data. Thanks!

-j

----------


## JET2011

The more I work with this the more I realize it is so awesome. On the pivot table the months don't sort across, the months are not in order. I have done a fair amount of customizing at this point, is there a simple way to fix this?

Most grateful - j

----------


## dflak

Right click on one of the months in the pivot table and select sort. Jan, Feb, Mar ... are recognized as a custom sort order.

----------


## JET2011

It worked!  You completed everything!  It was such a pleasure working together I wish I had another problem. :Smilie: 

Best regards,

j

----------


## dflak

I hang out here all the time, so if you do have a problem. Just post it and someone will pick it up.

----------

