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

    Quote Originally Posted by Baldeagle View Post
    I see that it has done the same to mine as it did to yours!!!
    Yeah, just delete that code. I know why it is doing it and I will post the correct code here in a bit. I just want to make sure the code you have so far is functioning correctly.

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

  3. #48
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Just spotted how to delete email addresses!!

  4. #49
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Baldeagle View Post
    ...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?
    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.

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

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

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

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

    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?

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

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

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

    We're Getting There!

    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.

  12. #57
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Baldeagle View Post
    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!
    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.

    Quote Originally Posted by Baldeagle View Post
    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?
    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.


    Quote Originally Posted by Baldeagle View Post
    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?
    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.

    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.

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

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

  15. #60
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    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"));
    The report works exactly as intended when I hardcode any of the Partners' codes.

    Yes, the 'ECM' or 'OM' comes from the 'Code' field.

Page 4 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