Email Report to Multiple Recipients 5

[rokerij]

We have a report that we would like to send to multiple people. The report is grouped by company and has information listed below for each company including their email address. We only want to send the pages that are tied to that email address to the report.
So, we have a report that has 50 different companies, each page has a detail that belongs specifically to that company, including their email. Is there a way to send the report via email and have only the pages that are associated with the company to the email address, so that each company is not receiving the information on all the others, but just information contained in the report that is directly related to them? Does that make sense?

S.C. Albertin
Database Administrator/Newbie Tech
United Way

Help me to find my way, so that I may help others find theirs...

 

[hartkea]

The sql creates a table, outfile for report. This table only has one company(dealer in my case) and is used as the record source for my report.

Please find below the entire event procedure. Hope this helps.

Anne

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim eml As String
Dim vOfficeID As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblOffice", dbOpenDynaset)
rs.MoveFirst
Do
vOfficeID = rs("OfficeID")

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT [z shell to create outfile by dealer].* INTO [outfile for report] FROM [z shell to create outfile by dealer] WHERE ((([z shell to create outfile by dealer].DEALER)= '" & vOfficeID & "'))", -1
DoCmd.SendObject acSendReport, "email dealer inventory information", "MicrosoftExcelBiff8(*.xls)", rs("Email_Address"), "", "", "Inventory Report", "Please find attached your dealer inventory report", True

rs.MoveNext


Loop Until rs.EOF
rs.Close
db.Close

 

[danvlas]

You know, of course, that a temporary table does NOT release space when it is deleted unless you compact the database... Running a make-table query repeatedly would cause your database to grow extremely large even in one session, provided you 'overwrite' the table many times...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[nickforrest]

It appears that the above solutions attach the report to the email. How do I make a report snapshot appear in the email body? I'm pretty new to VB, so please type slowly!

 

[mbrant123]

I am so thankful I found this, I have been working on this all day and I am on the edge of going crazy.

I used the example code you posted to create and e-mail a report grouped by department. Your code got me much closer but the report that is sent is blank. Have I done something wrong in the query that limits the records?

Here is the code that I used for the button that e-mails the report:
Private Sub Command8_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MASTERLIST", dbOpenDynaset)
rs.MoveFirst
Do
Timekeeper() = rs("timekeeper")
DoCmd.OpenReport "pto - eib balances", acViewPreview
DoCmd.SendObject acSendReport, "pto - eib balances", "SnapshotFormat(*.snp)", rs("Email"), "", "", "email subject text", "email message body", False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub

Masterlist is a table that has all dept and the contact e-mail.

Here is the query that is to send the correct group to the correct dept. I have this as the recordset for the report:

SELECT masterlist.*, Balances.*
FROM masterlist INNER JOIN Balances ON masterlist.[Cost Center] = Balances.Dept
WHERE (((masterlist.Timekeeper)=timekeeper()));

Could you help me figure out why I am getting a blank report?

 

[mbrant123]

I figured out what I was doing wrong. Thanks for all the help it was exactly what I needed.

 

[kaatjev]

Thank you so much for this thread.
We are attempting to send e-mails to our clients detailing the orders for the previous day.
During testing, I ended up with an empty form for the clients that did not order anything on the day.
How do I prevent that?

 

[kaatjev]

Thank you to dhookom for providing me with the clue to resolve my issue.

I added

   recCount = DCount("[ACCESSION]", "Query1")
   If recCount > 0 Then
      DoCmd.SendObject acSendReport, "CCL", acFormatRTF, rs("E-MAIL ADDRESS"), "", "", "Daily Census Report", "testing daily census", False
   End If

and it now only sends a report to those clients that ordered something.