Attaching Subform & Form to email Command button

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows
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'


SQL:
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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.
Rich (BB code):
    CstName = Me![Planner]
    varTo = Me![Notes]
    stSubject = ":: Second Check is Complete ::"
    DelDate = Me.[WorkPackage]
 
Upvote 0
! 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
 
Upvote 0
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.
SQL:
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 ....
1667386386770.png


SQL:
 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
 
Last edited:
Upvote 0
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.


SQL:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top