Please help. ref sending multiple emails in access

[richarduk1983]

I have a good knowledge of access and visual basic, but i am not at an advanced level.

I am trying to build a button that emails a list of clients(contain ed within a query)
a receipt or letter and personalizes the body with data from the same table/query.

I am using Microsoft access 2010 and windows mail live 2012.

I have managed to be able to send a single email, personalize the body with data from the table/query and attach a report in pdf format.

I would be truly greatfull if you can assist.

Additional imformation

QUERY: receiptstosend
EMAIL FIELD: email_address
REPORT: receipt

Many Thanks
Richard

[Seth Schrock]

You just need to loop your your query. Setup a DAO Recordset in VBA with your query being the recordset. You would put your code that worked for one email inside the loop.

[ADezii]

The process is exactly as Seth describes. Here is a little more detail that will Export the Report and send it to multiple E-Mail Recipients (AIR CODE):

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strBody As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("qryEMails", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    strSubject = "Letter to " & ![First Name] & " " & ![Last Name]
    strBody = "YaDa - YaDa"
      DoCmd.SendObject acSendReport, "<Report Name>", acFormatPDF, _
                       ![E-mail Address], , , strSubject, strBody
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[richarduk1983]

Thankyou so much for your help so far. I have used the code and managed to get it to produce the correct number of emails (7) though there is a problem, its using all the information from the first set of records for all 7 emails. Please help. Bellow is my upto date code.
Regards
Richard

Private Sub Command7_Click()

    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String
    

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("queryyelllettertosend", dbOpenForwardOnly)

    With rst
      Do While Not .EOF
        strSubject = "TEST" & [first_name] & " " & [surname]
        strBody = "Dear" & " " & [first_name] & vbCrLf & vbCrLf & "Please find attached a letter referring to leaving our company feedback with reference to you move dated" & " " & [movedate]
        stDocName = "receipt"
          DoCmd.SendObject acSendReport, stDocName, acFormatPDF, email_address, , , strSubject, strBody
          .MoveNext
      Loop
    End With

    rst.Close
    Set rst = Nothing

End Sub

[Seth Schrock]

You are close, but you aren't referencing the recordset fields in your code, but the controls in your form. Try this:

Private Sub Command7_Click()
 
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String
 
 
    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("queryyelllettertosend", dbOpenForwardOnly)
 
    With rst
      Do While Not .EOF
        strSubject = "TEST" & ![first_name] & " " & ![surname]
        strBody = "Dear" & " " & ![first_name] & vbCrLf & vbCrLf & "Please find attached a letter referring to leaving our company feedback with reference to you move dated" & " " & ![movedate]
        stDocName = "receipt"
          DoCmd.SendObject acSendReport, stDocName, acFormatPDF, email_address, , , strSubject, strBody
          .MoveNext
      Loop
    End With
 
    rst.Close
    Set rst = Nothing
 
End Sub

[richarduk1983]

THANKYOU. WOW THIS WORKS!!!!!!

I have one more question and then i should be good to go.
When access comes across an empty email text box, it stops. Not all our clients use emails ( we have a lot of elderly customers who use post only. is there any way of organizing this??
bellow is up to date code
Regards
Richard

Private Sub emailyellcomletters_Click()
 
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEMail As String
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String
    
    
 
    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("queryreviewlettertosend", dbOpenForwardOnly)
 
    With rst
      Do While Not .EOF
        strEMail = ![email_address] & ";"
        strSubject = "REVIEW"
        strBody = "Dear" & " " & ![first_name] & vbCrLf & _
"bla bla bla"

          DoCmd.SendObject , , , strEMail, , , strSubject, strBody
          .MoveNext
      Loop
    End With
 
    rst.Close
    Set rst = Nothing
 
End Sub

[Seth Schrock]

Please use code tags when posting code (just click on the [CODE/] button).

You can just put an If-Then statement to test if the email_address field is null right below your Do While statement and have the End If just above your .MoveNext statement. This is how I would do it:

If !email_address & "" <> "" Then

[richarduk1983]

Seth i cannot thank you enough. its great and very stream lined.

My next stage is to add an individual receipt, to the individual email.
I have managed to add an individual receipt, to an individual email, when only one persons details are listed on a form using a report based on a query collecting the key information such as client number from the form. filtering out all the other clients.

My problem comes when i try to do the same when multiple clients are listed. It addresses the individual, entering the email address, name, subject(individ ual to the client) and other personal details within the body(individual to the client) but when its comes to the receipt, it attaches as a pdf all the receipts for every client in one pdf.

Is their a way of filtering this??
I hope ive explained this correctly.

Additional information is as follows:
the table of details displayed within a sub form are taken from a query.
the receipt is a report converted through access to a pdf.

code as follows:

Private Sub Command7_Click()
 
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEMail As String
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("queryreceipts", dbOpenForwardOnly)
 
    With rst
      Do While Not .EOF
      If !email_address & "" <> "" Then

        strEMail = ![email_address] & ";"
        strSubject = "Ladygate Removals"
        strBody = "Dear" & " " & ![first_name] & vbCrLf & vbCrLf & "Please find attached your receipt dated" & " " & ![movedate] & ![client_no]
        stDocName = "receiptemailtest"
          DoCmd.SendObject acReport, stDocName, acFormatPDF, strEMail, , , strSubject, strBody
                    
          End If
          
          .MoveNext
      Loop
    End With
 
    rst.Close
    Set rst = Nothing
 
End Sub

[Seth Schrock]

The problem is that the report doesn't have the criteria of the current record of your loop. Try opening the report using the DoCmd.OpenRepor t() command with the criteria of the current record of your loop then have your DoCmd.SendObjec t() and then close the report after that. I think that it will then send the report as it is open, but you would have to check that to verify that I'm correct. There is another way if that doesn't work, but it is much more complicated so I'm hoping that we don't have to go that route.