Passing FIELDS from a SUBFORM into STRINGS for email 2

[humvie]

I am trying to take data from a subform to include it in an email. It works fine when only referring as such [subform]![field] but then it only shows the first line of the subform records.

Does anyone know how to include all records from the subform?

This is what I have to date:

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'**************************************************************
'*create string with email address

strEmail = Text85
strBody = strBody & "We are please to announce your order has been shipped. Below you will find the appropriate" & _
 " shipment details. For more information please contact customer service." & Chr(13) & Chr(13) & Chr(13)
strBody = strBody & "Date Shipped: " & Date & Chr(13)
strBody = strBody & "Carrier Name: " & CarrierName & Chr(13)
strBody = strBody & "Customer Order Ref: " & CustomerOrder & Chr(13)
strBody = strBody & "Ref Number: " & OurOrder & Chr(13)
strBody = strBody & "Delivery Instructions and Other Pertinent Information: " & Instructions & Chr(13) & Chr(13)
strBody = strBody & "BOL Line Details: " & [tblShipmentDetails subform]![detQTY] & [tblShipmentDetails subform]![detUnitQty] & Chr(13) & Chr(13)
strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & "shipping department."

'***creates and sends email
With objEmail
    If Not IsNull(Text85) Then
    .To = strEmail
    End If
    .Subject = "SHIPMENT NOTIFICATION"
    If Not IsNull(Text87) Then
       .CC = (Text87)
    End If
    .Body = strBody
    If Not IsNull(Text85) Or Not IsNull(Text87) Then
    .Send
    End If
End With


Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
'objOutlook.Quit

Exit Sub

 

[MakeItSo]

Try not referring to the fields on your form, cause you will only get the current record.
[blue]In order to get all records, you can loop through the form's data source:

Dim rs as Recordset, Carriers as String...
...
Set rs= Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
YourVariable=rs!{i]respectiveFiled in your query/table[/i]
for example
Carriers=Carriers & rs!CarrierName
rs.MoveNext
Loop

rs.Close
[/blue]

Does that help you?
;-)
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[humvie]

Thanks Andreas,

But when I do as suggested, I get an error telling me "Item not found in this collection"

I think this is because it's trying to refer from the current form but the actual records should be from a subform location within the main form.

Subform is called "tblShipmentDetailsSubform" (note I removed the space seeing it is bad programming practice)

 

[Quehay]

humvie,

You're now using the recordset rather than form, so referring to the field names of the table, i.e., ORDER_DETAILS.quantity
rather than [tblShipmentDetails subform], etc., should fix this.


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[MakeItSo]

Thanks for the hop-in, Jeffrey! ;-)
And many thx for the pinkies
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos