Page 1 of 1

Script for deleting messages after few days

Posted: 2005-11-14 15:45
by supermdi
Hi,


i am looking for a script wich can delete messages. So what i want is to delete mails after a few days or an week.

Can anyone help ?

Posted: 2005-12-01 04:20
by philip
I'm in the process of setting something up that deletes mail in the Junk Mail folder after a set number of days. I have a rule in hMailServer that sends spam to the Junk E-mail folder if it is marked as spam by assp. Instructions for that are found here. Then I plan on scheduling the following vbscript routine to run once a day to clear out the old e-mail marked as spam.

DAYS_TO_KEEP - Anything older than this will be deleted
SCAN_FOLDER - The IMAP folder to scan for old mail
CONN_STRING - The connection string to the mysql database which uses myODBC found here.

If you are using MS Sql Server then the sql will need to be changed to use the Sql Server specific DATEADD() function and GETDATE() function.

This scans ALL accounts and ALL domains for the specified IMAP folder. I've done limited testing on this so far and it SEEMS to work (I only have one domain set up), be sure to test it thoroughly before using it in production. I am using hMailServer 4.2 beta, not sure which build.

Code: Select all

Const DAYS_TO_KEEP = "14"
Const SCAN_FOLDER = "Junk E-mail"
Const CONN_STRING = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3307;DATABASE=hmailserver;UID=root;PWD=your_password;"

Dim hMail
Dim hDomain
Dim hAccount
Dim sql
Dim conn
Dim rst
Dim DomainID
Dim AccountID
Dim MsgID
Dim TempID

DomainID = -1
AccountID = -1

Set hMail = CreateObject("hMailServer.Application")
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

conn.ConnectionString = CONN_STRING
conn.Open

sql = "select hm_messages.messageid, " _
    & "hm_messages.messageaccountid, hm_accounts.accountdomainid " _
    & "from hm_messages inner join " _
    & "hm_imapfolders on (hm_messages.messagefolderid=" _
    & "hm_imapfolders.folderid) inner join hm_accounts on " _
    & "(hm_messages.messageaccountid=hm_accounts.accountid) " _
    & "where hm_imapfolders.foldername=" _
    & "'" & SCAN_FOLDER & "' and hm_messages.messagecreatetime " _
    & "<= ADDDATE(CURDATE(), INTERVAL -" & DAYS_TO_KEEP & " DAY) " _
    & "ORDER BY hm_accounts.accountdomainid, hm_messages.messageaccountid"

rst.Open sql, conn

Do Until rst.EOF
    TempID = rst.Fields("accountdomainid").Value
    If TempID <> DomainID Then
        Set hDomain = hMail.Domains.ItemByDBID(TempID)
    End If

    TempID = rst.Fields("messageaccountid").Value
    If TempID <> AccountID Then
        Set hAccount = hDomain.Accounts.ItemByDBID(TempID)
    End If

    MsgID = rst.Fields("messageid").Value
    hAccount.Messages.DeleteByDBID MsgID

    rst.MoveNext
Loop

rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
Set hMailMessages = Nothing


Posted: 2005-12-01 05:04
by philip
I just ran my script and then checked the folder it deleted from and it had a bunch of messages from postmaster saying that the messages is missing on the server (One for each deleted item I presume). Once I restarted hMailServer they all went away.

I thought using the API to delete the messages would prevent this from happening (because of caching), but I guess not.

Anyone have any ideas?

Posted: 2005-12-01 16:56
by Gustav
Why not use collection Messages and check each Message.Date?
Then, if Message is overdue, use method Messages.DeleteByDBID.

Posted: 2005-12-01 19:18
by philip
I want to be able to remove the items from a specific folder. I did not see anything in the API to get the IMAP folder name of the message.

Posted: 2005-12-02 16:29
by Gustav
Neither do I.
You could add to the Features Request to have a class IMapFolders and an IMapFolderID property of Message.

Posted: 2005-12-02 19:36
by martin
I've already added it to 4.2. In 4.2, the Account object has an property named IMAPFolders which allows you to navigate through all folders / messages.

Posted: 2005-12-02 20:19
by philip
Thanks Martin, I will rewrite the script using the IMAPFolders collection and get rid of the direct db access.

I still have a problem with the message sticking around in the cache after using DeleteByDBID. It shows an e-mail from Postmaster like the following for each of the deleted messages:
Postmaster wrote:The mail server could not deliver the message to you since the file
D:\Mail\Data\weatherite.biz\jdoe\35\{35DCDEB1-C8F3-4984-94D8-121EB785F520}.eml does
not exist on the server.

The file may have been deleted by anti virus software running on the server.

hMailServer
Is there a way to prevent this from happening?

Posted: 2006-01-09 13:30
by 3zero2
has this script been perfected? would be nice to have hmail delete old mail from the trash folder.

Posted: 2006-01-09 13:38
by CraigHarris
philip wrote:Is there a way to prevent this from happening?
Is there a way to force a cache refresh?
Maybe you could just turn the cache off? - or does hMail cache some things regardless of cache settings?

Posted: 2006-01-11 19:28
by philip
I have not had time to finish up the script, I am using it as is except I added this to the end of the script:

Code: Select all

'Restart Hmailserver to clear the cache
hMail.Stop
hMail.Start
Not pretty, but it works. I'm still not sure why using the DeleteByDBID wouldn't update the cache.

Also, with regards to Gustav suggestion to use the Message.Date, the Message Date property is in the e-mail date format which takes a bit of parsing work to get into a VB date, so using the sql to get the messages to delete was still the easiest for me.

Posted: 2006-01-11 20:01
by Gustav
Message Date property is in the e-mail date format which takes a bit of parsing work to get into a VB date
You should be able to let DateValue() do this for you:
http://www.devguru.com/Technologies/vbs ... value.html

Posted: 2006-01-11 20:08
by Gustav
'Restart Hmailserver to clear the cache
.. right, not pretty.
Can't you use the Clear() method:
http://www.hmailserver.com/documentatio ... =com_cache

Posted: 2006-01-11 21:45
by philip
You should be able to let DateValue() do this for you:
http://www.devguru.com/Technologies/vbs ... value.html
DateValue() won't parse RFC822 dates which are used in e-mails, I just tried it.
I've tried the clear() method and the postmaster messages don't go away.

Posted: 2006-01-11 21:46
by philip
BTW, I don't have the cache enabled.

Posted: 2006-01-11 21:57
by Gustav
If so, I guess we need Martin to tell which "cache" to clear and how ...

As for the RFC822 date format, do you have an example on hand?

Posted: 2006-01-11 22:04
by martin
Please tell me if I've understood the problem correctly:

1) You use DeleteByDBID to delete a message.
2) The message isn't removed from the message list in memory, which causes hMailServer to issue a "The file is missing"

Posted: 2006-01-11 22:14
by philip
As for the RFC822 date format, do you have an example on hand?
There are various forms that this can take, but here is one from the header of an e-mail I received:
Wed, 11 Jan 2006 11:45:55 -0800
1) You use DeleteByDBID to delete a message.
Yes.
2) The message isn't removed from the message list in memory, which causes hMailServer to issue a "The file is missing"
Exactly.

Posted: 2006-01-12 00:49
by martin
I've modified the API so that DeleteByDBID deletes the message from the cache as well (stupid not to do it). I've done a bit of refactoring in this area of the code in this build, so I won't put up a new build on the download page immediately. If anyone of you are running only on a test computer, you can download the build here:

http://dev.hmailserver.com/hMailServer- ... ld-188.exe

Don't install this build if you're running in production with users that need to be able to access the server. This new build contains a couple of risky changes that I will test before putting it up on the download page.

Posted: 2006-01-12 02:21
by philip
Thanks martin, I'll test it when you put it on the download page.

Also, I have a feature request to be able to know when a message was first placed in a particular folder. I describe what I mean in the feature request section:
http://www.hmailserver.com/forum/viewtopic.php?t=3512

Posted: 2006-01-12 10:28
by Gustav
Your task seems to have been solved.
But - just for the records - should you need to convert an RFC822 date string to a VB date value, I located decent code here (browse to the bottom):
http://www.vbrad.com/pf.asp?p=source/sr ... utines.htm

It takes into account the variations in the format you may encounter including, of course, the time zone information.

Posted: 2006-01-13 19:26
by philip
Wow! Thanks for the link Gustav, that is a lot of code to do what I would first think is a simple task.

the latest script

Posted: 2006-06-26 13:22
by edou
Hello philip,

can you post the latest script for deleting old emails please?

Thank you.

Posted: 2006-07-27 10:57
by Vandreaben
Hello,

Very good discussion. But will you please post the latest script to the public. We are several users who need the script.

Thank you.

Update

Posted: 2006-07-28 18:35
by sdpaul
Any idea when this script will be available.

Posted: 2006-08-21 20:47
by Vandreaben
This small script works fine for me :-)

Feel free to try!

Best regards
Lars


Code: Select all

Const DAYS_TO_KEEP_SPAM = "6" 
Const SPAM_FOLDER = "Spam"  'INBOX.Spam

On Error Resume Next

Dim oApp 
Set oApp = CreateObject("hMailServer.Application") 

Dim oDomains
Set oDomains = oApp.Domains 

Dim iDomains  
For iDomains = 0 To oDomains.Count - 1 

	Dim oDomain 
	Set oDomain = oDomains.Item(iDomains) 
	
	Dim oAccounts 
	Set oAccounts = oDomain.Accounts 

	Dim iAccounts
	For iAccounts = 0 To oAccounts.Count - 1 

		Dim oAccount 
		Set oAccount = oAccounts.Item(iAccounts) 
				
		Dim oMessages
		Set oMessages  = oAccount.IMAPFolders.ItemByName("INBOX").SubFolders.ItemByName(SPAM_FOLDER).Messages
		
		Dim iMessages
		For iMessages = 0 To oMessages.Count - 1 

			Dim oMessage
			Set oMessage = oMessages.Item(iMessages)
			
			Dim vbDate
			vbDate = CDate(Mid(oMessage.Date, 6, 20))
			
			If (vbDate < CDate(Now - DAYS_TO_KEEP_SPAM)) Then 
				oMessages.DeleteByDBID(oMessage.ID)
			End If
		Next

	Next

Next



Posted: 2006-11-14 20:49
by nwkit
is there a way to modify the above script to delete messages from one account in one domain only instead of looping through everything?

Posted: 2007-01-04 17:09
by irblue
Hi,

The script is perfect in the previous hmailserver version but it doesn't work with the new 4.3 version, anyone know how to fix the compatibility problem ?

Thanks in advance
Blue

Posted: 2007-01-04 19:04
by martin
You need to add
Call oApp.Authenticate("Administrator", "your-main-hmailserver-password")
after the line
Set oApp = CreateObject("hMailServer.Application")

Posted: 2007-01-04 23:33
by coverboe
This worked for me to delete from 1 account only:

Code: Select all

Const DAYS_TO_KEEP = "7" 

On Error Resume Next 

Dim oApp 
Set oApp = CreateObject("hMailServer.Application") 
Call oApp.Authenticate("Administrator", "Your Password")

Dim oDomain 
Set oDomain = oApp.Domains.ItemByName("Your Domain Name") 

   Dim oAccounts 
   Set oAccounts = oDomain.Accounts 

   Dim iAccounts 
   Set iAccounts = oAccounts.ItemByAddress("Account to Delete From")

       
     Dim oMessages 
     Set oMessages  = iAccounts.IMAPFolders.ItemByName("INBOX").Messages 
  
      Dim iMessages 
      For iMessages = 0 To oMessages.Count - 1 

         Dim oMessage 
         Set oMessage = oMessages.Item(iMessages) 
          
         Dim vbDate 
         vbDate = CDate(Mid(oMessage.Date, 6, 20)) 
          
         If (vbDate < CDate(Now - DAYS_TO_KEEP)) Then 
            oMessages.DeleteByDBID(oMessage.ID) 
         End If 
 Next

Posted: 2007-02-02 18:37
by bartley
I tried to run this one and nothing seems to happen, I created a JunkCleanup.vbs script with the below code, and nothing happened.... am I doing something wrong? I scheduled it to run, then also tried to run it by double clicking on it. Thank YOU!



Vandreaben wrote:This small script works fine for me :-)

Feel free to try!

Best regards
Lars


Code: Select all

Const DAYS_TO_KEEP_SPAM = "6" 
Const SPAM_FOLDER = "Spam"  'INBOX.Spam

On Error Resume Next

Dim oApp 
Set oApp = CreateObject("hMailServer.Application") 

Dim oDomains
Set oDomains = oApp.Domains 

Dim iDomains  
For iDomains = 0 To oDomains.Count - 1 

	Dim oDomain 
	Set oDomain = oDomains.Item(iDomains) 
	
	Dim oAccounts 
	Set oAccounts = oDomain.Accounts 

	Dim iAccounts
	For iAccounts = 0 To oAccounts.Count - 1 

		Dim oAccount 
		Set oAccount = oAccounts.Item(iAccounts) 
				
		Dim oMessages
		Set oMessages  = oAccount.IMAPFolders.ItemByName("INBOX").SubFolders.ItemByName(SPAM_FOLDER).Messages
		
		Dim iMessages
		For iMessages = 0 To oMessages.Count - 1 

			Dim oMessage
			Set oMessage = oMessages.Item(iMessages)
			
			Dim vbDate
			vbDate = CDate(Mid(oMessage.Date, 6, 20))
			
			If (vbDate < CDate(Now - DAYS_TO_KEEP_SPAM)) Then 
				oMessages.DeleteByDBID(oMessage.ID)
			End If
		Next

	Next

Next



Posted: 2007-02-03 23:01
by bartley
How do I know this has run, is there a log file that reports it's deletions? Plus, I don't see that it's working.... my IMAP folder is Inbox.Junk which I've put into the code, do I need to use Inbox.Junk or just Junk? Thanks.

Posted: 2007-02-04 11:11
by martin
bartley, this script assumes that it's Inbox.Spam. If you are using Inbox.Junk, you need to update the SPAM_FOLDER setting in the top of the script.

Posted: 2007-02-04 20:48
by bartley
Yeah, I've done this actually, it for some reason doesn't want to clear out any messages from that folder. Is there a way to debug and see what's actually happening when it runs?

Here's a repost of exactly what I've tried running:

Const DAYS_TO_KEEP_SPAM = "6"
Const SPAM_FOLDER = "Junk" 'INBOX.Junk

On Error Resume Next

Dim oApp
Set oApp = CreateObject("hMailServer.Application")

Dim oDomains
Set oDomains = oApp.Domains

Dim iDomains
For iDomains = 0 To oDomains.Count - 1

Dim oDomain
Set oDomain = oDomains.Item(iDomains)

Dim oAccounts
Set oAccounts = oDomain.Accounts

Dim iAccounts
For iAccounts = 0 To oAccounts.Count - 1

Dim oAccount
Set oAccount = oAccounts.Item(iAccounts)

Dim oMessages
Set oMessages = oAccount.IMAPFolders.ItemByName("INBOX").SubFolders.ItemByName(SPAM_FOLDER).Messages

Dim iMessages
For iMessages = 0 To oMessages.Count - 1

Dim oMessage
Set oMessage = oMessages.Item(iMessages)

Dim vbDate
vbDate = CDate(Mid(oMessage.Date, 6, 20))

If (vbDate < CDate(Now - DAYS_TO_KEEP_SPAM)) Then
oMessages.DeleteByDBID(oMessage.ID)
End If
Next

Next

Next

Posted: 2007-02-04 20:58
by martin
What hMailServer version are you using?

If you're running 4.3 or later, you need to:
You need to add
Call oApp.Authenticate("Administrator", "your-main-hmailserver-password")
after the line
Set oApp = CreateObject("hMailServer.Application")

Posted: 2007-02-04 21:21
by bartley
That was the key! thanks a lot!!! it worked!

Posted: 2007-05-16 22:59
by ikshadow
coverboe wrote:This worked for me to delete from 1 account only:

Code: Select all

Const DAYS_TO_KEEP = "7" 

On Error Resume Next 

Dim oApp 
Set oApp = CreateObject("hMailServer.Application") 
Call oApp.Authenticate("Administrator", "Your Password")

Dim oDomain 
Set oDomain = oApp.Domains.ItemByName("Your Domain Name") 

   Dim oAccounts 
   Set oAccounts = oDomain.Accounts 

   Dim iAccounts 
   Set iAccounts = oAccounts.ItemByAddress("Account to Delete From")

       
     Dim oMessages 
     Set oMessages  = iAccounts.IMAPFolders.ItemByName("INBOX").Messages 
  
      Dim iMessages 
      For iMessages = 0 To oMessages.Count - 1 

         Dim oMessage 
         Set oMessage = oMessages.Item(iMessages) 
          
         Dim vbDate 
         vbDate = CDate(Mid(oMessage.Date, 6, 20)) 
          
         If (vbDate < CDate(Now - DAYS_TO_KEEP)) Then 
            oMessages.DeleteByDBID(oMessage.ID) 
         End If 
 Next
It does not work for me :x

I tried to run it manually (double click), after it I checked my spam inbox and old email were still there.

Any idea how to fix it. (already have 10K emails in spam@email. inbox folder )
:oops:

Posted: 2007-05-16 23:28
by martin
Are you accessing the account using IMAP?
Are you sure that the script had finished running when you checked the spam inbox? The script could take a few minutes to execute.

Can` get it to work

Posted: 2008-01-26 01:39
by Whatsek
I am using hmailserver for a year now, and it is great.

All my problems, and configuration questions did I resolve by searching in the formums and manual. Thanks to everybody that posted!

But this time, I can get this script working....

I have: hMailserver 4.4-B270 using the buildt in Mysql server. I launch the script manualy from the explorer, and through command line: cscript "path to script" I receive no errors, Username and password are correct, I also added the line in the script to authenticate.

The script runs, but nothing happens.

Here is my script, note the 0 days, is for testing, tried also more days, also my folder names are correct, even in cappital:

---------------------------------------------------------------------

Const DAYS_TO_KEEP_SPAM = "0"
Const SPAM_FOLDER = "SPAM" 'INBOX.SPAM

On Error Resume Next

Dim oApp
Set oApp = CreateObject("hMailServer.Application")
Call oApp.Authenticate("removed" ,"removed")

Dim oDomains
Set oDomains = oApp.Domains

Dim iDomains
For iDomains = 0 To oDomains.Count - 1

Dim oDomain
Set oDomain = oDomains.Item(iDomains)

Dim oAccounts
Set oAccounts = oDomain.Accounts

Dim iAccounts
For iAccounts = 0 To oAccounts.Count - 1

Dim oAccount
Set oAccount = oAccounts.Item(iAccounts)

Dim oMessages
Set oMessages = oAccount.IMAPFolders.ItemByName("INBOX").SubFolders.ItemByName(SPAM_FOLDER).Messages

Dim iMessages
For iMessages = 0 To oMessages.Count - 1

Dim oMessage
Set oMessage = oMessages.Item(iMessages)

Dim vbDate
vbDate = CDate(Mid(oMessage.Date, 6, 20))

If (vbDate < CDate(Now - DAYS_TO_KEEP_SPAM)) Then
oMessages.DeleteByDBID(oMessage.ID)
End If
Next

Next

Next

----------------------------------------------------------------------------------

Does anyone have a suggestion that I can trie?

Thanks!

Posted: 2008-02-24 19:37
by mikew
coverboe wrote:This worked for me to delete from 1 account only:

Code: Select all

Const DAYS_TO_KEEP = "7" 

On Error Resume Next 

Dim oApp 
Set oApp = CreateObject("hMailServer.Application") 
Call oApp.Authenticate("Administrator", "Your Password")

Dim oDomain 
Set oDomain = oApp.Domains.ItemByName("Your Domain Name") 

   Dim oAccounts 
   Set oAccounts = oDomain.Accounts 

   Dim iAccounts 
   Set iAccounts = oAccounts.ItemByAddress("Account to Delete From")

       
     Dim oMessages 
     Set oMessages  = iAccounts.IMAPFolders.ItemByName("INBOX").Messages 
  
      Dim iMessages 
      For iMessages = 0 To oMessages.Count - 1 

         Dim oMessage 
         Set oMessage = oMessages.Item(iMessages) 
          
         Dim vbDate 
         vbDate = CDate(Mid(oMessage.Date, 6, 20)) 
          
         If (vbDate < CDate(Now - DAYS_TO_KEEP)) Then 
            oMessages.DeleteByDBID(oMessage.ID) 
         End If 
 Next
Despite I am a hMail beginner, I am quite convinced that this script has a bug: the oMessage object it obtains from the oMessages collection uses the wrong index. The reason behind this is, that messages get deleted. Effectively, the original script will delete the wrong messages and/or not all messages.


Here is my proposal to fix this bug:

Code: Select all

Const DAYS_TO_KEEP_SPAM = "6"
Const SPAM_FOLDER = "Spam"  'INBOX.Spam

On Error Resume Next

Dim oApp
Set oApp = CreateObject("hMailServer.Application")
Call oApp.Authenticate("Administrator", "password") 

Dim oDomains
Set oDomains = oApp.Domains

Dim iDomains 
For iDomains = 0 To oDomains.Count - 1

   Dim oDomain
   Set oDomain = oDomains.Item(iDomains)
   
   Dim oAccounts
   Set oAccounts = oDomain.Accounts

   Dim iAccounts
   For iAccounts = 0 To oAccounts.Count - 1

      Dim oAccount
      Set oAccount = oAccounts.Item(iAccounts)
            
      Dim oMessages
      Set oMessages  = oAccount.IMAPFolders.ItemByName("INBOX").Messages

      'message index
      Dim i 
      i = 0
            
      'total number of messages
      Dim n 
      n = oMessages.Count 

      while ( i < n )
         Dim oMessage
         Set oMessage = oMessages.Item(i)         

         'set your own condition as wanted
         'for example, use the date conditon of the original post
         Dim delete 
         delete = true  
        
         if (delete) then
           oMessages.DeleteByDBID(oMessage.ID)         
           n = n-1
         else
           i = i+1
         end if 
      wend 

   Next  'accounts

Next  'domains

Re: Script for deleting messages after few days

Posted: 2008-04-06 18:19
by bigcrawdaddy
How could I edit this script to delete from a folder outside of the imapfolder I use a spam folder that is not a subfolder of INBOX. So is this possible? If so how?

Thanks

Re: Script for deleting messages after few days

Posted: 2008-04-06 22:02
by redrummy
How could I edit this script to delete from a folder outside of the imapfolder
What do you mean by this? If it's not in an IMAP folder than it's not on the server.
I use a spam folder that is not a subfolder of INBOX. So is this possible? If so how?
Sure, just change "INBOX" to "Spam" or whatever the name of the folder is.

Re:

Posted: 2008-04-30 08:58
by Vertigo1
mikew wrote:Despite I am a hMail beginner, I am quite convinced that this script has a bug: the oMessage object it obtains from the oMessages collection uses the wrong index. The reason behind this is, that messages get deleted. Effectively, the original script will delete the wrong messages and/or not all messages.
It all depends how the functions work. If the messages collection is a static snapshot then it may well not change even as the underlying messages are deleted.

Are you making assumptions here or have you tested this and know categorically that deleted messages are removed from an already-obtained messages collection?

Re: Re:

Posted: 2008-04-30 22:05
by mikew
Vertigo1 wrote:Are you making assumptions here or have you tested this and know categorically that deleted messages are removed from an already-obtained messages collection?
I only made a short test with a small collection of mails and noticed that the original script did not work. So I applied the changes posted above and got the expected result. However, I did not test it with large mail collections or with users being active in parallel (which could conflict with the script operation).

Re: Script for deleting messages after few days

Posted: 2008-11-13 12:24
by luci
mikew, as I can see at the first look, you don't make a correction, but you introduce a major bug.

you set
Set oMessages = oAccount.IMAPFolders.ItemByName("INBOX").Messages

instead of
Set oMessages = oAccount.IMAPFolders.ItemByName("INBOX").SubFolders.ItemByName(SPAM_FOLDER).Messages

and you never use SPAM_FOLDER to test against, neither DAYS_TO_KEEP_SPAM, though you mention as a commnent someone can use it

This will delete all messages from INBOX !!!
Newbies, don't run this script on a production enviroment, because it will delete all emails from INBOX.

Re: Script for deleting messages after few days

Posted: 2009-01-07 23:14
by dracekvo
hello,

i have problem on this line
conn.Open

windows reported:
Error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
error on line: 20
Code: 80001005

Anyone can help me?
Thx very much.

Here my full code

Code: Select all

Const DAYS_TO_KEEP = "2"
Const CONN_STRING = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=hmailserver;UID=mysqluser;PWD=mysqlpass;"

Dim hMail
Dim hDomain
Dim hAccount
Dim sql
Dim conn
Dim rst
Dim DomainID
Dim AccountID
Dim MsgID
Dim TempID

Set hMail = CreateObject("hMailServer.Application")
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

conn.ConnectionString = CONN_STRING
conn.Open

sql = "select hm_messages.messageid, " _
    & "hm_messages.messageaccountid, hm_accounts.accountdomainid " _    
    & "from hm_messages inner join " _
    & "hm_accounts on (hm_messages.messageaccountid=hm_accounts.accountid) " _
    & "where hm_accounts.accountdomainid = 4 and " _
    & "hm_messages.messagecreatetime <= ADDDATE(CURDATE(), INTERVAL -" & DAYS_TO_KEEP & " DAY)"
    
rst.Open sql, conn

Do Until rst.EOF
    TempID = rst.Fields("accountdomainid").Value
    If TempID <> DomainID Then
        Set hDomain = hMail.Domains.ItemByDBID(TempID)
    End If

    TempID = rst.Fields("messageaccountid").Value
    If TempID <> AccountID Then
        Set hAccount = hDomain.Accounts.ItemByDBID(TempID)
    End If

    MsgID = rst.Fields("messageid").Value
    hAccount.Messages.DeleteByDBID MsgID

    rst.MoveNext
Loop

rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
Set hMailMessages = Nothing

Re: Script for deleting messages after few days

Posted: 2009-01-07 23:33
by martin
Did you install MyODBC?

Re: Script for deleting messages after few days

Posted: 2009-01-08 10:30
by dracekvo
Ops :oops:
Thank you.

Re: Script for deleting messages after few days

Posted: 2009-04-24 17:44
by susheel
The script is not working for me... I am using hMailserver 4.4. Can you please please help? Here is what I am using.


Const DAYS_TO_KEEP_SPAM = "1"
Const SPAM_FOLDER = "INBOX" 'INBOX.Spam

On Error Resume Next

Dim oApp
Set oApp = CreateObject("hMailServer.Application")
Call oApp.Authenticate("Administrator", "password")

Dim oDomains
Set oDomains = oApp.Domains.ItemByName("ftbombay.com")

Dim iDomains
For iDomains = 0 To oDomains.Count - 1

Dim oDomain
Set oDomain = oDomains.Item(iDomains)

Dim oAccounts
Set oAccounts = oDomain.Accounts.ItemByAddress("joseph.leahy@ftbombay.com")

Dim iAccounts
For iAccounts = 0 To oAccounts.Count - 1

Dim oAccount
Set oAccount = oAccounts.Item(iAccounts)

Dim oMessages
Set oMessages = oAccount.IMAPFolders.ItemByName("INBOX").Messages

'message index
Dim i
i = 0

'total number of messages
Dim n
n = oMessages.Count

while ( i < n )
Dim oMessage
Set oMessage = oMessages.Item(i)

'set your own condition as wanted
'for example, use the date conditon of the original post
Dim delete
delete = true

if (delete) then
oMessages.DeleteByDBID(oMessage.ID)
n = n-1
else
i = i+1
end if
wend

Next 'accounts

Next 'domains



I need to delete emails older than 1 day from a particular domain for e.g. ftbombay.com and for the email id joseph.leahy@ftbombay.com. Please help

- Susheel

Re: Script for deleting messages after few days

Posted: 2009-04-24 18:08
by ^DooM^
There are more than 1 script in this thread.

Re: Script for deleting messages after few days

Posted: 2009-04-24 18:10
by susheel
hi Doom,
Can you please help me to chose the one which will work in version 4.4? I will really appreciate

Re: Script for deleting messages after few days

Posted: 2009-04-24 18:22
by ^DooM^
I cannot. I no longer use V4 and have no way to test these scripts. You will just have to try them yourself.

Re: Script for deleting messages after few days

Posted: 2012-05-10 05:58
by Tim Josten
Here is my variant of junk remover. It removes old messages from various folders in all accounts. You can set your own IMAP folders. Written in PHP.

Code: Select all

<?php
  $username = "Administrator";
  $password = "12345";
  $folders = array("Junk", "Trash");
  $domain = "domain.com";
  $days_to_keep = 30 * 86400;
  $now = time();

  try
  {
    $hmail = new COM("hMailServer.Application");
    $hmail->Authenticate($username, $password);
    $_domain = $hmail->Domains->ItemByName($domain);

    for($i = 0; $i < $_domain->Accounts->Count; $i++)
    {
      $account = $_domain->Accounts->Item($i);

      for($j = 0; $j < count($folders); $j++)
      {
        try
        {
          $folder = $account->IMAPFolders->ItemByName($folders[$j]);
        }
        catch(Exception $ee)
        {
          $folder = null;
        }

        if($folder)
        {
          $messages = $folder->Messages;

          for($k = 0; $k < $messages->Count; $k++)
          {
            $message = $messages->Item($k);
            $date = strtotime($message->InternalDate);

            if($date && $date < $now - $days_to_keep)
            {
              echo $account->Address.": ".$folder->Name.": ID=".$message->ID.", Date=".$message->InternalDate."\r\n";

              try
              {
                $messages->DeleteByDBID($message->ID);
                $k--;
              }
              catch(Exception $eee)
              {
              }
            }
          }
        }
      }
    }
  }
  catch(Exception $e)
  {
    echo $e;
    exit(1);
  }
?>