Merrick,
Of course! It was staring me in the eyes the whole time. And is so easy, I feel
stupid.
I don't know of any way to pass a query to a report at run-time, but I surely
can change the SQL statement of the saved query which the report is based upon
when the User clicks the 'Email' Toolbar Control:
Public Function bar_Email()
???
?? 'Change QSL and format to email.
?? dim qd as DAO.QueryDef
?? set qd = CurrentDB.QueryDefs("myquery")
?? qd.QSL = "... WHERE InvoiceNumber = "? ?& ListBoxValue?? 'Change SQL
statement to include selected invoice?number?as?WHERE?clause?so one record is
fetched.
?? DoCmd.SendObject acSendReport, "rpt_Invoice", acFormatHTML,
"[email protected]", , , "This Is An Invoice"
End Function
It works. The only problem I will have is the front-end will tend to bloat with
each change of the query. I will have to regularly compact.
Now I need to automate Outlook and?I am not sure HTML is the best format...
Thanks for stimulating the brain cells, Merrick!
-John
-----Original Message-----
From: Merrick Watchorn <[email protected]>
To: [email protected]
Sent: Sun, 19 Apr 2009 10:51 am
Subject: Re: [Access VBA Central] email Reports
John,
?
???? Create a user form that ask them to select or enter the required data
elements, allow for something and null values to create an SQL string that is
passed to the report at run-time or creates the SQL Query.
?
Merrick
--- On Sat, 4/18/09, [email protected] <[email protected]> wrote:
From: [email protected] <[email protected]>
Subject: Re: [Access VBA Central] email Reports
To: [email protected]
Date: Saturday, April 18, 2009, 7:07 PM
Hi again Merrick,
Again, maybe I am missing something.
I am not sure what you are telling me.
Currently, all invoices to be posted are listed in a List Box on a form. The
User selects the job and previews the invoice before it is posted. It is
completely automatic after the initial selection and click of a toolbar control.
If I change the query to have the field (whose parameter is now passed in the
'Where Condition' of the OpenReport method) require a value, it will prompt the
User for that value, and all automation is lost.
Confused,
-John
-----Original Message-----
From: Merrick Watchorn <mswatch...@yahoo. com>
To: AccessVBACentral@ yahoogroups. com
Sent: Sat, 18 Apr 2009 11:49 am
Subject: Re: [Access VBA Central] email Reports
John,
?
???? Create the query as a static one that requires the user to answer all of
the questions prior to clicking on the button.
?
Merrick
--- On Sat, 4/18/09, jmlt...@aol. com <jmlt...@aol. com> wrote:
From: jmlt...@aol. com <jmlt...@aol. com>
Subject: Re: [Access VBA Central] email Reports
To: AccessVBACentral@ yahoogroups. com
Date: Saturday, April 18, 2009, 11:07 AM
Thank you Merrick,
I have already explored the SendObject Method, however it lacks 2 important
features:
1) Printed invoices are generated at run-time using the 'Where Condition'
argument of the OpenReport Method (of the DoCmd Object) to specify a single
invoice.
Unless the invoice is printed, the report will not inherit the 'Where'
Condition' in the Filter Property. Therefore, using the SendObject Method will
send the last report printed, or will try to send all reports (several
thousand) if the Filter Property of the report is blank.
2) The process is not completely automated. The email client prompts the user
to send. Ideally, the User would elect to email (rather than print) the invoice
and either it would be sent without prompt, or perhaps stashed in a que for
batch emailing - without prompt from the mail client.
What am?I missing here?
Thanks,
-John
-----Original Message-----
From: Merrick Watchorn <mswatchorn@ yahoo. com>
To: AccessVBACentral@ yahoogroups. com
Sent: Fri, 17 Apr 2009 6:24 am
Subject: Re: [Access VBA Central] email Reports
Private Sub Command0_Click( )
On Error GoTo Command0_Click_ Err
??? DoCmd.SendObject acTable, "tblAction_Type" , "", "mswatchorn@ yahoo. com",
"", "", _
???????????? ????????? ???????? "What ever you want", "body of the message",
False, ""
Command0_Click_ Exit:
??? Exit Sub
Command0_Click_ Err:
??? MsgBox Error$
??? Resume Command0_Click_ Exit
End Sub
--- On Thu, 4/16/09, John McGovern <jmlt...@aol. com> wrote:
From: John McGovern <jmlt...@aol. com>
Subject: [Access VBA Central] email Reports
To: AccessVBACentral@ yahoogroups. com
Date: Thursday, April 16, 2009, 12:13 PM
Hi to all,
I am new to the Group, and sure this question has been posted before, but I did
a quick search and did not find the right answer.
I want to email customer invoices from Access 97 via Automation. I have tried
using the Send command of the File menu, but the report is not formatted
correctly and the process is not automated anyway.
- What is the best format / software to do this - pdf, HTML, other?
-And then, on the email side; can it too be automated? Suggestions?
Thanks to all,
-John
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
