Looping through records to sendobject

[tmort]

In the past I've set up scheduled reports to be sent out as emal attachments using the Windows task scheduler to fire up Access and used a command line switch to start a macro. The macro would run a query that the sendobject was based on and then do a sendobject.

I'd like to do something a little different. I'd like to send out an individual scheduled report to each of the companines in a table. The table would have company name, account number and the email address to send the report.

I'm sort of putting together some code and am not sure of the syntax etc.

I plan on doing the scheduling and the command line to fire up a macro which will first open the table of company names and email addresses then run the following code:

Function exportreport()

Dim stTo As String, ststartDate As String, stendDate As String, stcc As String, stconame As String
Dim stMessage As String
Dim dbs As DAO.Database
Dim rst As DAO.RecordSet

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("reportaccounts ")

While Not rst.EOF and Not rst.BOF

stTo = Me![tables]![reportaccounts]!

ststartDate = Date()-90
stendDate = Date()
stconame = [Tables]![reportaccounts]![coname]
DoCmd.SendObject acSendQuery, "export qry", acFormatXLS, [stTo], , , [stconame] & " " & "Quarterly Reports" & " " & ststartDate & " " & "to" & " " & stendDate, “This is your quarterly report”, False

rst.MoveNext
Wend


Am I on the right track? Also, I need to get the account number into the query. Do I use Me![tables]![reportaccounts]![accountnumber] ??

Thanks in advance

 

[jgc31]

On a brief look you do seem to be heading in the right direction. Search the site and you will find lots of threads on this subject.

Minor point you have

ststartDate = Date()-90
stendDate = Date()

but you have dimed both as strings not dates.

I find that it is easier when putting a SQL or similar string into an action to set it outside of the actual command line eg

strEmailText = " blah blah blah"

docmd.sendobject acSendReport,,acFormatSNP,"bod@somewhere.com",,,strEmailText,"blah",false

functionally the same just easier to read and therefore debug

 

[tmort]

I see that about the dates,

Thanks