HTML Termination

**DMT Dave** · 01-04-2024, 05:44 AM

Hi guy's and happy new year to you all, can you kindly prompt me where i am going wrong here please ?

I have gone through this many times and looked at what i have done previously and still can't get this correct!!!!

The issue i have is the strSign (email signature) is going into the last record inside the HTML table status field within the rs loop, this makes me believe i am not terminating the HTML table correctly but just can't see the issue, i must have looked about 40 times and gone totally bananas now at: </tr> </td> to terminate the HTML table then use "<br>" then strSign

strFS & strFE is a string for font setting
shpDate iweek commencing set as date
TOD is Time Of Day string

Highlighted red is where i thought my problem is ?

Code:

strHTML = "<HTML><Body><table border='2' width='auto'><font color='black' size='3' face='Times New Roman'><tr><th>DelTo</th><th>Town</th><th>PostCode</th><th>Item Type</th>" & _            "<th>MFG</th><th>SL</th><th>Boxes</th><th>Rails</th><th>Status</th></tr>"
        
    strBody = strHTML
    
    strSQL = "Select * From tblEdit WHERE PostCode Like ""*" & strPC & "*"" And ShipmentDate = #" & ShpDate & "# And DelTo = '" & strDelTo & "' ORDER By ItemType, Status DESC"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    strIntro = strFS & TOD & "<br><br>Below is a list for: " & rs.Fields("DelTo") & strFE
    
    Do While Not rs.EOF
    
    Select Case rs.Fields("Status")
    Case Is = "Friday"
        strStatus = "Due In On " & rs.Fields("Status")
    Case Is = "Monday"
        strStatus = "Due In On " & rs.Fields("Status")
    
    Case Is = "Planning"
        
        Select Case rs.Fields("TotalBox")
        Case Is > 0
            strStatus = "At DMT Currently In " & rs.Fields("Status")
        Case Is = 0
            strStatus = "<font color='black' size='3'><i>Waiting Arrival</i></font> Currently In " & rs.Fields("Status")
        End Select
        
    Case Is = "Delivery"
        
        If Not IsNull(DLookup("DeliveryDate", "tblAssign", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")) Then
            DelDate = DLookup("DeliveryDate", "tblAssign", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")
                strDate = "Already Delivered " & Format(DelDate, "ddd-dd-mmm-yyyy")
                strStatus = strDate
        End If
        If IsNull(DLookup("DeliveryDate", "tblAssign", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")) Then
                strDate = "Assigned For Delivery"
                strStatus = strDate
        End If
        
    Case Is = "Collection"
        If Not IsNull(DLookup("CollectedDate", "tblCollections", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")) Then
            DelDate = DLookup("CollectedDate", "tblCollections", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")
                strDate = "Already Collected" & Format(DelDate, "ddd-dd-mmm-yyyy")
                strStatus = strDate
        End If
        If IsNull(DLookup("CollectedDate", "tblCollections", "[PostCode] = '" & myPC & "' And [ShipmentDate] = #" & ShpDate & "# And [DelTo] = '" & strDelTo & "' And [SONumber] = '" & strSL & "'")) Then
                strDate = "Assigned For Collection"
                strStatus = strDate
        End If
    End Select


    strType = rs.Fields("ItemType")
     
    strBody = strBody & "<tr>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & rs.Fields("DelTo") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & rs.Fields("Town") & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & rs.Fields("PostCode") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & strType & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & rs.Fields("ItemNo") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & rs.Fields("SONumber") & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & rs.Fields("TotalBox") & strFE & "</td>" & _
        "<td style='background-color:#F8F8FF'>" & strFS & rs.Fields("TotalRail") & strFE & "</td>" & _
        "<td style='background-color:#F5F5F5'>" & strFS & strStatus & strFE & "</td></tr>" & "|"

    rs.MoveNext
     
    Loop
            
    Set myItem = myApp.CreateItem(olMailItem)
    Set OutAccount = myApp.Session.Accounts.Item(1)
    With myItem
    .subject = mySubj
    .To = "emailaddress"
    .HTMLBody = strFS & strIntro & "<br>" & "<br>" & _
            strAsk & "<br>" & "<br>" & _
            Replace(strBody, "|", "<br>") & "<br>" & _
        strSign
    .SendUsingAccount = OutAccount
    .Display
    End With

strSign is going into last record status field, i can't see why

**Micron** · 01-04-2024, 08:24 AM

Where is the tag that ends your table? I can't see it. Perhaps that is the reason?

</tr> </td> to terminate the HTML table

Uh, no - that would be </table>?

**DMT Dave** · 01-04-2024, 08:44 AM

Hi Micron, thank you, i did look at that but that still isn't adding the records in the HTML table, even though yes it has stopped the signature going into the table

Am i going nut's here ?

Code:

strHTML = "<HTML><Body><table border='2' width='auto'><font color='black' size='3' face='Times New Roman'><tr><th>DelTo</th><th>Town</th><th>PostCode</th><th>Item Type</th>" & _ 
           "<th>MFG</th><th>SL</th><th>Boxes</th><th>Rails</th><th>Status</th></tr></table>"

**Micron** · 01-04-2024, 09:18 AM

that still isn't adding the records in the HTML table,

You didn't say that was a problem. It was, or it is now? You wouldn't terminate the table before building all of it, but it appears that is what you are now doing.

**DMT Dave** · 01-04-2024, 09:25 AM

I missed out the usual method what i use which does the work required, still involves what you said about </table> totally was correct thank you Micron

I normally set strBoxStart and strBoxEnd at the very top of my code so any part of the code can use it which is what i didn't do on this occasion, lapse of concentration and lost in work

Code:

Dim strBoxStart as String, strBoxEnd as String
strBoxStart = "<script><table width='auto';style='text-align:left;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:10px'><tr style='background:white;mso-highlight:blue' ctx.shadowblur;20; ctx.shadowcolor;blue></script>"
strBoxEnd = "</tr></table>"

In fact, i would be better adding to a public function so i can just call strBoxStart and strBoxEnd wherever required and that would stop me forgetting

**Minty** · 01-04-2024, 09:43 AM

If you use the same values everywhere just set them as constants in a global module somewhere?

**Edgar** · 01-04-2024, 09:44 AM

I would suggest to use the HTML reference. That way, you won't need to keep track of opening or closing tags. You also wouldn't need to do string manipulations and your code would be cleaner and easier to read for you and others.

HTML Termination

Thread Tools

HTML Termination

Similar Threads

Display HTML or webpage in HTML report

show a termination this period in the next period's query or report

Filtering out employees with a termination date from a report...

Contract Termination Date Needed

HTML and VBA

Posting Permissions