Can't generate the correct HTML tables

**DMT Dave** · 06-21-2023, 12:27 AM

Hi guy's, i have tried several times with this one, i know in this instance, there are 3 records but can only get 1 record in 1 HTML table, had a look at others and based on previous threads, I should have this loop correct ?

rs5 is always the same collection location
rs2 should generate 3 HTML tables but only 1 record in 1 HTMl table ?

Running the query shows 3 records!!!

I am trying add a table per record...............

Code:

 strFS = "<font size='3' face='Arial' style=text-align=center; vertical-align=middle>" 
strFE = "</font>"
    
Select Case varCollections
    Case Is = 1
    
    iCust = "537"
    
    Set rs5 = CurrentDb.OpenRecordset("Select * From tblDealers WHERE RecordNo = " & iCust)
        sCollName = rs5.Fields("Name")
        sCollAdd1 = rs5.Fields("Add1")
        sCollTown = rs5.Fields("Town")
        sCollPC = rs5.Fields("PostCode")
        
        sCollFrom = sCollName & "|" & _
        sCollAdd1 & vbCrLf & "|" & _
        sCollTown & vbCrLf & "|" & _
        sCollPC


        
        strQRY = "qryCollectionsDue"
            strMSG = "<font size='3' face='Arial' style=text-align=center; vertical-align=middle>" & _
                TOD & "||" & "This is a list of what we have due to be collected.||" & _
                "Please remove the TBC in the table cell 'TBC' and replace with your anticipated collection date.</font>"
    
    Set rs2 = CurrentDb.OpenRecordset(strQRY)


    Do While Not rs2.EOF
   
        strHTML = "<HTML><head><style>" & _
                     "table{border-collapse:collapse; width: 35%} td,th{text-align:left}  table,td,th{border: 2px solid blue;}" & _
                    "</style></head><Body><table>" & _
             strFS & "<tr><th>Week Commencing:</th><td>" & Format(DateAdd("d", 3, rs2.Fields("ShipmentDate")), "ddd-dd-mmm-yyyy") & "</td></tr>" & _
                     "<tr><th>Customer:</th><td>" & rs2.Fields("Customer") & "</td></tr>" & _
                     "<tr><th>Collection From:</th><td>" & Replace(sCollFrom, "|", "<br>") & "</td></tr>" & _
                     "<tr><th>Lift Type:</th><td>" & rs2.Fields("LiftType") & "</td></tr>" & _
                     "<tr><th>Lift Details:</th><td>" & rs2.Fields("LiftNo") & "</td></tr>" & _
                     "<tr><th>Collection Date:</th><td>" & "TBC" & "</td></tr>" & strFE & _
                    "</table>"
    
        strBody = strHTML
    
        Debug.Print strBody
        
    rs2.MoveNext
    Loop

   Set oOutlook = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set oOutlook = New Outlook.Application
    End If


    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
    Set OutAccount = oEmailItem.Session.Accounts.Item(1)
    .To = "removed"
    .CC = "removed; removed"
    .subject = "Collections Due"
    .HTMLBody = Replace(strMSG, "|", "<br>") & "<br>" & strBoxStart & Replace(strBody, "|", "<br>") & strBoxEnd & "<br>" & _
        strUserSign & "<br>" & "<br>" & _
        "<P><IMG border=0 hspace=0 alt='' src='file://T:/DMT Ltd/Logo Media/" & SigFile & "' align=baseline></P>" & "<br>" & "<br>" & _
        "<FONT color=#00008B>" & eDisc & "<br>" & "<FONT color =#00008B>" & eDisc2
    .SendUsingAccount = OutAccount
    .Display
    End With
    
End Select

**Welshgasman** · 06-21-2023, 03:05 AM

That is beacuse you are overwriting strBody each time?

I would also expect the HEAD code to be in there once, not for every record?

Your Debug.Print should have shown you that?

**DMT Dave** · 06-21-2023, 04:12 AM

Hi WGM, thank you for finding what i still can't, please correct me if i am wrong, in fact i know i am wrong because it doesn't work

So my interpretation of this section is:

1: Open the recordset with (in this instance x 3 records

Code:

Set rs2 = CurrentDb.OpenRecordset(strQRY)

2: Tell the recordset to start a loop while it's not at the end of file

Code:

Do While Not rs2.EOF

3: set the table and data from the recordset

Code:

strHTML = "<HTML><head><style>" & _                     "table{border-collapse:collapse; width: 35%} td,th{text-align:left}  table,td,th{border: 2px solid blue;}" & _
                    "</style></head><Body><table>" & _
             strFS & "<tr><th>Week Commencing:</th><td>" & Format(DateAdd("d", 3, rs2.Fields("ShipmentDate")), "ddd-dd-mmm-yyyy") & "</td></tr>" & _
                     "<tr><th>Customer:</th><td>" & rs2.Fields("Customer") & "</td></tr>" & _
                     "<tr><th>Collection From:</th><td>" & Replace(sCollFrom, "|", "<br>") & "</td></tr>" & _
                     "<tr><th>Lift Type:</th><td>" & rs2.Fields("LiftType") & "</td></tr>" & _
                     "<tr><th>Lift Details:</th><td>" & rs2.Fields("LiftNo") & "</td></tr>" & _
                     "<tr><th>Collection Date:</th><td>" & "TBC" & "</td></tr>" & strF "</table>"

4: Tell the email body it should be x 3 records (or recordset count) but 3 in this instance

Code:

strBody = strHTML

5: move through the records

Code:

rs2.MoveNext

6: No record breaks / Continue for all records

Code:

Loop

Based on your comment, That is beacuse you are overwriting strBody each time?

i moved the strBody = strHTML after the loop, still 1 record out of 3

Debug is showing 3 html collect from taken from rs5

Code:

 "<tr><th>Collection From:</th><td>" & Replace(sCollFrom, "|", "<br>") & "</td></tr>" & _

email is showing 1 table with 1 record

I am certainly nuts because I must have incorrect interpretation of this!!!!!

**Welshgasman** · 06-21-2023, 04:26 AM

Think about it.
You are getting data from a record.
You are putting into html
You are putting that as the message.

You are getting the next record
Your are putting it into html
You are putting that as the message.

You need to be concatenating the html to the msgbody, not replacing it each time

, otherwise you will end up just with data from the last record?

Also I believe the header should be inserted only once, given it's name, same with any footer.

So if I was doing it, I would build the body of the message, then prefix the header to the html and add any footer.

Think about the steps you need to take.

**DMT Dave** · 06-21-2023, 08:20 AM

Hi WGM, all i needed to do was change

strHTML = "<HTML><head><style>" & _To
strHTML = strHTML & "<HTML><head><style>" & _

**Welshgasman** · 06-21-2023, 08:46 AM

Originally Posted by DMT Dave

Hi WGM, all i needed to do was change

strHTML = "<HTML><head><style>" & _To
strHTML = strHTML & "<HTML><head><style>" & _

Well I mentioned that, twice.

However I would have coded it as strBody = strBody & strHtml, but the end result is the same.

You have played around with html more than I have, but I thought Head was just that a header.

**DMT Dave** · 06-21-2023, 10:43 AM

Yes you did

Thank you as always though for suggesitons

Can't generate the correct HTML tables

Thread Tools

Can't generate the correct HTML tables

Similar Threads

Unable to generate correct report from query based on join properties

generate html file based on access data

Correct Query to generate a form

Parsing HTML Tables for Storage in Access

Web Query - How to take information from sites not in HTML tables

Posting Permissions