Attaching Subform & Form to email Command button

[Plukey]

I am able to attach Main forms data and generate mail but unable to attach subform's data. Here's the current code provided by a google search. Getting Error "Item not found in this collection'

Private Sub cmdEmailList_Click()
On Error GoTo Err_cmdEmailList_Click

    Dim CustName As String      ' Customer Name
    Dim varTo As Variant        '-- Address for SendObject
    Dim stText As String        '-- E-mail text
    Dim DelDate As Variant      '-- Rec date for e-mail text
    Dim stSubject As String     '-- Subject line of e-mail
    Dim stOrderID As String     '-- The Order ID from form
    Dim detailQry As String
    Dim dQuery As String
    Dim rs As DAO.Recordset
   
    Set rs = Me![subformquestions subform].Form.RecordsetClone
While Not rs.EOF
     dQuery = dQuery & rs![Brand Name].Value & vbTab & rs![Model Name].Value & vbTab & rs![Color].Value & vbCrLf
Wend
Set rs = Nothing
  
   
    CstName = Me![Planner]
    varTo = Me![Notes]
    stSubject = ":: Second Check is Complete ::"
    DelDate = Me.[WorkPackage]
    stText = "Dear" & CstName & _
             "Your Workpackage has been checked." & Chr$(13) & Chr$(13) & _
             "Please refer to your WPP Questions " & Chr$(13) & _
             "WPP Number: " & DelDate & Chr$(13) & Chr$(13) & _
              dQuery & Chr$(13) & _
             "This is an automated message. Please do not respond to this e-mail."

    'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, True
Err_cmdEmailList_Click:
    MsgBox Err.Description
  
End Sub

 

[Micron]

You should indicate which line raises the error. Sorry if I missed it but I took a good look and saw no indication.
I would always validate that a recordset has records before proceeding.
You might need a space after Dear when you get it working.

 

[Joe4]

I don't know if this plays any role in it, but in some cases you are using a period after "Me" and in other places you are using an exclamation point, i.e.

    CstName = Me![Planner]
    varTo = Me![Notes]
    stSubject = ":: Second Check is Complete ::"
    DelDate = Me.[WorkPackage]

 

[Micron]

! provides late bound access to the default member of what precedes ! by passing what follows it as the name argument for that member.
So for Me (which is the form or report object) the default member is the controls collection. "Notes" would have to be the name of a control on the form or report.
I never use ! in references unless it applies to recordset objects, which may or may not actually require the ! vs dot, but that is another topic.
Since the reference is late bound, compiling code that uses Me!txtMyControll when the control name is MyControl will not fail until run time, whereas Me.Controll will fail at compile time.
HTH

 

[Plukey]

I appreciate the Feedback! Since my post...I attempted to try a different approach. This form is a questionnaire roughly 32 questions w/ YES or No check boxes. Once the questions are checked the user clicks the email button & it emails the form. =This line opens the query with the filter and gives me the attachment that i need.

DoCmd.OpenReport "subformquery", acViewPreview, , "PSB='" & PSB & "'"

all i need now is to place the form as an attachment in an email.

If I remove the below from the code the email generates & that's as far as I've gotten. But when I leave it in it just says ....

 Set rs = Me!subformquery.Form.RecordsetClone
While Not rs.EOF
     dQuery = dQuery & rs![Brand Name].Value & vbTab & rs![Model Name].Value & vbTab & rs![Color].Value & vbCrLf
Wend
Set rs = Nothing

It says

 

[Plukey]

Alright, here's what I've come with...seems to be a lot simpler, except where it says Planner. Id like it to look in the PLanner Box & use that userID when the outlook comes up.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

DoCmd.OpenReport "subformquery", acViewPreview, , "PSB='" & PSB & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, "Planner", , , "WPP Checklist", "Your Workpackage has been checked.", True

Exit_Command23_Click:
    Exit Sub

Err_Command23_Click:
    'MsgBox Err.Description
    Resume Exit_Command23_Click
End Sub

 

[Micron]

You want to replace "Planner" with what's showing in combo (SHF00)? I must not be understanding because I doubt SHF00 is a valid Outlook recipient.

 

[welshgasman]

So swap "Planner" for Me.UserID

 

[Plukey]

So swap "Planner" for Me.UserID

Yep, I think I've been looking at it for too long, should've known that. Thank you!