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