Adding Microsoft Access subform data to an email-CodePudding

I have two linked tables in Microsoft access, which I've used to create a main form with a subform containing related records. I would like to send an email that displays all the data displayed in the subform for the parent record (i.e. the record in the main form). I am unfortunately an absolute beginner to VBA; I can get the email draft to the right person with the right message, except for the records from the subform. I assume there's a couple ways to go about it, and I'll use whatever works. I've unsuccessfully tried a loop that prints the value for each field, and a couple loops to add each record to a table and then displaying the table. Here's the latter:


Dim Msg As String
Dim rs As DAO.Recordset
Dim i As Integer
Set rs = Forms!formName!subformName.Form.RecordsetClone

    'Define format for output
    Msg = "Dear " & Contact & ",<P>" & "Email message is here. <P>" & _
    "<BR>"
    
    strTable = "<Table>"

loop attempt 1

Do Until rs.EOF
    strTable = strTable & "<TR>"
        For i = 0 To rs.Fields.Count - 1
            strTable = strTable & rs.Fields(i)
        Next
    strTable = strTable & "</TR>"
    rs.MoveNext
    Loop
    strTable = strTable & "</Table>"

loop attempt2

Do Until rs.EOF
        strTable = strTable & "<tr><td>" & Field1 & "</td><td>" & Field2 & "</td><td>" & Field3 & "</td>"
        rs.MoveNext
    Loop

the email part

  strBody = Msg & strTable

Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
    .BodyFormat = olFormatHTML
    .HTMLBody = strBody
    .To = Email
    .Subject = "Subject"
    .Display

    
End With

Set M = Nothing
Set O = Nothing

End Sub

CodePudding user response：

Say you have two linked tables below:

t_contacts:

contact_id (AutoNumber/Number;fieldsize=Long Integer;PK)
contact_name (Short Text; fieldsize=50)

t_invoices:

invoice_id (AutoNumber/Number; fieldsize=Long Integer; PK)
contact_id (Number; fieldsize=Long Integer; FK)

and a textbox on mainform with contact_id value named txtContactID

Here the code to get recordset data ready:

Dim subformSQL As String, subformParams As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

subformParams = "PARAMETERS [par_contact_id] Long; " 
subformSQL = subformParams & "SELECT * FROM t_invoices " & _
             "WHERE contact_id = par_contact_id"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", subformSQL)
qdf.Parameters("par_contact_id") = Me.txtContactID 
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
On Error Resume Next: rs.MoveLast
On Error Resume Next: rs.MoveFirst

If rs.RecordCount > 0 Then
    'recordset data is ready to use.
    'do your email formatting here

End If

You can use variable to replaces Params. But i used to use this way to prevent data type conflict when executing query.