Finding each sentence until next sentence

[DMT Dave]

Hi Guy's i am still getting all on one line, as Moke 123 in post 12 states, it will become one line, how do i now separate the 2 records in this instance to 2 lines ?? mTotal states there is 2 but the line is now separated with the pipes, i am probably not understanding correctly, please forgive me

in the snip below, i have separated the 2 items black and red, the red line should be under the black one (new Line), if there were 10 records in the mBody (recordset) then 10 lines, I have highlighted (j) in the html body is related to vtext

Once again, thank you guy's i think i am not understanding correctly!!, i would be very grateful if you add or tweak or tell where to add what ??

vText = Replace(Replace(Replace(mBody, "." & Chr(13) & Chr(10), "|"), ".", "|"), Chr(13) & Chr(10), "|")
While InStr(vText, "||") <> 0
vText = Replace(vText, "||", "|")
Wend
' Set MyItem = MyApp.CreateItem(olMailItem)
' With MyItem
' .To = "deliveries@removed.co.uk"
' .subject = mDriver & " " & Format(Me.txtDelDate, "dd-mm-yy")
' .Body = mDriver & " - " & Me.txtDelDate & Chr(10) & Chr(10) & _
' mTotal & " " & "items for delivery" & Chr(10) & Chr(10) & _
' "DMT Consignment:" & " " & "C" & DLookup("RecordNo", "tblCustomers", "[Name] = '" & MyCus & "'") & "-" & Me.txtCurrentSheet & Chr(10) & Chr(10) & _
' DMTBody
'.Send
' End With
'End If


If Left(Me.txtCustomer, 3) <> "Gli" Then
If MsgBox("Print 2 x Delivery Notes For:" & Chr(10) & Chr(10) & _
Me.cboIndex3Print & Chr(10) & Chr(10) & _
"Total Items:" & " " & mTotal, vbQuestion + vbYesNo, "PRINT DELIVERY NOTES") = vbNo Then
DoCmd.CancelEvent
Else
DoCmd.OpenReport "rptDeliveryNote", acViewNormal
DoCmd.OpenReport "rptDeliveryNote", acViewNormal
End If
End If
sldLne = "-------------------------------------------------------------------------------------------------"
Lne = ". . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ."
a = TOD & ", " & "Please details of deliveries now assigned for:" & " " & Me.cboIndex3Print.Column(0) & "-" & Me.cboIndex3Print.Column(1)
b = "Attached is a copy of the consignment."
c = "DRIVER DETAILS:"
d = Chr(149) & " Delivery Date: " & Chr(149) & " " & Format(Me.txtDelDate, "dddd-dd-mmm-yyyy")
e = Chr(149) & " Delivery Driver: " & Chr(149) & " " & mDriver & " " & mDriverContact
f = Chr(149) & " Vehicle: " & Chr(149) & " " & Me.cboVehicle
g = "DELIVERY TO:"
h = Chr(149) & " " & Me.cboIndex3Print.Column(0) & " - " & Me.cboIndex3Print.Column(1) & " " & Chr(149)
h2 = "OUR REF: " & Me.txtCurrentSheet & "-" & mRec
j = vText
k = "Due to the current global pandemic situation, we would prefer for our drivers do sign any paperwork on your behalf to prevent any unnecessary handling."
l = "Once you can confirm your delivery is present and correct, you can respond to this email to confirm if you wish to."
'm = "Our Ref: " & Me.txtCurrentSheet & "-" & mRec
Set MyItem = MyApp.CreateItem(olMailItem)
Set OutAccount = MyApp.Session.Accounts.Item(1)
'BodyMessage = TOD & ", " & "Please find attached an update of deliveries now assigned for:" & " " & Me.cboIndex3Print.Column(0) & "-" & Me.cboIndex3Print.Column(1)
With MyItem
.To = MailTo
.CC = mCC & ";" & "deliveries@removed.co.uk"
.subject = Me.cboIndex3Print.Column(1) & " " & "Delivery Assigned For" & " " & Format(Me.txtDelDate, "ddd-dd-mmm-yy")
.Attachments.Add "T:\Fld1\PDF\Delivery Notes" & Me.cboIndex3Print.Column(0) & " " & Format(Me.txtDelDate, "dd-mm-yy") & ".pdf"
.HTMLBody = a & "<br>" & "<br>" & b & "<br>" & "<br>" & sldLne & "<br>" & c & "<br>" & d & "<br>" & e & "<br>" & f & "<br>" & sldLne & "<br>" & g & "<br>" & _
h & "<br>" & sldLne & "<br>" & "<br>" & h2 & "<br>" & sldLne & "<br>" & "<br>" & j & "<br>" & "<br>" & sldLne & "<br>" & "<br>" & k & "<br>" & "<br>" & l & "<br>" & "<br>" & _
"Kindest Regards" & "<br>" & "<br>" & n & "<br>" & _
fName & "<br>" & "<br>" & _
"<P><IMG border=0 hspace=0 alt='' src='file://T:/OurCompany/Logo Media/DMT dispatch@ Email Signature.jpg' align=baseline></P>" & "<br>" & "<br>" & _
"<FONT color=#00008B>" & eDisc & "<br>" & "<FONT color =#00008B>" & eDisc2[/CODE]

Resulting in 2 records on the same line below

[moke123]

Dave

Heres a visual example.

It shows each step individually. (also shows the code on the form)

The final button runs all the steps and writes the results to a table.

(Also note it would be easier for us if you didnt post images of the text with lines through it, but post the text with any sensitive info replaced with other text.)

[DMT Dave]

Hi Moke 123, thank you very much will take a look at your file, thank you

[DMT Dave]

Hi Moke 123, thank you for sending the db example, i have read what you have done, converted slightly to suit my db, i am getting 4 lines when there is a record of 2, I will upload 2 lots of code

I may be doing too many newlines or for some reason the record is duplicate, can you correct me on this please ???, i wont upload full procedures as its quite long, just the parts im trying to adapt from your example to my db

The the below case, there are 2 records

rsBody = rsBody & "Item: " & rs.Fields("ItemType") & " SL No: " & rs.Fields("SONumber") & " Item No: " & rs.Fields("ItemNo") & " Boxes: " & rs.Fields("TotalBox") & " Pieces: " & rs.Fields("TotalPieces")  & vbNewLiners.MoveNext
rs.Sort = "ItemType"
Loop

Me.txtMailBody2.Visible = FalseMe.txtMailBody.Visible = True
Me.txtMailBody = ""
Me.txtMailBody = Replace(rsBody, vbNewLine, "|")
Dim myA As String, myB As String, SplitPipes As String
Dim var As Variant
Dim strOut As String
    If MsgBox("Now View Split ?", vbYesNo) = vbNo Then
    DoCmd.CancelEvent
    Else
    var = Split(rsBody, "|")
    For i = 0 To UBound(var)
        rsBody = rsBody & var(i) & vbNewLine
    Next i
    Me.txtMailBody.Visible = False
    Me.txtMailBody2.Visible = True
    Me.txtMailBody2 = rsBody
    End If

Result: (changed a couple of wordings below but the top2 are the same as bottom 2) this is in txtMailBody2

Item: Thing SL No: SL-123456 Item No: 111111-AH Jones Boxes: 3 : Pieces 3
Item: Thing2 SL No: SL-999999 Item No: 222222-AhJones2 Boxes: 3 Rails: Pieces 4
Item: Thing SL No: SL-123456 Item No: 111111-AH Jones Boxes: 3 : Pieces 3
Item: Thing2 SL No: SL-999999 Item No: 222222-AhJones2 Boxes: 3 Rails: Pieces 4

Thanks again for you help

[DMT Dave]

Just thinking, can i not re query a lstBox and use selected column numbers,

Loop through the lstBox excluding headers and add them to html email body ???

So a String would equal list.count or something similar ?

[moke123]

One thing you may want to try is putting a bunch of Debug.Print statements in your code so you can see what your dealing with.

something like

Debug.Print "X= " & rsBody
   var = Split(rsBody, "|")
    For i = 0 To UBound(var)
Debug.Print "Y= " & var(i)
        rsBody = rsBody & var(i) & vbNewLine
    Next i
Debug.Print "z= " & rsBody
    Me.txtMailBody.Visible = False
    Me.txtMailBody2.Visible = True
    Me.txtMailBody2 = rsBody

[DMT Dave]

Thank you Moke, i am grateful of your example you posted, i will check this out and try again in a while, another method which i thought might be easy is:

requery a list based on criterias , can you give me an example of this ?

Me.lstBody.Requery
1: Count the items
2: make a string for each item vbnewline

Dim lstBody as string

lstbody = list contents

.htmlBody = "<br>" & lstBody

??

[moke123]

I'm a little bit at a loss as I dont have any insight to what your working with. While you can see it I cant.

I think I need to see what is in rsbody. That is the record you're trying to parse and getting 2 sets of results?
Is rsbody in a table? Could you post a Db with that one table with a record or 2? You can change names or any private info in the record.

Just a FYI, sometimes when you post within code tags the text gets screwed up by the procedure that adds the code tags.
like in your post above it changed to

  & vbNewLiners.MoveNext

and also

Me.txtMailBody2.Visible = FalseMe.txtMailBody.Visible = True

Sometimes no matter how many times you try to fix it, it just keeps happening. It's not your fault just wanted to make you aware to double check it.

[orange]

Dave,

Have you considered the "boiler plate" approach suggested by Micron in post #13?
Similar approach to form letters, invoices , shipment info.... There is a pattern of body text with some placeholder/markers that get substituted/replaced based on details of individual recipients. (much like mail merge)

Perhaps you could provide some example body text (same text regardless of individual recipient) and some sample individuals with their details.

There may be some issues where the number of details may change individual to individual, but it seems a route worth investigating.

[moke123]

After writing a long post and refering back to previous posts I think I answered my own questions and now I'm totally lost as far as why you need to parse anything.

Post #19 you posted partial code

rsBody = rsBody & "Item: " & rs.Fields("ItemType") & " SL No: " & rs.Fields("SONumber") & " Item No: " & rs.Fields("ItemNo") & " Boxes: " & rs.Fields("TotalBox") & " Pieces: " & rs.Fields("TotalPieces")  & vbNewLiners.MoveNext
rs.Sort = "ItemType"
Loop

which I assume should produce

Item: Thing SL No: SL-123456 Item No: 111111-AH Jones Boxes: 3 : Pieces 3
Item: Thing2 SL No: SL-999999 Item No: 222222-AhJones2 Boxes: 3 Rails: Pieces 4

I think your right Orange, Micron nailed it in post#13.

[orange]

Yes, I think some placeholder and replacement approach may be appropriate.
I suggested a function from Phil S in post 11 that shows a technique.