Automatically move email from inbox to user created folder based upon age of email

[hkeiner]

Specifically, I want emails that are in my INBOX FOLDER to be automatically moved to another USER CREATED FOLDER when they become 90 days old. I would like these 'old' emails to be identified and moved automatically each time when I start up Outlook. I know I can use a Rule to immediately move all emails to a USER CREATED FOLDER when they are initially received, but I do not want to do that.

As background, my IT department recently instituted a procedure to remotely and automatically DELETE emails when they are over 90 days old if they are still in a user's INBOX FOLDER. If the user has moved the email to another user created folder, the email is not deleted until 2 more years have passed passed. While I accept that this a commendable effort by our IT staff to reduce the large quantity of unneeded emails clogging up their servers, it does mean that I have to be very diligent to manually move all my important emails to other folders before the 90 day clock expires. As a safety net, I would like to have Outlook automatically do this before the 90 day window expires so that I don't inadvertently lose any important emails. (As a side note, emails are not actually DELETED from the company's servers after 90 days. The are archived to long term storage for legal purposes but are no longer accessible to users via Outlook, which is the same as deleted to the user for most intents and purposes.)

While I have some experience with VBA code in Word and Access, I have never done anything in Outlook so not sure if the above is possible. I searched the Outlook forum for a similar/related discussion involving VBA methods based upon age of an email but did not see any.

[skatonni]

With a folder under the inbox named AgedMail.

Code goes in ThisOutlookSession

Option Explicit

Private Sub Application_Startup()

' Move Aged Mail

Dim oSourceFldr As Folder
Dim oTargetFldr As Folder
Dim oItems As items
Dim i As Long
Dim s As String
Dim d As Date

d = Date
s = "[Received] < """ & d - 80 & """"

Set oSourceFldr = Session.GetDefaultFolder(olFolderInbox)
Set oTargetFldr = Session.GetDefaultFolder(olFolderInbox).Folders("AgedMail")

Set oItems = oSourceFldr.items.Restrict(s)

For i = oItems.count To 1 Step -1
    DoEvents ' keep working in Outlook through the slow move process
    'Debug.Print "Moving " & i
    oItems(i).Move oTargetFldr
Next

ExitRoutine:
    Set oSourceFldr = Nothing
    Set oTargetFldr = Nothing
    Set oItems = Nothing
    'Debug.Print "Done."

End Sub

[hkeiner]

skatonni,

Thanks so much for the code. I think I'm close.

Your code runs OK when the AgedMail folder is located under the INBOX (as you explained in your reply), but in my situation the AgedMail folder has to be located outside of the INBOX. This is because IT's 90-day purge of emails includes a purge of all emails in folders located under the INBOX too. In order for an old email to be excluded from the 90-day purge, a PERSONAL FOLDER has to be created outside the INBOX. I guess I should have mentioned this in my initial post.

Anyway, when I moved the AgedMail folder outside the INBOX, I get a debug error (see below) when running the code,which seems to indicate that the path to the AgedMail folder is not specified correctly. Also, when I manually move the AgedMail folder from inside the INBOX to outside the INBOX, Outlook asks the following question "Are you sure you want to move the folder inside the folder hkeiner@bart.gov?" I think this message is also a good clue as to what the problem line a code should say.

I tried to figure out how to change the problem line of code to specify the correct folder path but as a novice to Outlook's VBA object model I couldn't readily figure it out. I'm not sure if it is more appropriate to research and study this on my own, start a new thread asking for help on this somewhat separate but related VBA problem, or ask you first. I'm choosing the easiest way first. :-)

LINE HIGHLIGHTED ON DEBUG SCREEN:

Set oTargetFldr = Session.GetDefaultFolder(olFolderInbox).Folders("AgedMail")

[skatonni]

To specify a folder at the same level as the inbox. Go to the folder above and back down to the target folder.

Set oTargetFldr = Session.GetDefaultFolder(olFolderInbox).Parent.Folders("AgedMail")

A less flexible method.

Set oTargetFldr = Session.Folders("your email address").Folders("AgedMail")

[hkeiner]

Thanks. I changed that problematic line of code (to that shown below) and the macro moves the old emails to my desired PERSONAL FOLDER now without any errors. I haven't gotten the code to fire up automatically when Outlook starts yet, but I'll take a shot at troubleshooting that myself. I already see that there are multiple threads addressing this issue and various possible causes/solutions so no need to repeat them here.

Thanks again for your help.

Set oTargetFldr = Session.Folders("hkeiner@bart.gov").Folders("AgedMail")

[hkeiner]

skatonni,

A little hiccup turned up in the way the below line of code "restricts" the items to be moved from a oSourceFldr to the oTargetFldr. I see that "normal" emails listed by default in a folder are moved OK based upon their [RECEIVED] date. However, older emails (which are apparently stored on the Microsoft Exchange server instead of the normal location for emails as they get older) are not moved to the oTargetFldr even though their [RECEIVED] dates meet the criteria for being moved.

Here is a bit more info ....Older emails in a folder are stored on the Microsoft Exchange server based upon the age of the email instead of their "normal" location. These older emails are accessible just like 'normal' emails in a folder by clicking on a link appearing just below the last displayed 'normal' email listed in the folder. Specifically, one line below the last 'normal' email says "There are more items in this folder on the Server" and the second line says "Click here to view more on Microsoft Exchange". After clicking on the latter link, the older emails are listed with the 'normal' emails and seem indistinguishable when looking at them in the folder. However, upon closing Outlook and opening Outlook again, these older emails are again not listed in the folder unless the user clicks again on the special link at the bottom of the folder.

This scenario could be quite unique to my organization and a solution may not be apparent, but I thought I would ask anyway since you seem to know a whole lot about this Outlook VBA stuff.

Set oItems = oSourceFldr.items.Restrict(s)

[hkeiner]

A little hiccup turned up in the way the below line of code "restricts" the items to be moved from a oSourceFldr to the oTargetFldr. I see that "normal" emails listed by default in a folder are moved OK based upon their [RECEIVED] date. However, older emails (which are apparently stored on the Microsoft Exchange server instead of the normal location for emails as they get older) are not moved to the oTargetFldr even though their [RECEIVED] dates meet the criteria for being moved.

Well, I solved this problem. My solution did not involve changing the VBA code but instead I changed the Outlook setting at "File/Account Settings/Email/Change/Offline Settings/Use Cached Exchange Mode/Mail to keep offline" to the "ALL" setting. Now all emails that meet the date criteria in the VBA code are moved. Apparently the "move" command in the VBA code ONLY works on offline emails.

Thanks again to Skatonni for providing the original VBA code I used, as I would never have figured this all out on my own.