It may. I will have to take a look. I will have a chance to go over it in about an hour.
It may. I will have to take a look. I will have a chance to go over it in about an hour.
I found the issue. It was in my code. I have uploaded a working example here. You need to un-compress the Zipped folder I have uploaded here. To do this, Right Click and Extract All...
The issue with the code I posted is I did not reset the WHERE clause that loops through Missions2 table. So now, I assign the Original Where statement to the variable within the loop. Before, it was outside the loop.
The Report uses the query object and the query object depends on the Missions table. The VBA code depends on the Missions2 table. The Missions2 is a temporary table for testing. Adjust the Email addresses in the Missions2 to avoid sending test reports to the wrong email addresses.Code:strEmailAddress = rs![E-mail address] strBody = "Dear " & rs![2Code] & ":<br><br>Please find a copy of my report attached here." strWhere = "WHERE ([Non-Cheque Payments].Code NOT IN ('CONEX', 'PAYPA', 'FLOAT', 'WEC')) " strWhere = strWhere & "AND [Missions].Code = '" & rs![2Code] & "' " qryDef.SQL = strSQL & strWhere & strOrderBy
Brilliant!! That does the job. If I copy your latest code into my main DB do I replace the reference to Missions2 with my 'Single' query - 'All Payments to Missions (Single)' - which has the 32 lines that represent the 32 Partners that will get their own page of the Report as an attachment?
No, you will replace Missions2 with MissionsIf I copy your latest code into my main DB do I replace the reference to Missions2 with my 'Single' query - 'All Payments to Missions (Single)' -
Excellent!! I really, really appreciate you sticking with this, and guiding a non-VB guy through the maze to the end. The charity that I assist on a voluntary basis with this Database will be really thankful for all your help.
I will now be able to mark this thread as 'Solved' after a real marathon! Thanks again.
You may want to add some additional safeguards to the code in case there are any records that do not contain an Email Address or a Code, etc. Also, you might want to revise the code the generates the Subject and Body for the email. Let me know.
Thanks again. As the Charity relies on an email address for their communications with Partners and since Partners of necessity must have a code for the system to work, I think additional safeguards would be unnecessary. I think that the Subject and Body of the Emails will be standard each year and I can tweek these in the code as necessary.
I think I have just 2 questions as I tidy up the live version of the code -
1. Do I revert the 'strEmailAddress' (which is showing my own email address) back to ""? Or do I need to do something different there?
2. Is there any easy way to trap the emails before they are sent so that I can be absolutely certain that in the live DB they contain the right information? Perhaps just the first one if that is easier. I'm quite happy that that would not be necessary when I deliver it to the Charity for their use in a live situation - the issue of all 32 emails on the click of the button on the Menu is fine.
When I look at the code in the DB I uploaded, I do not see your Email address anywhere in any of the code. The variable, 'strEmailAddress', gets its assignment here1. Do I revert the 'strEmailAddress' (which is showing my own email address) back to ""? Or do I need to do something different there?and hereCode:strReportName = "All Payments to Partners" strEmailAddress = "" strSubject = "This is my report" strBody = ""It retrieves it from the Missions table. Actually, it receives it from the Missions2 table. But, the Missions2 table is temporary. You will change the VBA fromCode:While rs.EOF = False 'WHERE (((Missions.Code)="AIM")) strEmailAddress = rs![E-mail address]TOCode:Set db = CurrentDb Set rs = db.OpenRecordset("SELECT DISTINCT Missions2.Code, Missions2.[E-mail address] FROM Missions2", dbOpenSnapshot)This will cause the assignment of strEmailAddress to be the actual partner email address. This happens in the following statement.Code:Set db = CurrentDb Set rs = db.OpenRecordset("SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions", dbOpenSnapshot)Code:strEmailAddress = rs![E-mail address]Welcome to the world of automation. Of course, you can do anything. However, the idea of automating the process is so you do not interact with the application. It is not a manual process.2. Is there any easy way to trap the emails before they are sent so that I can be absolutely certain that in the live DB they contain the right information? Perhaps just the first one if that is easier. I'm quite happy that that would not be necessary when I deliver it to the Charity for their use in a live situation - the issue of all 32 emails on the click of the button on the Menu is fine
If you recall, as we were building the code, I kept insisting that you check the query, check the report, etc. That was your verification. Then, we even tested sending multiple emails to yourself. This was an additional test to see if things are working correctly. You even caught an error at that point. So switching the code from using the test table Missions2 to the Production table, "Missions", is the last step. You do this when things are great.
Having said that, there are additional things that can be done to ensure the code functions before going live. This involves Debugging. In fact, I used Debugging techniques to pinpoint the error in my code that you brought top my attention when you looked at the Email attachments.
As I mentioned, there are a multitude of ways to reassure yourself. The code that does the sending isI believe it is as simple as commenting out that .Send method if you want to leave the email you created open and not sent. I do not recall exactly, at the moment. All you really need to do, after testing with Missiosns2, is make sure you have valid emails in the table named Missions.Code:'Save the email to ensure the attachment is a 'copy of the original file (olByValue) .Save .Send
But yeah, there are still additional ways to test your code before going live.
OK. I take your point! I will just be careful to make sure that the appropriate changes are made in the live version of the Code!
Again, very many thanks for your patience and expertise. I think you should consider changing your tagline from 'Sometimes Helpful' to 'Goes the extra mile'!!
I hope things go well for you and don't hesitate to use this forum to get answers to your questions. As for the tagline, I can only be helpful when both sides participate!
It's me again - excuse the pun!!
There are 2 glitches when I put the final code into my live Database -
1. It tries to send an email to every entry in my Missions Table regardless of whether they received any payments in the last year. This was what I was suspicious of in my post #78. Am I OK to use '[Missions]' in this section of the code -
But do I need to replace '[Missions]' with '[All Payments to Partners (Single)]'in this section (this was the query that I created to select only those Partners' details that had received payments)Code: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 "
'WHERE (((Missions.Code)="AIM"))
strEmailAddress = rs![E-mail address]
strBody = "Dear " & rs![Code] & ":<br><br>Please find a copy of my report attached here."
strWhere = "WHERE ([Non-Cheque Payments].Code NOT IN ('CONEX', 'PAYPA', 'FLOAT', 'WEC')) "
strWhere = strWhere & "AND [Missions].Code = '" & rs![Code] & "' "
qryDef.sql = strSQL & strWhere & strOrderBy
2. There are some Partners where there are 2 email addresses that are used to communicate with them. The code doesn't seem to be able to handle that - it choked when it came to the first one of them!! For example if the E-mail Address field contained '1234@here.com ; 5678@here.com' (sorry the hyperlink format jumped in here unintentionally!) is there something that would need to be tweeked to handle that situation?
Sorry to bother you again.
The following is what determines which clients need an email.
So you need to adjust the SQL within that code. I suggest you create a new query and do not combine or use the All Payments to Partners query.Code:Set db = CurrentDb Set rs = db.OpenRecordset("SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions", dbOpenSnapshot)
Start by creating a new query and paste the existing SQL into SQL view.Then go to Design View. Add another table that will help you determine 'whether they received any payments in the last year'. Create a JOIN from the Missions table to the second table. From within the second table, double click the field names you will want to apply criteria against. By double clicking the field names, you will add those fields to the Grid at the bottom of the Query Designer. Add the appropriate criteria. For the fields that you added, un-tick or de-select the 'Show' option.SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions
After you add criteria and un-tick the Show option, look at your query in Datasheet View. When you are satisfied, post the resulting SQL here.
As for the multiple email addresses thing, we will have to look at that after we get the SQL sorted.
The following is the SQL for the query that selects only those Partners that received payments -
The 3 fields are Partner, E-mail Address and Code.Code:SELECT DISTINCT [Prefix] & " " & [Full Name (Exc Prefix)] AS Partner, Missions.[E-mail address], Missions.Code FROM [Non-Cheque Payments] INNER JOIN Missions ON [Non-Cheque Payments].Code = Missions.Code WHERE (((Missions.Code)<>"CONEX" And (Missions.Code)<>"UNALL" And (Missions.Code)<>"FLOAT" And (Missions.Code)<>"PAYPA" And (Missions.Code)<>"WEC")) ORDER BY [Prefix] & " " & [Full Name (Exc Prefix)];
In a previous post you had ...
It seems the list of exclusions is longer, this time (post #88). Is this something that changes often? Is there some data in the tables that could generate this list? Perhaps a query that retrieved a Data Range?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"))
No. Just 1 extra exclusion. These are specific non-Partner codes - they can't be picked up from anywhere else.