Email Using Data From Form with SubForm

[WAR40K]

I am having an issue where I am trying to send out an email with information from a form. However, the main form has a subform with important data I also want included in the email. When I use the code the below I get error messages regarding object property can't be found or improper usage etc... Is there someway to callout the fields in the subform to insert that data as well? Thank you in advance

******************code*************************************
Dim email, ref, origin, destination, notes, strBody, stDocName, strFullFileName, stFilter As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'****************beginning of email code*********************************************
email = distribution
ref = "Warranty Investigation report"

strBody = "Warranty Investigation Report for Work Order #: " & wir_no & Chr(13)
strBody = strBody & "See Attached file" & Chr(13) & Chr(13)
strBody = strBody & "Reference WIR #: " & ref_wir & Chr(13)
strBody = strBody & "Date: " & wir_date & Chr(13)
strBody = strBody & "Investigator: " & Chr(13)
strBody = strBody & "Customer Report Number: " & cust_report_number & Chr(13)
strBody = strBody & "Event Location: " & event_location & " Event Date: " & event_date & Chr(13)
strBody = strBody & "Customer: " & customer & Chr(13) 'this line contains a field from my subform.
strBody = strBody & "Interface Part #: " & part_no & " Serial Number: " & serial_no & Chr(13) 'this line contains a field from my subform.
strBody = strBody & "Discrepancy: " & discrepancy & Chr(13) 'this line contains a field from my subform.
strBody = strBody & "Root Cause: " & root_cause & Chr(13)
strBody = strBody & "Corrective Action: " & corrective_action & Chr(13)
strBody = strBody & "Corrective Action Effective Date: " & ca_effective & Chr(13)
strBody = strBody & "Investigation Details: " & investigation_details & Chr(13)
strBody = strBody & "Conclusions: " & conclusions & Chr(13)
strBody = strBody & "Investigation Summary: " & summary & Chr(13) & Chr(13)
strBody = strBody & "Thank you" & Chr(13) & Chr(13)
strBody = strBody & "msAccess MRF Dbase" & Chr(13) & Chr(13)



Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = email
.Subject = ref
.Body = strBody
.Attachments.Add ("c:\WIR_report.snp")
.Display
End With

Set objEmail = Nothing
'objOutlook.quit

***********************end code**********************

 

[PHV]

Instead of customer have you tried this ?
Me![NameOfTheControlHostingTheSubform].Form!customer

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[WAR40K]

Thank you for your input PH,

unfortunately I couldn't get your solution to work either. I finally created a new form that included all the information without the subform and used that as the source for the email. I hid the new form so the method is transparent to the end user. The only thing I don't like is I cannot pass the current record to the new form so I had to prompt the user to enter the record ID (in this case a work order number) so that the form would pull the correct data and only return a single record. Not the most elegant solution, sort of like driving nails with a sledge hammer, but it works for now.