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

 

[scriverb]

I have done just this in the past. If not already available create a table with Company Identifier and Email Address. Modify the RecordSource for the report to a query with a criteria selection to the Company Identifier. This will allow for the report to only be run for that particular company. Use the email address and the company identifier to execute the DoCmd.SendObject command which will run and email the report to the target company.

Post back if you need more assistance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[rokerij]

First and foremost, thank you for your response.

Now, would we have to run a seperate report for each individual company? Using the identifier to tell the report the criteria from which to pull? If this is the case, do you know of a more timely way. Right now it's only 50 companies, and that is a lot of seperate reports to run, but some months it can triple that. Thanks for your thoughts and help!

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

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

 

[scriverb]

No that is the only way I have seen to do this. But, I don't see it being terribly longer to produce the individual reports as compared to one report. It will be just one click of your command button to print and then the VBA code will just loop through the process producing the specific smaller subset report and emailing directly to the recipient. I think this is the only way to perform this task.

Maybe another TT expert can chime in her if they have a better way. I am open to learning something new this morning also.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ExcessivePlay]

Bob,

This could potentially solve a weekly reporting issue I have with 463 offices. If you have time and could send a small sample database with the associated code I would greatly appreciate it.

Thanks in advance,

Richard Thornton

 

[scriverb]

I can give you some code right her in the thread. First of all we need a table with the OfficeID(Text) and the Email Address(Text) fields. I will call this tblOffice.

Copy and paste this code into a database module. We are creating a Global variable and a function to be used in selecting the correct office records for each report'

Global vOfficeID as String
Function OfficeID()
   OfficeID = vOfficeID
End Function

Now we need a select query to select just the target office for the report to be printed. Use the following as an example with your table and fields:

Select A.* FROM [red]tblOffice[/red] as A WHERE A.[[red]OfficeID[/red]] = [blue]OfficeID()[/blue];

Copy and paste this code into the OnClick event procedure of a command button on a form:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("tblOffice", dbOpenDynaset)
rs.MoveFirst
Do
   vOfficeID = rs("OfficeID")
   DoCmd.SendObject acSendReport, "[red]rptYourReportName[/red]", "SnapshotFormat(*.snp)", rs("[red]Email_Address[/red]"), "", "", "[blue]email subject text[/blue]", "[blue]email message body[/blue]", False
   rs.movenext
Loop Until rs.eof
rs.close
db.close

I have selected the Snapshot view to be used here because it maintains the formatting of the ACCESS reports. Each office receiving this report would have to the Microsoft Office snapshot viewer installed. See thread: thread703-816130 for more information on the download and installation of this viewer from Microsoft. This is an accepted well tested format that preserves the formatting of your reports when sent out.

Post back if you have any more questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

One more item. The SQL above should be saved and named. The named query then should be identified as the RecordSource for your Report.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ExcessivePlay]

Bob,

Thanks for the code and quick reply. I am heading out of town for the weekend but your sample code looks like it should do the trick and I will reply and let you know if I run into any problems first of the week.

Thanks again,

Richard

 

[ExcessivePlay]

Bob,

I truly appreciate the code and think I have followed your instructions to the T. As always I try to make sure I don't just copy someone else’s code but I try to learn from your efforts and work.

With this said I added a simple table named just as you named it, tblOffice, with a field called OfficeID that would hold the eMail addresses of my office managers, in this test case it just has one address for testing – mine.

I created a module with the code you provided by simply opening a new module, pasting the provided code saving and naming it OfficeID.

I then created a new simple query with your exact code since I created a table named tblOffice with the OfficeID field.

Finally I created a simple form with one command button and in the OnClick EventProcedure pasted your code between a “Private Sub eMailReport_Click()” header and “End Sub” footer.

With this completed when I activate the form button the edit screen highlights the first Dim db As DAO.Database line with a [Compile error: User-defined type not defined] message.

Did I miss something or just not understand your message?

Thanks again for your help,

Richard

 

[scriverb]

No you did not mess anything up. The code I provided is written using the Data Access Object library. You are probably using A2K version which as its default uses ADO. You must reference the DAO library for your database. This is done by opening a form in design view(any form). Click the Code button or select Code from the View menu. From the Tools menu select References. A popup window will appear. This is where you must select by checking the Microsoft DAO 3.6 Object Library. Click OK. Close the form. Now you can attempt testing the form and code again. The error should not popup again.

I would rename your database Module to basOfficeID. Try not to name objects the same exact name. The function within this module is called OfficeID so having two objects the same may in some instances confuse even ACCESS.

Post back with results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[danvlas]

If you have one report and one way to send it, here is an easy way:

1. Create a form to display the email addresses you want to send the report to. Make sure the form contains the CompanyID
2. Base your report on a parameter query. The parameter would take the CompanyID from the form:
Forms![YourFormName]![CompanyID]
3. The button should scroll through the recipients and send the report:

With Me.RecordsetClone
   While Not .EOF
      DoCmd.SendObject blah blah blah
      .MoveNext
      Me.Bookmark = .Bookmark
   Wend
End With

For more options, follow the link below my signature...




Daniel Vlas
Systems Consultant

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

 

[ExcessivePlay]

Bob,

That corrected the error! The only remaining issue I now have is Outlook first asked if I wanted to allow Access to automatically send messages, which I said yes to, and now Outlook asks me to verify that I want to send each message. I have looked in Outlook Options but can't find where I can turn this feature off.

Thanks!!!
-----

Daniel I may look at your access program if the distribution of reports continues to be an issue for me.

 

[danvlas]

Ahhhh...the damn security 'feature'... You're using Outlook XP...

Because of this, I decided to downgrade to Outlook 2K.
But, you may try looking for Outlook Redemption or PTFB on Google.

Or switch to another email program: Outlook Express, Netscape Messenger, Pegasus Mail and others...




Daniel Vlas
Systems Consultant

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

 

[scriverb]

danvlas has identified an issue with OfficeXP and Outlook. Please heed his advise and take a look at another thread that I was responding to when smedvid provided some links concerning this feature.

thread702-817914

These prompts have nothing to do with ACCESS and the SendObjects command.

Glad that your code is working now for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[kq]

This thread has been amazingly helpful. How would this look if I wanted to print to PDF (I have Acrobat blah blah blah) instead of emailing?

 

[scriverb]

kq: Please explain just what you mean in your posting. This thread had to do with emailing. Now you want to print to a PDF file?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[hartkea]

Bob,

Thank you for the valuable information. I was able to create the e-mails, however, the entire report is being sent to each individual in the tbloffice table.

The original record source for my report is a query that prompted the user to select a company to view that report. I removed the prompt but I am having difficulty incorporating the sql string
Select A.* FROM tblOffice as A WHERE A.[OfficeID] = OfficeID();
as the record source for my report.

Any suggestions would be appreciated.

Thanks,

Anne

 

[scriverb]

hartkea: I think another thread being started would be good as you have indicated a little different problem here. Please give all the details of your problem and we can start from there.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[hartkea]

Bob,

Thanks for your response. I was able to resolve my issue with the following query.

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

Anne

 

[terre]

Anne
Could you explain....maybe just code that for the example above......
and
where does the sql go?