John, You are welcome, I am sure I will run into a problem and you will have my solution. Merrick
--- On Sun, 4/19/09, [email protected] <[email protected]> wrote: From: [email protected] <[email protected]> Subject: Re: [Access VBA Central] email Reports To: [email protected] Date: Sunday, April 19, 2009, 3:28 PM *** Sorry, this is corrected. Besides dyslexia, I suffer from amateur radio as well - hence the "QSL"! 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 SQL and format to email. ?? dim qd as DAO.QueryDef ?? set qd = CurrentDB.QueryDefs ("myquery" ) ?? qd.SQL = "... 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, "custo...@yahoo. com", , , "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 <mswatch...@yahoo. com> To: AccessVBACentral@ yahoogroups. com 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, 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, 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 <mswatchorn@ 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] [Non-text portions of this message have been removed]
