Page 6 of 7 FirstFirst 1234567 LastLast
Results 76 to 90 of 101
  1. #76
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862

    It may. I will have to take a look. I will have a chance to go over it in about an hour.

  2. #77
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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.
    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
    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.
    Attached Files Attached Files

  3. #78
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    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?

  4. #79
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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)' -
    No, you will replace Missions2 with Missions

  5. #80
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    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.

  6. #81
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  7. #82
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    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.

  8. #83
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    1. Do I revert the 'strEmailAddress' (which is showing my own email address) back to ""? Or do I need to do something different there?
    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 here
    Code:
    strReportName = "All Payments to Partners"
    strEmailAddress = ""
    strSubject = "This is my report"
    strBody = ""
    and here
    Code:
    While rs.EOF = False
    'WHERE (((Missions.Code)="AIM"))
        strEmailAddress = rs![E-mail address]
    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 from
    Code:
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT Missions2.Code, Missions2.[E-mail address] FROM Missions2", dbOpenSnapshot)
    TO
    Code:
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions", dbOpenSnapshot)
    This will cause the assignment of strEmailAddress to be the actual partner email address. This happens in the following statement.
    Code:
    strEmailAddress = rs![E-mail address]
    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
    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.

    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 is
    Code:
                    'Save the email to ensure the attachment is a
                    'copy of the original file (olByValue)
                    .Save
                    .Send
    I 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.

    But yeah, there are still additional ways to test your code before going live.

  9. #84
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    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'!!

  10. #85
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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!

  11. #86
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    Two problems!!

    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 -
    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 "
    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)

    '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.

  12. #87
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The following is what determines which clients need an email.
    Code:
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions", dbOpenSnapshot)
    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.

    Start by creating a new query and paste the existing SQL into SQL view.
    SELECT DISTINCT Missions.Code, Missions.[E-mail address] FROM Missions
    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.

    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.

  13. #88
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    The following is the SQL for the query that selects only those Partners that received payments -
    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)];
    The 3 fields are Partner, E-mail Address and Code.

  14. #89
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    In a previous post you had ...
    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"))
    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?

  15. #90
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    No. Just 1 extra exclusion. These are specific non-Partner codes - they can't be picked up from anywhere else.

Page 6 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Send Emails from outlook
    By WhiskyLima in forum Access
    Replies: 8
    Last Post: 08-11-2014, 11:02 AM
  2. Loop Through Query, Send Multiple Emails
    By tdoolittle in forum Reports
    Replies: 6
    Last Post: 05-12-2014, 09:33 PM
  3. Programming Access to Send Emails?
    By BMW150 in forum Access
    Replies: 8
    Last Post: 09-17-2013, 06:14 PM
  4. Send Emails Automatically
    By cbrsix in forum Programming
    Replies: 10
    Last Post: 10-19-2012, 10:52 AM
  5. Replies: 1
    Last Post: 06-26-2012, 09:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums