none
Send Access report by email to all records RRS feed

  • Question

  • I have a form named "Détails du message" that is linked to the query "tblMessage Requête", I can't figure out the code to send email to all records with the report based on the same query.

    Here is the code to send 1 email and it works perfectly:

    Private Sub EnvoiCourrielUnique_Click()
    On Error GoTo EnvoiCourrielUnique_Err
    
    Dim strMessageHeader As String
    Dim strMessagePasDeSujet As String
    Dim strMessagePasDeCorps
    
    Select Case Forms![Détails du message].Langue.Caption
        Case "FR"
            strMessageHeader = "Logiciel Auto-Caisse, Inc. MESSAGE!"
            strMessagePasDeSujet = "Vous devez inscrire un sujet avant d'envoyer un courriel!"
            strMessagePasDeCorps = "Vous devez inscrire une description du courriel avant d'envoyer!"
                Case "EN"
            strMessageHeader = "Auto-Caisse Software, Inc. MESSAGE!"
            strMessagePasDeSujet = "You must enter a subject before to send an email!"
            strMessagePasDeCorps = "you must enter a description for this email before to send"
    End Select
    
    If IsNull(Me.Sujet) Then
    Call MsgBox(strMessagePasDeSujet, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    If Me.Body = "" Then
    Call MsgBox(strMessagePasDeCorps, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    
        With CodeContextObject
            On Error Resume Next
            DoCmd.SendObject acReport, "rptMessagesUnique", "PDFFormat(*.pdf)", Courriel, "", "", .Sujet, .Body, False, ""
        End With
    Forms![Détails du message].SetFocus
    DoCmd.GoToControl "AprèsMAJCourriel"
    
    EnvoiCourrielUnique_Exit:
        DoCmd.GoToControl "AprèsMAJCourriel"
        Exit Sub
    EnvoiCourrielUnique_Err:
        MsgBox Error$
        Resume EnvoiCourrielUnique_Exit
    
    End Sub

    Thank you all


    Claude Larocque

    Thursday, December 15, 2016 10:12 PM

Answers

  • Hi DBguy,

    When I saw the code I told to myself, that's it, I should I thought about this :) but after trying it, what it did is that 5 emails were sent because of the 5 records, but it were sent to the same record, the first one.

    However, this is something interesting and I am going to play with it a little to see if I can find the solution, but I am convince that you directed me the right way... I will keep you inform here.

    Thanks!

    Claude


    Claude Larocque


    Hi Claude,

    Sorry to hear it didn't work. If you want, you can try it another way. For example:

    DoCmd.GoToRecord , , acFirst
    
    Do While Me.Recordset.EOF = False
        EnvoiCourrielUnique_Click
        DoCmd.GoToRecord , , acNext
    
    Loop
    

    (still untested)

    Hope it helps this time... Cheers!

    Friday, December 16, 2016 5:37 PM
  • I've had a look at your file and the obvious problem with your code is that the strID variable is being assigned the value of the ID column in the form's current record, so remains static.  The value of the current row in the recordset should be assigned to the variable, and this should be within the loop with:

    With rs
        .MoveFirst
         Do While Not .EOF
              strID = .Fields("ID")
              ' etc
         .MoveNext
         Loop
    End With

    However, I don't think this is going to solve your problem  As I said earlier, I think you need to base the report on a query which references a parameter whose value is the ID value at each iteration of the loop, i.e. by referencing a hidden control in the form to which the ID value is assigned in the same way that the strID value is assigned a value above.  Each time the SendObject method is called the report generated as a PDF file will be restricted by the parameter to a separate ID value.  Opening the report in print preview is unnecessary.  

    I can't comment on the Outlook problem as I don't use Outlook.

    Ken Sheridan, Stafford, England

    Saturday, December 17, 2016 1:45 PM

All replies

  • Hi Claude,

    Just a thought, but could you try it this way?

    Dim rs As Object
    
    Set rs = Me.RecordsetClone
    
    With rs
    
        .MoveFirst
        Do While Not .EOF
            EnvoiCourrielUnique_Click
            .MoveNext
        Loop
        .Close
    
    End With
    
    Set rs = Nothing

    (untested)

    Hope it helps...

    Thursday, December 15, 2016 10:42 PM
  • Hi DBguy,

    When I saw the code I told to myself, that's it, I should I thought about this :) but after trying it, what it did is that 5 emails were sent because of the 5 records, but it were sent to the same record, the first one.

    However, this is something interesting and I am going to play with it a little to see if I can find the solution, but I am convince that you directed me the right way... I will keep you inform here.

    Thanks!

    Claude


    Claude Larocque


    Friday, December 16, 2016 9:00 AM
  • When looping through the clone of the form's recordset you need to assign the value of the Courriel column to the To argument of the SendObject method.  If you move the code from the EnvoiCourrielUnique button's Click event procedure to a function declared as Private in the form's module you can call the function at each iteration of the loop, passing the current Courriel value into the function as an argument of the function.  In the code the argument name would be used as the To argument of the SendObject method.  The EnvoiCourrielUnique button's Click event procedure would call the function just once, passing the current Courriel value into the function.

    Ken Sheridan, Stafford, England

    Friday, December 16, 2016 11:23 AM
  • Hi Ken,

    I do play with the code since a few hours, at the moment, I send a PDF document to each individual on the list, however, the pdf document shows all 5 reports, the fields are correct on each report but I want of course to send their own separate document to each client.

    I did entered the email (Courriel) into the line send Object:

    Note: I forgot the Unique button for now, I did create a new query and a new report that is base to all selected clients, in this case, the query "tblMessages Requête" shows all selected clients and the report "rptMessages" is based on that query, here is the code:

    Private Sub SendAllClients_Click()
      On Error GoTo Error_Handler
    
    Dim strMessageHeader As String
    Dim strMessagePasDeSujet As String
    Dim strMessagePasDeCorps
    
    Select Case Forms![frmtblMessages Requête].Langue.Caption
        Case "FR"
            strMessageHeader = "Logiciel Auto-Caisse, Inc. MESSAGE!"
            strMessagePasDeSujet = "Vous devez inscrire un sujet avant d'envoyer un courriel!"
            strMessagePasDeCorps = "Vous devez inscrire une description du courriel avant d'envoyer!"
                Case "EN"
            strMessageHeader = "Auto-Caisse Software, Inc. MESSAGE!"
            strMessagePasDeSujet = "You must enter a subject before to send an email!"
            strMessagePasDeCorps = "you must enter a description for this email before to send"
    End Select
    
    If IsNull(Me.Sujet) Then
    Call MsgBox(strMessagePasDeSujet, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    If Me.Body = "" Then
    Call MsgBox(strMessagePasDeCorps, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    strSQL = "SELECT * FROM [tblMessages Requête]"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    
    With rst
        .MoveFirst
        Do While Not .EOF
            On Error Resume Next
            DoCmd.SendObject acSendReport, "rptMessages", "PDFFormat(*.pdf)", rst!Courriel, , , rst!Sujet, rst!Body, False, ""
            .MoveNext
        Loop
    End With
        
    Closerst:
        rst.Close
        dbs.Close
        Set rst = Nothing
        Set dbs = Nothing
    
    Error_Handler_Exit:
        On Error Resume Next
    Exit Sub
    
    Error_Handler:
        MsgBox "L'erreur suivante est survenue, prenez le no d'erreur en note ET CONTACTEZ LE SUPPORT D'AUTO-CAISSE!" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Détails du message" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "Une erreur est survenue, contactez (514) 767-5440!"
        Resume Error_Handler_Exit
    
    End Sub

    How can I modify the code to send their report and not all reports?

    Thanks for your time

    Claude


    Claude Larocque

    I also tried:

    DoCmd.SendObject acSendReport, "rptMessages", "PDFFormat(*.pdf)", "[Courriel]= & rst![Courriel]", , , rst!Sujet, rst!Body, False

    Same result

    The code below send an email to each one and only 1 report, however, this is the data for the 1st record only, seems like it does not loop??

    Dim dbs As DAO.Database
    Dim rst As Object
    
    Set rst = Me.RecordsetClone
    
    With rst
     .MoveFirst
        Do While Not .EOF
        DoCmd.OpenReport "rptMessages", acViewPreview, , "[Courriel]='" & Me.[Courriel] & "'", acHidden
        DoCmd.SendObject acReport, , "PDFFormat(*.pdf)", rst!Courriel, "", "", .Sujet, .Body, False, ""
        rst.MoveNext
        Loop
    End With
    rst.Close

    Friday, December 16, 2016 12:49 PM
  • You need to restrict the report to the client in question.  You cannot do this in the way you normally would when calling the OpenReport method, as the SendObject method does not have a WhereCondition argument to filter the report.  The way to do it is to include a parameter in the report's RecordSource query which references a control in the form whose value identifies the current client.  As the form remains on a single client's record, however, you cannot use a bound control for this, so you will need to add a hidden text box control to the form, and, at each iteration of the loop assign a value from the recordset, e.g. ClientID or similar, to this control before calling the SendObject method.

    When using the first button to send a report to the current client only you'd assign the current ClientID or similar to the control.

    PS: when sending a report as aPDF file you don't need to open the report in print preview.  For an example see Invoice.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes an option to email the current invoice as a PDF file, though not multiple invoices as in your case.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, December 16, 2016 2:12 PM Postscript added.
    Friday, December 16, 2016 2:05 PM
  • Hi DBguy,

    When I saw the code I told to myself, that's it, I should I thought about this :) but after trying it, what it did is that 5 emails were sent because of the 5 records, but it were sent to the same record, the first one.

    However, this is something interesting and I am going to play with it a little to see if I can find the solution, but I am convince that you directed me the right way... I will keep you inform here.

    Thanks!

    Claude


    Claude Larocque


    Hi Claude,

    Sorry to hear it didn't work. If you want, you can try it another way. For example:

    DoCmd.GoToRecord , , acFirst
    
    Do While Me.Recordset.EOF = False
        EnvoiCourrielUnique_Click
        DoCmd.GoToRecord , , acNext
    
    Loop
    

    (still untested)

    Hope it helps this time... Cheers!

    Friday, December 16, 2016 5:37 PM
  • Well, I search on the net and I saw that I am not the only one struggling to send a report to each record on the list, I appreciate very much all the help you give me, but, I am still having no luck making it work.

    For that reason, I did upload a small database on OneDrive that contains the principal objects needed. Hopefully you can get access to that database, I will check from another computer if I can upload it...

    From another computer I did successfully download the test database, here is the link, this way, you might be able to send the report for each ID or Email with their own data.

    https://1drv.ms/f/s!AoWuL6VEABn3gWVoEmL4KMNLmWca

    Note: I have a query named "tblMessages Requête Unique", this query as a criteria that show only the current ID on the form, but you guys will see that I am sure...

    Claude

    Also, on my computer the Outlook security message appears for each records, I don't think that stops the code from doing its job, no?


    Claude Larocque


    • Edited by Claude Larocque Saturday, December 17, 2016 9:47 AM Add comments
    Saturday, December 17, 2016 9:45 AM
  • I've had a look at your file and the obvious problem with your code is that the strID variable is being assigned the value of the ID column in the form's current record, so remains static.  The value of the current row in the recordset should be assigned to the variable, and this should be within the loop with:

    With rs
        .MoveFirst
         Do While Not .EOF
              strID = .Fields("ID")
              ' etc
         .MoveNext
         Loop
    End With

    However, I don't think this is going to solve your problem  As I said earlier, I think you need to base the report on a query which references a parameter whose value is the ID value at each iteration of the loop, i.e. by referencing a hidden control in the form to which the ID value is assigned in the same way that the strID value is assigned a value above.  Each time the SendObject method is called the report generated as a PDF file will be restricted by the parameter to a separate ID value.  Opening the report in print preview is unnecessary.  

    I can't comment on the Outlook problem as I don't use Outlook.

    Ken Sheridan, Stafford, England

    Saturday, December 17, 2016 1:45 PM
  • Hi Claude,

    I uploaded a modified version of your db.

    Hope it helps...

    Saturday, December 17, 2016 8:14 PM
  • Hi DBguy,

    Thanks for having taking the time, your msgbox works fine when I click on the button, however, when I put my line of code with docmd.sendobject, I have an error 2046 on the DoCmd.GoToRecord , , acNext

    Of course when I am on the last record and I tell to go to next, I am at the end of the recordset, that might be the reason:

    I have uploaded the revised database, here is the link:

    https://1drv.ms/u/s!AoWuL6VEABn3iUGCaRQph0wGShcw

    Note: The email addresses you have in the recordset are valid, so feel free to send as many as you want during the test, if you use outlook of course :)

    Thanks


    Claude Larocque

    Sunday, December 18, 2016 9:25 AM
  • Hi Claude,

    Unfortunately, I don't have Outlook. Do you think maybe it's an issue of timing? Try inserting some DoEvents or SleepAPI after the SendObject method to give the email some time to finish before looping to the next record.

    Just a thought...

    Sunday, December 18, 2016 4:03 PM
  • Thank you anyway, it is amazing on the Internet the number of people that are looking to send pdf documents automatically, and it is amazing as well how complicated it is to do such a simple task.

    However, I found that if I can create multiples PDF documents in a folder, I can afterward send these documents, so I tried this and again I am looking for trouble :)

    I have a table named tblMessages that contains all the messages I want to send, so to create a unique pdf for all records, I have this code:

    Private Sub Commande294_Click() Dim sSQL As String, db As DAO.Database, rs As DAO.Recordset, todayDate As String, fileCourriel As String Set db = CurrentDb todayDate = Format(Date, "MMDDYYYY") sSQL = "SELECT * FROM [tblMessages];" Set rs = db.OpenRecordset(sSQL) fileCourriel = rs![Classer sous] While Not rs.EOF

    'The kill isn't necessary, but if I can't have the name and date then I could delete it and have

    a name included, something like StatementOfAccount_121820016.pdf

    But the code below always recreate the first record anyway...

    If FileExists("C:\Auto-Caisse\Clients\Courriel client\Rapports États de compte PDF\*.*") Then Kill "C:\Auto-Caisse\Clients\Courriel client\Rapports États de compte PDF\*.*" End If fileName = Application.CurrentProject.Path & "\Rapports États de compte PDF\rs![Classer sous]_" & todayDate & ".pdf" DoCmd.OutputTo acReport, "rptMessagesUnique", acFormatPDF, fileName, False rs.MoveNext Wend End Sub

    Of course it can not work at first try, but it does create a pdf file with the date, but I would like to have the name of the customer before the date, that way it will be a unique pdf that I can send to each customer...

    The code above generate a pdf in the right folder, but only 1 pdf without the name but with the rs![Classer sous¿_ instead of the field rs![Classer sous] here an image:

    Note, I have TeamViewer if someone is interested :)


    Claude Larocque


    • Edited by Claude Larocque Sunday, December 18, 2016 6:25 PM more comments
    Sunday, December 18, 2016 6:22 PM
  • Hi Claude,

    The reason you're not getting the name from the record in your filename is because you forgot to concatenate it properly into the variable. Try instead:

    Application.CurrentProject.Path & "\Rapports États de compte PDF\" & rs![Classer sous] & "_" & todayDate & ".pdf"

    Hope it helps...

    Sunday, December 18, 2016 6:27 PM
  • First thank you for your help

    I am almost there, you can be sure that I will post all the necessary code to send multiples pdf files based on a recordset via Outlook when I'm done.

    Here's what I need,

    On the line sfile = I need to have the following, the path =C:\Auto-Caisse\Clients\Courriel client\Rapports États de compte PDF + the field from the recordset named [Classer sous] + the field from the recordset named [NoCarteRepas] + .pdf...

    I thought it will be simple, so I did this:

        'Append attachment
        sFile = "C:\Auto-Caisse\Clients\Courriel client\Rapports États de compte PDF\" & rst![Classer sous] & "_" & rst![NoCarteRepas] & ".pdf"

    and tried also:

    sFile = Application.CurrentProject.Path & "\Rapports États de compte PDF\" & rst![Classer sous] & "_" & rst![NoCarteRepas] & ".pdf"


    Note: that in the rst, these 2 fields exists, matter of fact I create pdf files with that in the code below:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim objOutlook As New Outlook.Application
    Dim objMail As Outlook.MailItem
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblMessages Requête")
    
        Set dbs = CurrentDb
    
    While Not rst.EOF
        With rst
                fileName = Application.CurrentProject.Path & "\Rapports États de compte PDF\" & rst![Classer sous] & "_" & rst![NoCarteRepas] & ".pdf"
                DoCmd.OutputTo acReport, "rptMessagesUnique", acFormatPDF, fileName, False
           .Edit
           !EnvoyerCourriel = False
           !PDFCréé = True
           .Update
            Forms![frmtblMessagesNew].Requery
    
        End With
           rst.MoveNext
        Wend


    Note that the query "tblMessages Requête" as for criteria that the field [EnvoyerCourriel] is set to false, so after the pdf file is created, I requery the form and the first record goes away because that field is set to true and the field [PDFCréé] is set to true, then it creates the second pdf file and the current record goes away:

    and below the code to send the email:

    Private Sub SendEmail()
     On Error GoTo Err_SendEmail
    
     Dim objOutlook As New Outlook.Application
     Dim objMail As Outlook.MailItem
     Dim sSQL As String, dbs As DAO.Database, rst As DAO.Recordset
     Dim sTitle As String, sFile As String, sErr As String
    
        'Prelims
        DoCmd.SetWarnings False
        DoCmd.Hourglass True
        Set dbs = CurrentDb
    
        'Prepare email message
        Set objMail = objOutlook.CreateItem(olMailItem)
        With objMail
    
        'Build recordset on recipients
        Set rst = dbs.OpenRecordset("tblMessages Requête à envoyer")
    
        'Add Recipient
        While Not rst.EOF
    
           With .Recipients.Add(rst![Courriel])
              .Type = olTo
           End With
    
           rst.MoveNext
        Wend
    
        'Add the subject
        .Subject = "État de compte de votre carte-repas"
    
        'Add standard message text to body
        .Body = "Vous avez reçu un message!" & vbCrLf
        .Body = .Body & "de la part de Bleu Lotus service de cafétéria" & vbCrLf
       
        'Closure
        .Body = .Body & vbCrLf & "Sincèrement" & vbCrLf & vbCrLf & "Nathalie, Bleu Lotus"
        'Append attachment
        sFile = Application.CurrentProject.Path & "\Rapports États de compte PDF\" & rst![Classer sous] & "_" & rst![NoCarteRepas] & ".pdf"
        sTitle = "Message de Bleu Lotus"
        With .Attachments.Add(sFile, olByValue)
             .DisplayName = sTitle
        End With
    
        'Send the mail message
        .Send
    
        End With
        rst.Close
        Set rst = Nothing
        Set objMail = Nothing
        Set objOutlook = Nothing
    
    Exit_SendEmail:
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
        Exit Sub
    
    Err_SendEmail:
        sErr = "Error " & Error & " / " & Err
        MsgBox sErr, vbInformation + vbOKOnly, "Error on Email subroutine"
        Resume Exit_SendEmail
        
     End Sub

    Note: After the pdf files are created, the query "tblMessages Requête à envoyer" shows the records which the pdf files have been created, because the criteria for that query is [PDFCréé] = true and [EnvoyerCourriel] = false

    I have an error # 3021, on the sfile =

    I know it because I check the code 1 line at a time with an error message and everything went well until that line,

    so I guess if I can change the sfile to find the right file, that would be perfect... and these files exists (see picture above)

    error received:

    In case you needs it, I did create a new field name txtPath in my tblMessages table (added in both queries] that gives me the filename that will be created in pdf format:

    Thanks

    Claude


    Claude Larocque



    Monday, December 19, 2016 9:39 AM
  • OK, it seems like it is unclear, so here is a simple question, base on my code SendEmail above how to I add an attachments to all email when the fields txtDir and fileName represents the file I want to send for each record:

    Thanks to all

    NOTE: these 2 fields is on the form where the button to send email is

    Claude


    Claude Larocque


    Tuesday, December 20, 2016 7:20 PM
  • Thank you dbGuy and Ken,

    I have almost completed what I wanted to do, however, I will start a new topic because this one is confusing even to me :)

    So please go to see my next one on how can I send attachments and loop

    Thanks

    Claude


    Claude Larocque

    Thursday, December 22, 2016 11:45 AM
  • Perfect, I have finally found the way to do it, everythings works perfectly... thanks to DBguy and Ken

    Claude


    Claude Larocque

    Friday, December 23, 2016 1:09 PM
  • Hi Claude,

    Congratulations! Glad to hear you got it to work. Ken and I were happy to assist. Good luck with your project.

    PS. I was waiting for your other thread but I guess you found a solution before you had to post a new thread.

    Cheers!

    Friday, December 23, 2016 3:58 PM