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

    As far as first, using the Filter method of the report would be simplest. However, you will still need to iterate through a recordset to get the email addresses and the WHERE criteria. When this is a requirement, I prefer to adjust the Recordsource of the report.

    I would suggest you create a report in a temp folder and email that report using Outlook Automation. The next step would be to test some code that will export a report to a temp folder and email it, without the loop.

    Beginning with a new form and while in Design View, add a single command button and save your form. Paste the following into your Click event of your button. You will want to replace the address in red with your own email address. Like I mentioned, take a baby step and don't try looping through anything yet. Send the report to yourself.
    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 = "Please find a copy of my report attached here."
    
    '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
    'Bail if there is a problem with the directory
    If strPath = "" Then
    Exit Sub
    End If
    'Add the file name to the path
    strFullPath = strPath & "\" & strReportName & ".pdf"
    'create a report in the temp folder
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFullPath
    
    'Use early binding to create an email
    Dim objOutlook As New Outlook.Application
    Dim objNewEmail As MailItem
    Dim objAttachReport As Attachments
    '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
    'tidy up
    Set objAttachReport = Nothing
    Set objNewEmail = Nothing
    Set objOutlook = Nothing
     
    MsgBox "Complete"
    In addition to this code, you will also need a custom function to manage your temp PDF reports. The following code will create a folder where your DB resides and the click event will use this folder to store the temp reports. You would paste this below your click event's End Sub line. Use the enter key to create empty space below your entire Click Event and then paste.
    Code:
    Function manageTempFolder() As String
    '''DELETE the PDF files in the temporary folder
    'If the temp folder does not exist, create it
    '''''****************************************
    
    On Error GoTo ERR_manageTempFolder
    'Start with the DB's current path and add
    'unique subdirectories
    Dim strPath As String
    strPath = Application.CurrentProject.Path & "\TempFolder20581A\TempEmail"
    'set default for Return
    manageTempFolder = strPath
    'Returns empty string on error
     
    'Make sure the path exists and make folder if not
    'Test for the subfolder
        If Dir(strPath, vbDirectory) = "" Then  'make a folder
        
    'Test for the parent
            If Dir(Application.CurrentProject.Path & "\TempFolder20581A", vbDirectory) <> "" Then 'Only need subfolder
                MkDir (strPath)
            Else    'Make both
                MkDir (Application.CurrentProject.Path & "\TempFolder20581A")
                MkDir (strPath)
            End If
        End If
                'Get ready to delete all pdf files
                Dim strKill As String
                strKill = Dir(strPath & "*.pdf", vbHidden)
                'Delete the temporary files
                While strKill <> ""
                    Kill strPath & strKill
                    strKill = Dir(strPath & "*.pdf", vbHidden)
                Wend
                
    EXIT_manageTempFolder:
    Exit Function
     
    ERR_manageTempFolder:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Error # " & Err.Number
    manageTempFolder = ""
    Resume EXIT_manageTempFolder
    
    End Function
    If you have any troubles, do your best to describe the issue.

  2. #32
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Naturally, there is more than one way to achieve the goal, but I had my reasons for not suggesting automation so I'd like to clarify. It's more powerful and complex than what the user needs and is less likely to break in the future due to some sort of OS or Office upgrade down the road, a network platform change and directory changes (where the report is stored). More importantly, according to OP post #16 he/she states they are not proficient in vba, hence I opted for a simpler solution. I did say in post#22 that the recordset would have to contain the email recipient data and would have to be looped through. ItsMe, it''s quite generous of you to post all that code (which appears quite good) and I could be wrong but I think it's way over OP's level of vba knowledge. I guess we suggest what we are willing to support by way of this forum..

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

    More progress

    Thanks ItsMe - I have got the first section of code working OK and an email with the attached report has arrived. I have copied the second section of coding (which you described as follows)
    In addition to this code, you will also need a custom function to manage your temp PDF reports. The following code will create a folder where your DB resides and the click event will use this folder to store the temp reports. You would paste this below your click event's End Sub line. Use the enter key to create empty space below your entire Click Event and then paste.
    and pasted it after the 'End sub' of the previous code but nothing happens. Am I missing something? How is the function called if it is after the end of the Sub command?

    Concerning Micron's latest comments, I think I understand where he is coming from! But my concern is to have a system that works when the operator presses a button on a Menu without having to interact in any way with what might follow. I share your appreciation of ItsMe's generosity in providing me with coding thus far. But if I am to follow Micron's route, unless I have misunderstood him completely, this requires the operator to manually select and process each Partner's report - this seems to defeat my objective of simplification for the operator!

  4. #34
    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 have got the first section of code working OK and an email with the attached report has arrived...
    If you received an email, the second part of code is working. The second part I said to paste is a Private Function. It works together with your click event. The purpose is to create a temp folder to hold the PDF file. If the folder does not exist, it will create the folder. If there is a PDF file inside the temp folder, it will delete it. The following is what calls the function. The function returns a variable string that represents a valid path.
    Code:
    '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
    After the code is executed; After the function is called, you will have an empty folder named 'TempFolder20581A' and a subfolder within. These folders are located in the same directory as your DB file.

    I will post a subsequent post for the next step. The next step will be to email yourself multiple reports. I would normally test a small number of emails/reports. However, to try and keep things simple, we will iterate and email all.

  5. #35
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Hello There.

  6. #36
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for clarifying how the second piece of coding works. But I'm afraid there is no empty Folder called TempFolder20581A in my Database Folder.... or anywhere else on my PC

  7. #37
    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
    Thanks for clarifying how the second piece of coding works. But I'm afraid there is no empty Folder called TempFolder20581A in my Database Folder.... or anywhere else on my PC
    Copy all of the code from your form's module and paste it here. Be sure to wrap the code you paste here in Tags.

  8. #38
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Is there some of your code missing from your last post? There is only one short piece of code which doesn't have anywhere for me to add my email address!

  9. #39
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Code:
    Private Sub Command74_Click()'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 = "*********.net"
    strSubject = "This is my report"
    strBody = "Please find a copy of my report attached here."
    
    
    '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 = "C:\Our Folders\imap"
    'Bail if there is a problem with the directory
    If strPath = "" Then
    Exit Sub
    End If
    'Add the file name to the path
    strFullPath = strPath & "\" & strReportName & ".pdf"
    'create a report in the temp folder
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFullPath
    
    
    'Use early binding to create an email
    Dim objOutlook As New Outlook.Application
    Dim objNewEmail As MailItem
    Dim objAttachReport As Attachments
    '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
    'tidy up
    Set objAttachReport = Nothing
    Set objNewEmail = Nothing
    Set objOutlook = Nothing
     
    MsgBox "Complete"
    End Sub
    
    
    Function manageTempFolder() As String
    '''DELETE the PDF files in the temporary folder
    'If the temp folder does not exist, create it
    '''''****************************************
    
    
    On Error GoTo ERR_manageTempFolder
    'Start with the DB's current path and add
    'unique subdirectories
    Dim strPath As String
    strPath = Application.CurrentProject.Path & "\TempFolder20581A\TempEmail"
    'set default for Return
    manageTempFolder = strPath
    'Returns empty string on error
     
    'Make sure the path exists and make folder if not
    'Test for the subfolder
        If Dir(strPath, vbDirectory) = "" Then  'make a folder
        
    'Test for the parent
            If Dir(Application.CurrentProject.Path & "\TempFolder20581A", vbDirectory) <> "" Then 'Only need subfolder
                MkDir (strPath)
            Else    'Make both
                MkDir (Application.CurrentProject.Path & "\TempFolder20581A")
                MkDir (strPath)
            End If
        End If
                'Get ready to delete all pdf files
                Dim strKill As String
                strKill = Dir(strPath & "*.pdf", vbHidden)
                'Delete the temporary files
                While strKill <> ""
                    Kill strPath & strKill
                    strKill = Dir(strPath & "*.pdf", vbHidden)
                Wend
                
    EXIT_manageTempFolder:
    Exit Function
     
    ERR_manageTempFolder:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Error # " & Err.Number
    manageTempFolder = ""
    Resume EXIT_manageTempFolder
    
    
    End Function

  10. #40
    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
    Is there some of your code missing from your last post? There is only one short piece of code which doesn't have anywhere for me to add my email address!
    Yes, I had to edit post #35. There was an issue with the forum's code tags because one of your field names is 'Code'. I will post the code in a later post, after we sort out the folder thing.

  11. #41
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    According to post #39, you should have a folder there. To test this, try the following. Create a form and add a button. Place the following in the button's click event.
    Code:
    Dim strPath As String
    strPath = Application.CurrentProject.Path & "\TempFolder20581A"
    
    FollowHyperlink strPath
    Let me know how this goes. I want to make sure everything is solid before we move forward. Almost there. just a couple more steps. And BTW, there is no way to avoid iterating through the records and applying a dynamic parameter. So I am not really dragging you through the mud here.

  12. #42
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Error message - "Cannot open the specified file"

  13. #43
    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
    Error message - "Cannot open the specified file"
    OK, we should try again. Use the form that has the code in post # 39 and then look for the folder. It should be sitting next to your DB file. You can also use the example in post #41 to open the directory. The function I provided uses the current directory of the DB file that executes the code. Also, you might want to delete your email address from post # 39.

  14. #44
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I got your code in the Email from the Forum even though it doesn't appear in the web!! The 4 lines after 'Use early binding to create an email were all strung together in one line and I have split them Is this OK. Also there was a funny bit before "Som" as follows -Code:
    ---------
    & "'" I have deleted this!
    Attached is the code as amended - is this what you intended? I haven't attempted to run it yet.
    [CODE]Private Sub Command75_Click()'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 = "******.net"
    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
    '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!
    Code:
     & ":<br><br>Please find a copy of my report attached here."
        strWhere = " WHERE Missions.Code = '" & rs!
        Som
        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"
    End Sub

  15. #45
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I see that it has done the same to mine as it did to yours!!!

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