Solved: How to Throttle SMTP Traffic

[Opv]

I maintain an Excel mailing list of classmates for several graduating classes from high school, and I have VBA code to send mail directly from Excel. I am not running into a problem with my ISP server so far, as I think I'm allowed to send 500 emails per hour. However, I am rapidly approaching that limit, so I am interested in implementing a timer mechanism of sorts to throttle the outgoing email traffic so that once I send the maximum number of emails for one hour, my VBA script will pause the transmission for an hour before sending the remaining emails.

I'm curious as to which option would be the most efficient, i.e., establishing a timer to pause each outgoing email so that no more than 500 are sent within an hour, or some sort of counter that allows the first 500 emails to go through before then pausing an hour before sending the remaining items? Or, is there a better solution?

[mdmackillop]

I would go with OnTime to send a batch one hour after the previous lot finished until all were sent.

[Opv]

I would go with OnTime to send a batch one hour after the previous lot finished until all were sent.

Thanks for the suggestion. I'll give that a try.

[Opv]

I tried OnTime and I can see how that could be incorporated. I also tried Wait and I think I like that one better for my purposes, as I am wanting to incorporate it within my loop to send individual emails so that once x number of emails have been sent, the Wait would be triggered to pause the subroutine for an hour before proceeding. Unless there is some resource disadvantage to using Wait, I think I'd prefer to use it.

[mdmackillop]

As Wait is an application event, you cannot do anything else in that instance of Excel whilst the Wait is running.

[Opv]

As Wait is an application event, you cannot do anything else in that instance of Excel whilst the Wait is running.

Thanks! I see now. I didn't notice that when I first tested Application. Wait, as the wait period was so short it was insignificant. Further testing helps me see what you are talking about. I hate that, as Wait is so easy to work into my loop.

The only issue I had with OnTime is that it seems (unless I am missing something) to be more applicable to one Sub delaying the execution of a second Sub, as opposed to pausing an existing Subroutine. If I am correct in that regard, OnTime will necessitate:

1. capturing the item number of the last email sent in the loop;
2. Exiting the existing Sub;
3. Passing the variable to a second Sub containing the OnTime statement (if that is possible?);
4. Then passing that variable +1 back to the original Sub so that my loop picks up where it left off (again, if that is possible?).

Please let me know if I'm incorrect in my presumptions about OnTime. Also, would OnTime still be preferred to, say, a Windows timer?

[mdmackillop]

Something like
Option Explicit

Dim x As Long, y As Long

Sub DoStuff()
x = 0 'Counter
y = 5 'Batch size
Call Timing
End Sub

Sub Timing()
Dim i As Long
For i = (x * y) + 1 To (x * y) + y
'Send emails
Cells(i, 1) = "Data" & i
Next
x = x + 1
If x = 5 Then Exit Sub
Application.OnTime Now + TimeValue("00:00:05"), "Timing"
End Sub

[Opv]

Thanks. That works. It took me a bit to figure out how to adapt the example to my Excel email script, but I think I got it now. I've tested it with a small number of dummy rows of data, with a batch size of 2 (two at a time) and it is sending two emails at a time, waiting the appropriate period of time and then sending two additional emails, etc., until all emails have been sent. I think I'm ready to modify the batch size and OnTime configuration to accommodate my full datbase.

Thanks again for your help!

[Opv]

One follow-up question....If I wanted to substitute


Application.OnTime Now + TimeValue("00:00:00"), "sendMail"

with....


Dim myTimer as ????
myTimer = "00:00:05"

Application.OnTime Now + TimeValue(myTimer), "sendMail"

What would myTimer need to be Dimed as? String does not seem to be working.

[mdmackillop]

String would be correct.

Both these work for me
Sub Timing2()
Dim i As Long
Dim MyTimer As String
MyTimer = "00:00:05"
For i = (x * y) + 1 To (x * y) + y
'Send emails
Cells(i, 1) = "Data" & i
Next
x = x + 1
If x = 5 Then Exit Sub
Application.OnTime Now + TimeValue(MyTimer), "Timing2"
End Sub


Sub Timing()
Dim i As Long
Dim MyTimer As Date
MyTimer = TimeValue("00:00:05")
For i = (x * y) + 1 To (x * y) + y
'Send emails
Cells(i, 1) = "Data" & i
Next
x = x + 1
If x = 5 Then Exit Sub
Application.OnTime Now + MyTimer, "Timing"
End Sub

[Opv]

Yes, it seems to be working for me this morning. I don't know what I was doing last night that was causing the kink.

Thanks

[Opv]

Hey, there appears to be another alternative to Application.Wait other than Application.OnTime. This in effect pauses a subroutine similarly to Wait but without rendering Excel unusable while the timer is running.


Sub pauseIt(ByVal freeze As Boolean)
Dim myTimer As String
Dim myTime As Date

myTimer = "00:00:10"
myTime = Now

Do While Now < myTime + TimeValue(myTimer)
If Not freeze Then DoEvents
Loop

End Sub

Of course, this script is called from the script in which you want the pause to occur as follows:


call pauseIt(False)

For what it's worth....

[Opv]

Well, apparently I spoke too soon. That code fails to achieve what OnTime achieves when called from within certain read-write routines. It was initially tested with a simple loop which created a message using msgbox. When I attempted to incorporate it into my email loop, it functioned identically to Application.Wait. So, I'm back to the suggested loop using OnTime.

[mdmackillop]

What is Excel required for and is it neccessary? Another method would be to create a Script file which could call your email programme. This could be run from Windows scheduler.

[Opv]

What is Excel required for and is it neccessary? Another method would be to create a Script file which could call your email programme. This could be run from Windows scheduler.

Excel would not necessarily be required, per se, while the script is running. I am just accustomed to having Excel open all day long, as I do so many things in the application that are unrelated to the particular script in question. Consequently, I'm routinely updating other worksheets. However, I'm not sure I'd deem it critical for any of those other worksheets to have to be updated while the script in question is running.

[mdmackillop]

Check this out (http://www.petri.co.il/send_mail_from_script.htm) for possible scripting solutions. I'm sure there are many others.

[Opv]

Check this out (http://www.petri.co.il/send_mail_from_script.htm) for possible scripting solutions. I'm sure there are many others.

Thanks.

[Opv]

I think I'll stick with OnTime. :)