{"id":64263,"date":"2007-08-10T01:22:00","date_gmt":"2007-08-10T01:22:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/08\/10\/how-can-i-save-emails-that-are-more-than-one-month-old\/"},"modified":"2007-08-10T01:22:00","modified_gmt":"2007-08-10T01:22:00","slug":"how-can-i-save-emails-that-are-more-than-one-month-old","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-save-emails-that-are-more-than-one-month-old\/","title":{"rendered":"How Can I Save Emails That Are More Than One Month Old?"},"content":{"rendered":"

\"Hey, \n

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

— RS<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

On Error Resume Next<\/p>\n
Const olFolderSentMail = 5\nConst olMSG = 3<\/p>\n
Set objOutlook = CreateObject(“Outlook.Application”)\nSet objNamespace = objOutlook.GetNamespace(“MAPI”)\nSet objFolder = objNamespace.GetDefaultFolder(olFolderSentMail)<\/p>\n
dtmTargetDate = Date – 30<\/p>\n
Set colItems = objFolder.Items\nSet colFilteredItems = colItems.Restrict(“[CreationTime] <‘” & dtmTargetDate & “‘”)<\/p>\n
For Each objMessage In colFilteredItems\n    strName = objMessage.Subject\n    strName = Replace(strName, “:”, “”)\n    strName = Replace(strName,”\/”,””)\n    strName = Replace(strName,”\\”,””)\n    strName = Replace(strName,”,”,””)\n    strName = Replace(strName, Chr(34),””)\n    strName = Replace(strName,Chr(39),””)\n    strName = Replace(strName,”?”,””)<\/p>\n
    strName = “C:\\Test\\” & strName & “.msg”\n    objMessage.SaveAs strName, olMSG  \nNext\n<\/PRE>\n
Let\u2019s see if we can figure out how this script works. (Incidentally, the script isn\u2019t anywhere near as complicated as it looks. And don\u2019t worry: we\u2019ll explain why<\/I> it looks so complicated in a minute or two.) <\/P>\n
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 \u2013 Outlook message format \u2013 we want to use when saving each item in the folder). After that we create an instance of the Outlook.Application<\/B> object, bind to the MAPI namespace, then use this line of code to connect to the Sent Items folder:<\/P>
Set objFolder = objNamespace.GetDefaultFolder(olFolderSentMail)\n<\/PRE>\n
Whew!<\/P>\n
Our end goal is to go through the Sent Items folder and save a copy of each message that\u2019s more than a month old. (Or, for our purposes, more than 30 days old.) How are we supposed to know<\/I> whether or not a message is more than a month old? Actually, that\u2019s pretty easy to determine: all we have to do is compare the message\u2019s CreationTime<\/B> 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.<\/P>\n
Of course, in order to do that we need to know exactly what was<\/I> the date 30 days prior to today. Fortunately, we can determine that<\/I> simply by subtracting 30 days from the current date, something we do here:<\/P>
dtmTargetDate = Date – 30\n<\/PRE>\n
Now we\u2019re 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<\/I> the items in the Sent Items folder:<\/P>
Set colItems = objFolder.Items\n<\/PRE>\n
From there we apply a Filter<\/B> to the collection, restricting the data to items where the CreationTime property is earlier (less than) the value of the variable dtmTargetDate:<\/P>
Set colFilteredItems = colItems.Restrict(“[CreationTime] <‘” & dtmTargetDate & “‘”)\n<\/PRE>\n
And no, we won\u2019t spend any time talking about Outlook filters in today\u2019s column; for more information, see our article Filtering Email Messages in Microsoft Outlook<\/B><\/A>. The main things to note for now: you need to put square brackets around the property name ([CreationTime]<\/B>) and, even though dtmTargetDate is a date-time value, you need to treat it like a string value; hence the crazy quotation marks:<\/P>
“[CreationTime] <‘” & dtmTargetDate & “‘”\n<\/PRE>\n
At this point we\u2019d be done (pretty much), except for one thing. We\u2019ve decided to use the value of the Subject<\/B> property as the file name for each saved file. That\u2019s fine, except that email Subject lines often include characters (such as colons) that can\u2019t be included in file names:<\/P>\n\n\n\n
\u2022<\/TD>\n\n
RE: The TechNet Script Center<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Because of that, we can\u2019t save a message until we remove these illegal characters. How are we going to do that? We\u2019re glad you asked that question.<\/P>\n
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\u2019s what these two lines of code are for:<\/P>
For Each objMessage In colFilteredItems\n    strName = objMessage.Subject\n<\/PRE>\n
We then use a series of Replace<\/B> commands to replace invalid file name characters with, for our purposes, nothing. For example, this command removes any colons from the variable strName:<\/P>
strName = Replace(strName, “:”, “”)\n<\/PRE>\n
If you take a closer look at the For Each loop, you\u2019ll see that we have similar commands to remove periods, commas, single quote marks (chr(39)<\/B>) and double quote marks (chr(34)<\/B>). Of course, there might be other characters you need to remove in order to create valid file names; if that\u2019s the case, well, we\u2019ve left it up to you to add in additional Replace commands as needed.<\/P>\n
After we\u2019ve cleaned up the value of the variable strName we then use this line of code to construct a file path for our new file:<\/P>
strName = “C:\\Test\\” & strName & “.msg”\n<\/PRE>\n
This gives us a file path similar to the following (note that the colon following the RE<\/I> has been removed):<\/P>\n\n\n\n
\u2022<\/TD>\n\n
C:\\Test\\RE The TechNet Script Center.msg<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
From there we can save the file by calling the SaveAs<\/B> method, passing the file path and the file type (the constant olMSG) as the two method parameters:<\/P>
objMessage.SaveAs strName, olMSG\n<\/PRE>\n
And then it\u2019s back to the top of the loop, where we repeat the process with the next item in the collection.<\/P>\n
That should get you going, RS. And now, all kidding aside, it\u2019s time for the Scripting Guy who writes this column to do what he really<\/I> does each and every day: go to lunch with the other Scripting Guys. Sure, it\u2019s a tough job. But someone has to do it.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Incidentally, that\u2019s not just a figure of speech: someone does<\/I> 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.<\/P>\n
Or both.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I save all the items in my Sent Items folder that are more than one month old?— RS Hey, RS. You know, we\u2019d like to answer this question; we really would. However, the Scripting Guy who writes this column is leaving on vacation tomorrow, which means that \u2013 being an […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[212,49,3,5],"class_list":["post-64263","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-outlook","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I save all the items in my Sent Items folder that are more than one month old?— RS Hey, RS. You know, we\u2019d like to answer this question; we really would. However, the Scripting Guy who writes this column is leaving on vacation tomorrow, which means that \u2013 being an […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64263"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64263\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}