William:
Of course there are many ways to do this. You aren't going to have a full
blown solution posted in a newsgroup; you could probably hire someone to
code all this for you; but if you want to do it yourself, here's some hints
To meet the max 95 per hour would require a bit of coding to loop through
those records available to send, send a max count of 95 and mark them as
sent (with a yes / no field), then wait an hour and do the process again.
(You can do scheduling using the Windows Task Scheduler combined with
Windows Scripting Host and VB script to launch Access and run a function
that does your e-mailing.)
To filter the report you want to send, using the SendMail method requires a
bit of code (since sendmail doesn't support filters), but its pretty easy to
do. Here's how:
1.) In a general module, dimension a variable that would hold the value of
the primary key for your report such as a customerID, etc.
Dim lngClientID as Long
'or Dim strClientID as String ' if its a string key
2.) Create a Sub procedure that sets this value that you can call from code:
Sub SetClientID(ClientID as Long)
lngClientID = ClientID
End Sub
3.) Create a Function that returns the client id as in:
Function GetClientID() as Long
GetClientID = lngClientID
End Function
4.) Then in your report's on Open Event add code like this:
Dim lngClientID as Long
lngClientID = GetClientID
If lngClientID >0 Then
Me.Filter = "[ClientID = " & lngClientID
Me.FilterOn = True
Else
Me.FilterOn = False
End if
5.) In your function used to send mail, do something like this:
a.) Open a recordset based on the report's underlying query where the
MailSent flag = False
be sure to use something like:
"Select Distinct ClientID from MyReportQuery"
b.) Loop through the recordset using a counter to output a max of 95 e-mails
c.) for each record in the recordset
1.) Call the SetClientID function with the ClientsID as the parameter
2.) Call the SendMail built in Access function
3.) After you've sent the mail call an update query to set that client
ID's mail sent flag to true
be sure to use the same parameters in that update query as were
used in your report so
that you are only selecting the current record for that client
4.) After the loop is done, call SetClientID one more time, setting it to
0
That's a start.
