I have rerun the code from post #39 but while the report is emailed to me there is no sign of any folder! Had you intended that I should have modified the code in any way?
By the way how can I delete my email address from the posts?
Just spotted how to delete email addresses!!
The only thing would have been adding your email to the code I provided. For the second question, you can edit your post using the tools in the green area at the bottom of your post.
I will post the code for the next step in a minute.
For the next step we will attempt to apply WHERE criteria to the Query Object you created. If you provided the correct Query name, it should work. You just need to replace the address with your own email address (like before).
You should have a form with a single Command Button on it. This form should have tested successfully. Now, we will add a second button. Give this button a good name and also change the Caption to indicate this button does something specific. You should have two buttons on your form, now.
In the second button's click event, paste the following. You need to use your own Email address. Also, you need to change 'ReplaceWithFieldName' with the word Code . The idea is that when this code executes, it will email you multiple emails. Each Email should include a unique report. 'WHERE (((Missions.Code)="SomeVariable"))
If you get an error, make a note of what the error says and click OK. Then, make a note of which line of code get's highlighted. Post any questions you have here and if you have success or failures. The last step will be to make the To email address dynamic. You will need to let me know if the email address is available within your "All Payments to Partners" query and what the name of the field is.
Code:'Declare variables and assign values Dim strReportName As String Dim strPath As String Dim strFullPath As String Dim strEmailAddress As String Dim strSubject As String Dim strBody As String strReportName = "All Payments to Partners" strEmailAddress = "TestEmailAddress@MyDomain.Com" strSubject = "This is my report" strBody = "" 'before exporting your report you will want to make 'sure there is not a file in the folder with the same name 'and that the folder actually exists strPath = manageTempFolder strFullPath = strPath & "\" & strReportName & ".pdf" 'Bail if there is a problem with the directory If strPath = "" Then Exit Sub End If 'We will use the main query and apply FindFirst method 'to retrieve specific records Dim strQueryName As String strQueryName = "All Payments to Partners" 'Use DAO to open a recordset and iterate Dim db As DAO.Database Dim rs As DAO.Recordset Dim qryDef As DAO.QueryDef Dim strSQL As String Dim strWhere As String Set db = CurrentDb Set rs = db.OpenRecordset(strQueryName, dbOpenSnapshot) Set qryDef = db.QueryDefs(strQueryName) strSQL = qryDef.SQL rs.MoveFirst 'Use early binding to create an email Dim objOutlook As New Outlook.Application Dim objNewEmail As MailItem Dim objAttachReport As Attachments While rs.EOF = False 'WHERE (((Missions.Code)="AIM")) strBody = "Dear " & rs![ReplaceWithFieldName] & ":<br><br>Please find a copy of my report attached here." strWhere = " WHERE Missions.Code = '" & rs![ReplaceWithFieldName] & "'" qryDef.SQL = Replace(strSQL, ";", "") & strWhere 'create a report in the temp folder DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFullPath 'Instantiate your objects Set objNewEmail = objOutlook.CreateItem(olMailItem) Set objAttachReport = objNewEmail.Attachments 'Add your file to the Attachments Collection objAttachReport.Add strFullPath, olByValue With objNewEmail .BodyFormat = olFormatRichText .To = strEmailAddress .Subject = strSubject .HTMLBody = strBody 'Save the email to ensure the attachment is a 'copy of the original file (olByValue) .Save .Send End With 'Delete the report you just emailed ''Kill strFullPath 'Reset the Attachments collection 'Using the collection is useful when there is more 'than one attachnebnt per email Set objNewEmail = Nothing Set objAttachReport = Nothing 'Rinse and repeat rs.MoveNext 'Close the loop Wend 'rs.EOF now is True 'tidy up Set objAttachReport = Nothing Set objNewEmail = Nothing Set objOutlook = Nothing qryDef.SQL = strSQL Set qryDef = Nothing rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"
Syntax Error (missing operator) in query expression '[Non-Cheque Payments].Date WHERE Missions.Code = 'AIM''. It highlights qryDef.sql = Replace(strSQL, ";", "") & strWhere in the Code.
The Table referred to is part of the Query 'All Payments to Partners'. Can you spot the problem? The query works OK and produces the results expected.
The Email field address is 'E-mail Address' and is in the output from the query OK.
You have some reserved words in your database. Date is a reserved word and it is creating issues. Also, Hyphens, as well as other special characters, are frowned upon. The exception in the special characters thing is the hyphen. Renaming your fields would be best. However, we may be able to get things rolling by editing the SQL. You already know how to get to SQL view of your query object. Copy the SQL and post it here using the forum's code tags.
Trust this assists you - sorry about my field names (as you will appreciate, I am self taught and perhaps didn't pick up some of the basics early enough!)Code:SELECT [Prefix] & " " & [Full Name (Exc Prefix)] AS Partner, [Non-Cheque Payments].Date, [Non-Cheque Payments].Amount, Missions.[E-mail address], [Non-Cheque Payments].CodeFROM [Non-Cheque Payments] INNER JOIN Missions ON [Non-Cheque Payments].Code = Missions.Code WHERE ((([Non-Cheque Payments].Code)<>"CONEX" And ([Non-Cheque Payments].Code)<>"PAYPA" And ([Non-Cheque Payments].Code)<>"FLOAT" And ([Non-Cheque Payments].Code)<>"WEC")) ORDER BY [Prefix] & " " & [Full Name (Exc Prefix)], [Non-Cheque Payments].Date;
I'm going to have to call it a day soon - bedtime calls here. Hopefully you will be able to get me a step nearer success and perhaps we could complete the journey sometime tomorrow?
Not a problem. We still have a little ways to go, testing this step and implementing the dynamic email addresses. I will work something up and you can post back when you get the chance.
I revised the code that belongs in the Click event of your button. I changed the SQL quite a bit. I have no way to test it so you get to do that for us.
Code:'Declare variables and assign values Dim strReportName As String Dim strPath As String Dim strFullPath As String Dim strEmailAddress As String Dim strSubject As String Dim strBody As String strReportName = "All Payments to Partners" strEmailAddress = "TestEmailAddress@MyDomain.Com" strSubject = "This is my report" strBody = "" 'before exporting your report you will want to make 'sure there is not a file in the folder with the same name 'and that the folder actually exists strPath = manageTempFolder strFullPath = strPath & "\" & strReportName & ".pdf" 'Bail if there is a problem with the directory If strPath = "" Then Exit Sub End If 'We will use the query object as the recordsource of the Report. 'and assign dynamic SQL to the QueryDef Dim strQueryName As String strQueryName = "All Payments to Partners" 'Use DAO to open a recordset and iterate Dim db As DAO.Database Dim rs As DAO.Recordset Dim qryDef As DAO.QueryDef Dim strSQL As String Dim strWhere As String Dim strOrderBy As String strSQL = "SELECT [Prefix] & ' ' & [Full Name (Exc Prefix)] AS Partner, " & _ "[Non-Cheque Payments].[Date], [Non-Cheque Payments].[Amount], [Missions].[E-mail address], " & _ "[Non-Cheque Payments].Code " & _ "FROM [Non-Cheque Payments] INNER JOIN [Missions] ON [Non-Cheque Payments].Code = [Missions].Code " strWhere = "WHERE ([Non-Cheque Payments].Code NOT IN ('CONEX', 'PAYPA', 'FLOAT', 'WEC')) " strOrderBy = "ORDER BY [Prefix] & ' ' & [Full Name (Exc Prefix)], [Non-Cheque Payments].[Date];" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL & strWhere, dbOpenSnapshot) Set qryDef = db.QueryDefs(strQueryName) rs.MoveFirst 'Use early binding to create an email Dim objOutlook As New Outlook.Application Dim objNewEmail As MailItem Dim objAttachReport As Attachments While rs.EOF = False 'WHERE (((Missions.Code)="AIM")) strBody = "Dear " & rs![ReplaceWithFieldName] & ":<br><br>Please find a copy of my report attached here." strWhere = strWhere & "AND [Missions].Code = '" & rs![ReplaceWithFieldName] & "' " qryDef.SQL = strSQL & strWhere & strOrderBy 'create a report in the temp folder DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFullPath 'Instantiate your objects Set objNewEmail = objOutlook.CreateItem(olMailItem) Set objAttachReport = objNewEmail.Attachments 'Add your file to the Attachments Collection objAttachReport.Add strFullPath, olByValue With objNewEmail .BodyFormat = olFormatRichText .To = strEmailAddress .Subject = strSubject .HTMLBody = strBody 'Save the email to ensure the attachment is a 'copy of the original file (olByValue) .Save .Send End With '' 'Delete the report you just emailed Kill strFullPath 'Reset the Attachments collection 'Using the collection is useful when there is more 'than one attachnebnt per email Set objNewEmail = Nothing Set objAttachReport = Nothing 'Rinse and repeat rs.MoveNext 'Close the loop Wend 'rs.EOF now is True 'tidy up Set objAttachReport = Nothing Set objNewEmail = Nothing Set objOutlook = Nothing qryDef.SQL = strSQL Set qryDef = Nothing rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"
Good morning ItsMe, thanks for the revised coding. It was successful in dispatching emails but there are 2 glitches -
1. It sent an email to the Partners for each of the lines in my query - 'All Payments to Partners'. But this query has a line for each payment that went to each of the Partners in the course of the year. So for some there was one email but for others there was a variable number depending on the number of payments made in the course of the year! So i had a total of 75 emails instead of just 32! However I had created another query which has a single line for each Partner - 'All Payments to Partners (Single)'. Is it possible to feed that into the code so that only one email is sent to each Partner?
2. While the attachment to the first Email (to AIM) contained the details of the 4 payments that were made, each of the subsequent reports had no information - although when I look at the print preview of the Report in Access all the appropriate details are there. Did I perhaps make a mistake in entering the field name "Code" in the part of the code that you had highlighted in red? Or is there some other cause?
This is certainly progress! I trust it will be possible to iron out these 2 points.
It sounds like you may need a subreport. To get this correct, you need to test your query and your report. Review post #19. Hardcode your query object. Test your report. You need to understand what WHERE criteria will work for your needs. You may discover that you need two queries and two reports, one query for each of the reports. A second report will act as a subreport. It sounds like the first report may need to be based on a Totals query. So, in addition to a WHERE clause you may need to use the Totals tool (Sigma Icon in Design View). Use the designer. The WHERE clause may need to be replaced by a HAVING clause. Using the designer and the Totals tool will manage the SQL syntax.
Whatever you can do to make the most simple query that cerates 32 records vs. 70+ records, do that. As mentioned in post #19, do not worry about the VBA and the email. Just worry about the report. Anything is possible, so worrying about a topic that you are not currently working on does no good. Build the query or queries as if you were preparing for a meeting with a single partner.
The VBA is doing its own thing. So do not read too much into what data appears in the reports that were emailed. Considering the details section of the report showed multiple records, you may be able to get the job done without the need for a subreport. The answers are in hardcoding a query object and testing the report. If you can open a report in Print Preview and get the details section correct, make sure the report does not have duplicate pages. I cannot stress it enough that this is something that is sorted out before building a graphical interface for emails. If you get one partner looking good in a report, randomly choose another partner and test that partner by hardcoding them.While the attachment to the first Email (to AIM) contained the details of the 4 payments that were made, each of the subsequent reports had no information - although when I look at the print preview of the Report in Access all the appropriate details are there. Did I perhaps make a mistake in entering the field name "Code" in the part of the code that you had highlighted in red? Or is there some other cause?
It is likely your field Code is the correct one. I say this because you are Joining on the Code column. On a side note. You may want to try using a query that has an Outer Join. Currently, your query is using an Inner Join. When you drag a field from one table to the next table's field, you create a JOIN. You can adjust the JOIN's properties while in the Designer by double clicking the line. I usually select the line by clicking it once. Then I double click it to open the join Dialog Box.
Thanks for your comments. Just by way of clarification –
1. My query 'All Payments to Partners' (on which the Report 'All Payments to Partners' is based) has the following fields - Partner, Date, Amount, E-mail Address & Code. The criteria for code excludes 'CONEX', 'PAYPA', 'FLOAT', 'WEC' to produce a dataset of 75 lines.
2. My report uses the output from this query and produces a 32 page PDF document which has, after the common body of the report, a Partner Header section with the Partner field in it. It then has a Detail section with the Date and Amount fields in it. And finally a Partner Footer section with only the SUM([Amount]) field which shows the total of all payments made to that Partner.
3. The above works perfectly. And when I hardcode the criteria in the Code field in the query to be Like ‘ECM’ it produces a single page report with the appropriate details for that Partner – there are no duplicate pages.
4. I have changed the INNER JOIN to an OUTER JOIN in the query but it still produces the same output so I have returned it to an INNER JOIN.
When I said the following in my previous postI was suggesting that in the appropriate part of your code might it be possible to select each Partner’s code from this ‘Single’ query rather than from the full query? Because that would pick up 32 codes instead of 75. But I didn’t know where abouts in your code that change might be made. But my assumption is that if the 32 different Partners were identified then the Report would take care of itself! Although I don't know why the appropriate details for each of the Partners after the first one aren't appearing in the single page email attachments.However I had created another query which has a single line for each Partner - 'All Payments to Partners (Single)'. Is it possible to feed that into the code so that only one email is sent to each Partner?
What is the name of the field that you get ECM from, 'Code'? What happens when you try a different partner? Can you post that SQL here? I realize you have probably posted the SQL already.when I hardcode the criteria in the Code field in the query to be Like ‘ECM’ it produces a single page report with the appropriate details for that Partner – there are no duplicate pages.
The report works exactly as intended when I hardcode any of the Partners' codes.Code:SELECT [Prefix] & ' ' & [Full Name (Exc Prefix)] AS Partner, [Non-Cheque Payments].Date, [Non-Cheque Payments].Amount, Missions.[E-mail address], [Non-Cheque Payments].Code FROM [Non-Cheque Payments] INNER JOIN Missions ON [Non-Cheque Payments].Code = Missions.Code WHERE ((([Non-Cheque Payments].Code)<>"CONEX" And ([Non-Cheque Payments].Code)<>"FLOAT" And ([Non-Cheque Payments].Code)<>"PAYPA" And ([Non-Cheque Payments].Code)<>"WEC" And ([Non-Cheque Payments].Code) Like "OM"));
Yes, the 'ECM' or 'OM' comes from the 'Code' field.