Monday, November 08, 2004

Deleting old email with a Exoledb Script

This question pops up now and again from people who want to be able to scheduled a script that will delete mail in a folder that was received more then say 30 days ago. Typically this could be used on a postmaster mailbox (hopefully people are reading these mails before they get deleted) or on a mailbox that is doing auto replies when you want to keep the original emails for a period of time. Going about this is quite simple in Exoeldb all you really need to do is query on the desired folder for all the mail where the urn:schemas:httpmail:datereceived is older then 30 days. Once you have all these mails in a record set you can loop though the recordset and delete the email. The thing to remember when you are deleting mail this way is that the deleted mail goes into the dumpster of whatever folder you are deleting them from

mailboxurl = "file://./backofficestorage/yourdomain/MBX/mailbox/inbox"
set Rec = CreateObject("ADODB.Record")
set Rs = CreateObject("ADODB.Recordset")
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open mailboxurl, ,3
SSql = "SELECT ""DAV:href"", ""DAV:contentclass"" FROM scope('shallow traversal of """ & mailboxurl & """') "
SSql = SSql & " WHERE (""urn:schemas:httpmail:datereceived"" < CAST(""" & isodateit(now()-31) & """ as 'dateTime')) AND ""DAV:isfolder"" = false"
SSql = SSql & " AND ""DAV:contentclass"" = 'urn:content-classes:message'"
Rs.CursorLocation = 2 'adUseServer = 2, adUseClient = 3
rs.open SSql, rec.ActiveConnection, 3
while not rs.eof
rs.delete 1
rs.movenext
wend
rs.close


function isodateit(datetocon)
strDateTime = year(datetocon) & "-"
if (Month(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Month(datetocon) & "-"
if (Day(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4) & ":00Z"
isodateit = strDateTime
end function

If you want to go a step further with the script and crawl each folder in a mailbox to delete old content then you can combine this with one of the scripts from http://support.microsoft.com/default.aspx?scid=kb;en-us;320071 which gives you a method of enumerating every folder in a mailbox. Then you just call out an archive sub for each folder. This script gets a bit lengthy I’ve put an example of this as well as the other script from the article up here. As these scripts delete things great care should be always taken when using and testing scripts of this nature. Make sure you have adequately configured Deleted item retention rates and working backups, if you’re not sure what either of these things are don’t use the scripts (and seek help).

15 comments:

Anonymous said...

Glen,

I tried this script except with an HTTPS url instead of a file URL and it won't delete the emails. It finishes successfully and even counts 372 emails that it should delete, but it does nothing. Even seen this?

Glen said...

If you going to use the http namespace you should explicitly open the connection. eg use

mailboxurl = "https://server/exchange/mailbox/inbox"
set Rs = CreateObject("ADODB.Recordset")
set Conn = createobject("ADODB.Connection")
conn.provider = "ExOLEDB.Datasource"
conn.Open mailboxurl, "", "", -1
SSql = "SELECT ""DAV:href"", ""DAV:contentclass"" FROM scope('shallow traversal of """ & mailboxurl & """') "
SSql = SSql & " WHERE (""urn:schemas:httpmail:datereceived"" < CAST(""" & isodateit(now()-31) & """ as 'dateTime')) AND ""DAV:isfolder"" = false"
SSql = SSql & " AND ""DAV:contentclass"" = 'urn:content-classes:message'"
Rs.CursorLocation = 2 'adUseServer = 2, adUseClient = 3
rs.open SSql, conn, 3
while not rs.eof
rs.delete 1
rs.movenext
wend
rs.close

Anonymous said...

Glen thanks for your help...now I'm getting this:
C:\Documents and Settings\XXX\Desktop\deleteemails2.vbs(26, 1) ADODB.Connec
tion: Provider cannot be found. It may not be properly installed.

Which is at the line where I do this:

Conn.Open mailboxurl, username, password, -1

All those values are variables, hence the lack of double qoutes. Line 25 right above it is:
Conn.Provider = "ExOledb.Datasource"

Anonymous said...

Glen I changed the provider as was described suggested at another site to: MSDAIPP.DSO


But now I got this error:
C:\Documents and Settings\XXX\Desktop\deleteemails2.vbs(40, 1) ADODB.Record
set: Current Recordset does not support updating. This may be a limitation of th
e provider, or of the selected locktype.

Glen said...

This script is only supported locally on a Exchange server doing a recordset delete in MSDAIPP.DSO is not supported. Under the covers there using two completly different access methods. MSDAIPP.DSO uses webdav which is a little different from the ADO/Exoledb interface. What you could try which may work with MSDAIPP.DSO is to open a record for each mail you want to delete and use delete record eg

while not rs.eof
set rec1 = createobject("ADODB.Record")
rec1.open rs.fields("DAV:Href"),conn,3
rec1.deleterecord
set rec1 = nothing
rs.movenext
wend

If your running Exchange 2003 this probably wont work because of the changes made. Also if you trying to use this over SSL and your using self signed certifcates and there are any certificate popups (eg do you trust this certifcate) then it wont work either because the provider cant handle this.

You may be better just to write a straight WebDAV script to do this instead. The Exchange SDK has details of how to do this. The other method you can use remotly is to use CDO 1.2.

Anonymous said...

Glen,

It worked. Thank you very much.

Bhavesh said...

Hello
Is it possible to just delete mail attachments using EXOLEDB. emails are stored in public folders.

Thanks
Bhavesh
SEO Articles

Glen said...

Yes you can delete attachments pretty easly using the attachments well via CDOEX (which uses Exoledb) the Exchange SDK has more details on do this. Some simple CDOEX to do this in this script would be

set msgobj = createobject("CDO.Message")
msgobj.datasource.open rs.fields("dav.href").value,,3
msgobj.attachments.deleteall
msgobj.save
set msgobj = nothing

You might want to change the query also so it only looks at message with attachments eg Where ""urn:schemas:httpmail:hasattachment"" = True

Cheers
Glen

Anonymous said...

Hi Glen I'm back. I'm anonymous above which got it working with MSDAIPP.DSO. The script has been working but it giving me some unexpected problems. After it runs any rules that are setup on the server for the accounts that I delete emails get deleted as well. Why is this script undoing or deleting the rules for the accounts on the server?

Glen said...

This script will delete anything that has a content class of "'urn:content-classes:message'" instead of using where DAV:Content = you might be better of trying to use "http://schemas.microsoft.com/exchange/outlookmessageclass" = 'IPM.Note' the messageclass of rule should differ from that of messages so that should stop the rules getting deleted

Cody Anderson said...

Glen,

I have the exact issue you prescribed your script for - a postmaster inbox with a lot of unneeded stuff in it. I ran the "arcfolder.vbs" script on my Exchange server, only changing the mailboxurl to "file://./backofficestorage/globalmh.com/MBX/postmaster/inbox". The script runs without errors, but it doesn't seem to delete any messages from the Inbox. There's an abundance (100,000+) of messages from late 2005, and they should be getting selected for the record set and deleted....right? I can't help but think I'm missing a step - any thoughts?

Thanks,
Cody

Glen said...

I've found that if there is a very large number of objects you are trying to delete then using the recordset delete method is not the best way to go. You could try just deleting each record indivdually eg set delrec = createobject("ADODB.Record")
Rs.CursorLocation = 2 'adUseServer = 2, adUseClient = 3
rs.open SSql, rec.ActiveConnection, 3
while not rs.eof
delrec.open cstr(rs.fields("DAv:href")),,3
delrec.deleterecord
delrec.close
rs.movenext
wend
rs.close

Or do a rewirte using Mapi and CDO.

Cheers
Glen

Glyn said...

Great Script, works a treat many thanks. Would it be possible to amend it so that it deletes emails that are over 12 hours old rather than 1 day?

We have a spam mail box and we currently run the script ever 30 mins to clean out the folder but would like to clean mail over 12 hours old rather than 24 hours. The mail box gets very big and we would rather only store 12 hours worth of emails opposed to 24 hours.

Is this possible?

samuel said...

Hey There.
Great script, it's exactly what I was looking for!
First I need you to know that i've absolutly no idea about scripting, so here's my question:
I'd like to use the script for our Spam Public Folders and need to put in Credentials for the Administratoraccount. Is it possible to save credentials to do that?

Many Thanks,
Samuel Müller

Anonymous said...

Hi Glen,

I have a different problem. I want to extract all email attachments and save it to a local directory. I have a working code that's wirtten in vb.net 1.0 and i tried to convert it to C#.net 3.5 but an error occurs. Below is the converted codes. Error occurs during this line "poMessage.DataSource.OpenObject(poRecordMessage, "_Record");" and it says: "{System.Runtime.InteropServices.COMException (0x80040203): The object does not support this type of data source.

at CDO.IDataSource.OpenObject(Object Source, String InterfaceName)"

Can you help me with this? Thanks


private ADODB.Record poRecord;
private ADODB.Recordset poRecordset;
private ADODB.Record poRecordMessage;
private CDO.Message poMessage;
private ADODB.Connection poCon = new ADODB.Connection();

string url = psEMailboxURL + "Inbox";
poCon.Provider = "msdaipp.dso";
poCon.Open(url, psEMailAccountInformation.UserID, psEMailAccountInformation.Password, 0);
poRecord.Open(psEMailboxURL + "Inbox", poCon, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adFailIfNotExists, ADODB.RecordOpenOptionsEnum.adOpenRecordUnspecified, psEMailAccountInformation.UserID, psEMailAccountInformation.Password);
poRecordset.Open(psEMailboxQuery, poCon, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1);

poRecordMessage.Open(poRecordset, poCon, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adFailIfNotExists, ADODB.RecordOpenOptionsEnum.adOpenRecordUnspecified, psEMailAccountInformation.UserID, psEMailAccountInformation.Password);

poMessage.DataSource.OpenObject(poRecordMessage, "_Record");

int Count = poMessage.Attachments.Count;
if (piCounter <= Count)
{
CDO.IBodyPart att = poMessage.Attachments[piCounter];
att.SaveToFile(DestinationPath);
}
att = null;
}