Emailing Report For One Record

[Cavern]

Hello,
I have a report designed right now that prints information about a company from my database and I need to design a few forms that have lists of companies, and where the user can select a company from the list and click a button to send the report for that company to someone via email.
Right now I can only get the email to send ALL the pages of the report, but I only want to send the information for the selected company. I understand that I could make a report's query based on the form in order to check the company's ID, but that would mean that I'd need to make a bunch of different reports for each form I would like to print from (because they would each need to refer to a specific field in a specific form). Is there some way to pass a WHERE statement, or something similar when Emailing a report? Then I could use the same report for all the different forms, which saves a lot of time and space.

Thanks
-Cavern

 

[Pat Hartman]

You can create a user defined function to solve the problem.
1. Define a global variable in a standard module.
Global gCustID As Integer

2. Define a public function in a standard module
Public Function ReturnCustID()
ReturnCustID = gCustID
End Function

3. To set the variable prior to emailing the report
GCustID = Me.CustID

4. To use the function to filter the report's query

Select ...
From ...
Where CustID = ReturnCustID();

 

[Cavern]

Works great... Thanks a bunch!

 

[gschimek]

Is there a way to do this, and also have it automatically send an email to each individual person, with just their section of the report?

 

[Pat Hartman]

You would need to write a code loop to do this. You will also run afoul of Outlook security if you do this. Unless "you" have control of your exchange server and can change security settings, you will get a message for each send warning you that someone is trying to send email on your behalf.

You can take a look at the mailer product from FMS at fmsinc.com. There may be other similar products for sale or you can investigate using MAPI. There are articles in the kb on this. I don't have any sample code.