Building email using record loop

[Rainbowhawk]

Hi All

I am currently building an email using the code below, ugly but it seems to work in general.

Private Sub Command3_Click()

Dim Info As String
Dim olkapp As Outlook.Application
Dim olknamespace As Outlook.NameSpace
Dim objmailitem As Outlook.MailItem

Info = "Order Number: " & [Forms]![exceptionsinput]![Order Number] & Chr(13) & Chr(10) & "Customer Name: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![Customer Name] & Chr(13) & Chr(10) & "Outbase: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![Depot ID] & Chr(13) & Chr(10) & "Time Reported: " & [Forms]![DailyExceptionReportV3]![Exception Report time] & Chr(13) & Chr(10) & "Driver Details: " & [Forms]![DailyExceptionReportV3]![Items subform5].[Form]![Driver] & Chr(13) & Chr(10) & "Driver Number: " & [Forms]![exceptionsinput]![TelephoneNumber] & Chr(13) & Chr(10) & "Route: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![B&Q Route Number] & " Drop: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![Call No] & Chr(13) & Chr(10) & "Items " & [Forms]![DailyExceptionReportV3]![Items subform5].[Form]![Item] & " " & [Forms]![DailyExceptionReportV3]![Items subform5].[Form]![Product] & Chr(13) & Chr(10) & [Forms]![DailyExceptionReportV3]![Notes]

Set olkapp = New Outlook.Application
Set olknamespace = GetNamespace("MAPI")

Set objmailitem = olkapp.CreateItem(olMailItem)

With objmailitem
.To = "a@b.com"
.Recipients.ResolveAll
.Subject = "Delivery Exception"
.Body = Info
.Send
End With
Set objmailitem = Nothing
Set olknamespace = Nothing
Set olkapp = Nothing
End Sub

the problem i am trying to overcome is the fields

[DailyExceptionReportV3]![Items subform5].[Form]![Item]
and

[DailyExceptionReportV3]![Items subform5].[Form]![Products]

only give the first item, I can not seem to work out how to replace this with a loop that scrolls through all items related to the Order Number.

Any help appreciated.

 

[namliam]

For peets sake, please for your self and future generations make your code readable...

and for us, please use code tags....

Info = "Order Number: " & [Forms]![exceptionsinput]![Order Number] & Chr(13) & Chr(10) & _ 
       "Customer Name: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![Customer Name] & Chr(13) & Chr(10) & _
       "Outbase: " & [Forms]![exceptionsinput]![exceptioninput_detail].[Form]![Depot ID] & Chr(13) & Chr(10) & _
       "Time Reported: " & [Forms]![DailyExceptionReportV3]![Exception Report time] & Chr(13) & Chr(10) & 
' etc...

I am sure you agree that is much more readable.

Now for your question.

Make a recordset and skip thru it... I presume the required records are on the current form:

dim rs as dao.recordset
set rs = me.recordsetclone
Do while not rs.eof
' insert code here
    rs.movenext
loop
' clean up
rs.close
set rs = nothing

I hope you understand my meaning, if not post back.

Good luck !

 

[Rainbowhawk]

Ok,

Am now at this stage but does not seem to building the string

Dim PList As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do While Not rs.EOF
PList = PList & "Item: " & Me.Item & Me.Product & Chr(13) & Chr(10)
rs.MoveNext
Loop
' clean up
rs.Close
Set rs = Nothing

The email with PList as the body is blank

 

[Rainbowhawk]

Ok, Sorted had the form on data input,

Thanks for all your help

 

[namliam]

Dim PList As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do While Not rs.EOF
    PList = PList & "Item: " & Me.Item & Me.Product & Chr(13) & Chr(10)
    rs.MoveNext
Loop
' clean up
rs.Close
Set rs = Nothing

I hope you normaly indent your coding like above...

Also VbNewLine = Chr(13) & Chr(10)