Script for deleting messages after few days

This section contains scripts that hMailServer has contributed with. hMailServer 4 is needed to use these.
Post Reply
supermdi
New user
New user
Posts: 1
Joined: 2005-11-14 14:49

Script for deleting messages after few days

Post by supermdi » 2005-11-14 15:45

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 ?

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2005-12-01 04:20

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


philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2005-12-01 05:04

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?

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2005-12-01 16:56

Why not use collection Messages and check each Message.Date?
Then, if Message is overdue, use method Messages.DeleteByDBID.

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2005-12-01 19:18

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.

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2005-12-02 16:29

Neither do I.
You could add to the Features Request to have a class IMapFolders and an IMapFolderID property of Message.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2005-12-02 19:36

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.

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2005-12-02 20:19

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?

3zero2
New user
New user
Posts: 8
Joined: 2006-01-05 16:31

Post by 3zero2 » 2006-01-09 13:30

has this script been perfected? would be nice to have hmail delete old mail from the trash folder.

CraigHarris
Senior user
Senior user
Posts: 886
Joined: 2005-11-28 11:43

Post by CraigHarris » 2006-01-09 13:38

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?
Windows Server 2003 Std ::: hMailServer 4.3 B248 ::: 99% of email rejected as spam ;)

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-11 19:28

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.

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2006-01-11 20:01

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

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2006-01-11 20:08

'Restart Hmailserver to clear the cache
.. right, not pretty.
Can't you use the Clear() method:
http://www.hmailserver.com/documentatio ... =com_cache

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-11 21:45

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.

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-11 21:46

BTW, I don't have the cache enabled.

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2006-01-11 21:57

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?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2006-01-11 22:04

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"

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-11 22:14

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.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2006-01-12 00:49

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.

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-12 02:21

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

Gustav
Normal user
Normal user
Posts: 224
Joined: 2005-11-01 16:25
Location: CPH

Post by Gustav » 2006-01-12 10:28

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.

philip
New user
New user
Posts: 20
Joined: 2005-10-19 01:02

Post by philip » 2006-01-13 19:26

Wow! Thanks for the link Gustav, that is a lot of code to do what I would first think is a simple task.

edou
New user
New user
Posts: 1
Joined: 2006-06-26 13:14

the latest script

Post by edou » 2006-06-26 13:22

Hello philip,

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

Thank you.

Vandreaben
New user
New user
Posts: 5
Joined: 2006-07-27 10:53

Post by Vandreaben » 2006-07-27 10:57

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.

sdpaul
New user
New user
Posts: 3
Joined: 2006-06-21 01:56

Update

Post by sdpaul » 2006-07-28 18:35

Any idea when this script will be available.

Vandreaben
New user
New user
Posts: 5
Joined: 2006-07-27 10:53

Post by Vandreaben » 2006-08-21 20:47

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



nwkit
Normal user
Normal user
Posts: 133
Joined: 2004-04-19 03:57
Location: Canada

Post by nwkit » 2006-11-14 20:49

is there a way to modify the above script to delete messages from one account in one domain only instead of looping through everything?

irblue
New user
New user
Posts: 29
Joined: 2005-06-27 15:51

Post by irblue » 2007-01-04 17:09

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

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-01-04 19:04

You need to add
Call oApp.Authenticate("Administrator", "your-main-hmailserver-password")
after the line
Set oApp = CreateObject("hMailServer.Application")

coverboe
New user
New user
Posts: 16
Joined: 2006-06-21 22:05

Post by coverboe » 2007-01-04 23:33

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

bartley
New user
New user
Posts: 18
Joined: 2007-01-02 16:14

Post by bartley » 2007-02-02 18:37

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



bartley
New user
New user
Posts: 18
Joined: 2007-01-02 16:14

Post by bartley » 2007-02-03 23:01

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.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-02-04 11:11

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.

bartley
New user
New user
Posts: 18
Joined: 2007-01-02 16:14

Post by bartley » 2007-02-04 20:48

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

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-02-04 20:58

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")

bartley
New user
New user
Posts: 18
Joined: 2007-01-02 16:14

Post by bartley » 2007-02-04 21:21

That was the key! thanks a lot!!! it worked!

ikshadow
Normal user
Normal user
Posts: 75
Joined: 2006-07-19 11:17

Post by ikshadow » 2007-05-16 22:59

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:
hMailServer 5.4-B1950
MySQL 5.1.73
Domains: 32, Accounts: 1000+ , Messages: 1000.000+, Data size: 200GB+
Running since 18.7.2006 (hMailServer 4.2.2 - Build 199)

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-05-16 23:28

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.

Whatsek
New user
New user
Posts: 4
Joined: 2008-01-26 01:12

Can` get it to work

Post by Whatsek » 2008-01-26 01:39

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!

mikew
New user
New user
Posts: 13
Joined: 2007-05-06 11:43

Post by mikew » 2008-02-24 19:37

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

bigcrawdaddy
Normal user
Normal user
Posts: 53
Joined: 2008-02-19 14:41

Re: Script for deleting messages after few days

Post by bigcrawdaddy » 2008-04-06 18:19

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

redrummy
Senior user
Senior user
Posts: 370
Joined: 2007-06-21 06:52
Location: Alaska

Re: Script for deleting messages after few days

Post by redrummy » 2008-04-06 22:02

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.

Vertigo1
New user
New user
Posts: 1
Joined: 2008-04-29 22:33

Re:

Post by Vertigo1 » 2008-04-30 08:58

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?

mikew
New user
New user
Posts: 13
Joined: 2007-05-06 11:43

Re: Re:

Post by mikew » 2008-04-30 22:05

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).

luci
Normal user
Normal user
Posts: 70
Joined: 2008-02-29 14:29
Location: Romania

Re: Script for deleting messages after few days

Post by luci » 2008-11-13 12:24

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.
Radical Image Optimization Tool developer
Project Manager at CRIOSWEB

dracekvo
New user
New user
Posts: 9
Joined: 2008-11-11 20:13

Re: Script for deleting messages after few days

Post by dracekvo » 2009-01-07 23:14

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

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Script for deleting messages after few days

Post by martin » 2009-01-07 23:33

Did you install MyODBC?

dracekvo
New user
New user
Posts: 9
Joined: 2008-11-11 20:13

Re: Script for deleting messages after few days

Post by dracekvo » 2009-01-08 10:30

Ops :oops:
Thank you.

susheel
Normal user
Normal user
Posts: 51
Joined: 2008-07-04 21:09

Re: Script for deleting messages after few days

Post by susheel » 2009-04-24 17:44

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

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Script for deleting messages after few days

Post by ^DooM^ » 2009-04-24 18:08

There are more than 1 script in this thread.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

susheel
Normal user
Normal user
Posts: 51
Joined: 2008-07-04 21:09

Re: Script for deleting messages after few days

Post by susheel » 2009-04-24 18:10

hi Doom,
Can you please help me to chose the one which will work in version 4.4? I will really appreciate

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Script for deleting messages after few days

Post by ^DooM^ » 2009-04-24 18:22

I cannot. I no longer use V4 and have no way to test these scripts. You will just have to try them yourself.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

Tim Josten
New user
New user
Posts: 7
Joined: 2012-03-28 06:28

Re: Script for deleting messages after few days

Post by Tim Josten » 2012-05-10 05:58

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);
  }
?>

Post Reply