How Can I Save Emails That Are More Than One Month Old?

How Can I Save Emails That Are More Than One Month Old?

  • Comments 1
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I save all the items in my Sent Items folder that are more than one month old?

-- RS

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RS. You know, we’d like to answer this question; we really would. However, the Scripting Guy who writes this column is leaving on vacation tomorrow, which means that – being an important and valued employee of the Microsoft Corporation – he has an awful lot to do before he can take off. For example, he has to – well, never mind; they don’t let him do that anymore. But he does have to arrange for – no, scratch that; although the fire wasn’t really his fault, they don’t let him do that anymore, either. Of course, he still has to – you know what? Maybe, despite his busy schedule, he can answer this question for you after all:

On Error Resume Next

Const olFolderSentMail = 5
Const olMSG = 3

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderSentMail)

dtmTargetDate = Date - 30

Set colItems = objFolder.Items
Set colFilteredItems = colItems.Restrict("[CreationTime] <'" & dtmTargetDate & "'")

For Each objMessage In colFilteredItems
    strName = objMessage.Subject
    strName = Replace(strName, ":", "")
    strName = Replace(strName,"/","")
    strName = Replace(strName,"\","")
    strName = Replace(strName,",","")
    strName = Replace(strName, Chr(34),"")
    strName = Replace(strName,Chr(39),"")
    strName = Replace(strName,"?","")

    strName = "C:\Test\" & strName & ".msg"
    objMessage.SaveAs strName, olMSG  
Next

Let’s see if we can figure out how this script works. (Incidentally, the script isn’t anywhere near as complicated as it looks. And don’t worry: we’ll explain why it looks so complicated in a minute or two.)

As you can see, we start out by defining a pair of constants: olFolderSentMail (which represents the mail folder we want to access, the Sent Items folder), and olMSG (which represents the file format – Outlook message format – we want to use when saving each item in the folder). After that we create an instance of the Outlook.Application object, bind to the MAPI namespace, then use this line of code to connect to the Sent Items folder:

Set objFolder = objNamespace.GetDefaultFolder(olFolderSentMail)

Whew!

Our end goal is to go through the Sent Items folder and save a copy of each message that’s more than a month old. (Or, for our purposes, more than 30 days old.) How are we supposed to know whether or not a message is more than a month old? Actually, that’s pretty easy to determine: all we have to do is compare the message’s CreationTime property with the date from a month ago. If the CreationTime is less than that date, then the message must be more than one month old.

Of course, in order to do that we need to know exactly what was the date 30 days prior to today. Fortunately, we can determine that simply by subtracting 30 days from the current date, something we do here:

dtmTargetDate = Date - 30

Now we’re ready to retrieve a collection of all the sent mail items that are more than 30 days old. The first step in that process is to use this line of code to retrieve a collection of all the items in the Sent Items folder:

Set colItems = objFolder.Items

From there we apply a Filter to the collection, restricting the data to items where the CreationTime property is earlier (less than) the value of the variable dtmTargetDate:

Set colFilteredItems = colItems.Restrict("[CreationTime] <'" & dtmTargetDate & "'")

And no, we won’t spend any time talking about Outlook filters in today’s column; for more information, see our article Filtering Email Messages in Microsoft Outlook. The main things to note for now: you need to put square brackets around the property name ([CreationTime]) and, even though dtmTargetDate is a date-time value, you need to treat it like a string value; hence the crazy quotation marks:

"[CreationTime] <'" & dtmTargetDate & "'"

At this point we’d be done (pretty much), except for one thing. We’ve decided to use the value of the Subject property as the file name for each saved file. That’s fine, except that email Subject lines often include characters (such as colons) that can’t be included in file names:

RE: The TechNet Script Center

Because of that, we can’t save a message until we remove these illegal characters. How are we going to do that? We’re glad you asked that question.

To begin with, we set up a For Each loop to loop through all the items in our collection; inside that loop, we assign the value of the Subject property to a variable named strName. That’s what these two lines of code are for:

For Each objMessage In colFilteredItems
    strName = objMessage.Subject

We then use a series of Replace commands to replace invalid file name characters with, for our purposes, nothing. For example, this command removes any colons from the variable strName:

strName = Replace(strName, ":", "")

If you take a closer look at the For Each loop, you’ll see that we have similar commands to remove periods, commas, single quote marks (chr(39)) and double quote marks (chr(34)). Of course, there might be other characters you need to remove in order to create valid file names; if that’s the case, well, we’ve left it up to you to add in additional Replace commands as needed.

After we’ve cleaned up the value of the variable strName we then use this line of code to construct a file path for our new file:

strName = "C:\Test\" & strName & ".msg"

This gives us a file path similar to the following (note that the colon following the RE has been removed):

C:\Test\RE The TechNet Script Center.msg

From there we can save the file by calling the SaveAs method, passing the file path and the file type (the constant olMSG) as the two method parameters:

objMessage.SaveAs strName, olMSG

And then it’s back to the top of the loop, where we repeat the process with the next item in the collection.

That should get you going, RS. And now, all kidding aside, it’s time for the Scripting Guy who writes this column to do what he really does each and every day: go to lunch with the other Scripting Guys. Sure, it’s a tough job. But someone has to do it.

Note. Incidentally, that’s not just a figure of speech: someone does have to do it. Or at least someone has to go with Scripting Guy Dean Tsaltas, who inevitably forgets either his card key or the way to the cafeteria.

Or both.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment