Easy (I hope) MSAccess question (Docmd.sendobject)

Factual Questions
1

I have an Access Database.
As part of this database, I have a form.
I navigate, in the form to a particular record.
I want to be able to click on a button on the form and automatically email a report using only the data in the current record.

I can currently:
Using the docmd.sendobject command, open an email with an attached report. Unfortunately, I can only do this based on the reports underlying query, so in order to bring up a particular record I have to have the report’s query ask me for my criteria (an ID number in this instance) and this works, but I don’t want the users to have to do this.
I can also:
Using the docmd.openObject command, I can open a report using criteria from the current record on the form. however, I cannot have it attached by an email in this manner, which is what I want to be able to do.

I’m using MS Access 2003 vba code. Am I out of luck or is there some simple thing I can do?

2

Well, there’s probably a more elegant way but this works:

First create a new table “ReportParms” with fields “ReportName”, “ParmText1”, “ParmLong1”

Insert a record: “rptEmployees”,"", 12345

My example uses an employee report, so the idea here is that the report shows employee 12345

In rptEmployees, the reports Record Source is a query based upon the Employees table. For the EmployeeID field use the criteria:
DLookUp(“ParmLong1”,“ReportParms”,“ReportName=‘rptEmployees’”)

Your report should show employee 12345 in preview mode.

Next, your code should be something like:



Public Sub SendEmail()
Dim lEmployeeID As Long, SQL As String

lEmployeeID = 12345
DoCmd.SetWarnings False
SQL = "UPDATE ReportParms SET ParmLong1=" & _
    Str(lEmployeeID) & _
    " WHERE ReportName='rptEmployees'"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
DoCmd.SendObject acSendReport, "rptEmployees"

End Sub
3

[Here](http://www.gurman.co.uk/MDB/Custom email reports.zip) is a link to an Access 97 demo I created quite a while back; it emails reports that are customised to the recipients. The concept of the demo is to inform attendees of their booked seminar attendance schedules at a conference, but I expect the principles can be adapted to suit what you’re doing.

4

It did work indeed. Thanks.

Also, Mangetout, your example is pretty cool. It doesn’t do what I was looking for, but I think I’ll modify mine to use your concept of dynamic email addresses. My list is pretty stable, but for those rare times when the emails need to be changed, it would be easier to change in a table instead of looking for the code.

thanks.

5

Cheers; in a database application, it’s generally a good idea to hard-code as little as possible of the specifics of how the applications treats different people/products/groups/accounts/etc - one of the applications I’m supporting at the moment has a bunch of code that almost literally goes:

If customer = 001 then
<Show the products by ISBN on the invoice>

If customer = 002 or customer = 003 then
<Show the products by EAN on the invoice>

If customer = 004 then
<Show the products by EAN on the invoice, but hide the prices>

etc…

When in fact it should just have a table (called ‘documentstyles’ or some such), containing fields containing flags/switches, like this:
CustID, Document, ShowEAN, ShowISBN, ShowPrice…
001,Invoice,False,True,True
002,Invoice,True, False, True
003,Invoice,True, False, True
004,Invoice,True, False, False

  • That way, a new customer’s requirements can be added into the application just by editing the table - no recompile required; also, should there arise a need to customise other documents, or customise the invoices in different ways, the table and application code can be modified to handle it with the minimum of fuss.