Hi Guy's, I have had a scan through some code from previous posts to try and find a method of changing text colour of text If Status Field = "On Hold", tried Replace function then adding <mark> strStatus </mark>, after thought was even if I could get this to work,this will just highlight whatever the status field says! so would be no use
Is there an option do change it in vba instead as i think that would be the answer i am looking for as I only want to change text (highlight) if Status is "on hold" see where i have written >> (CAN I CHANGE THIS UCASE TEXT YELLOW)
Code:
Dim dtShipDate as Date
Dim strStatus as String, strCustString as String, strPC as String, strSubj as String, strBody as String
Dim strA as String,strB as String, strC as String
Dim strKR as String, strBoxStart as String, strBoxEnd as String, strFontStart as String, strFontEnd as String
Dim rs As DAO.Recordset
Dim myApp As New Outlook.Application, myItem As Outlook.MailItem
strBoxStart = "<table style='text-align:left;border:2px solid black;font-family:calibri;border-collapse:collapse;padding:25px'><tr style='background:white;mso-highlight:blue'>"
strBoxEnd = "</tr></table>"
strFontStart = "<font size='3'>"
strFontEnd = "</font>"
strCustString = Left(DLookup("DelTo", "tblDealers", "[DelTo] Like ""*" & Me.DelTo & "*"""), 5)strPC = Me.PostCode
dtShipDate = Format(Me.ShipmentDate, "mm/dd/yyyy")
strSubj = Me.DelTo & " On Hold"
strKR = "With Kind Regards"
Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo Like ""*" & strCustString & "*"" And ShipmentDate = #" & dtShipDate & "#")
Do Until rs.EOF
If rs.Fields("Status") = "Friday" Then
strStatus = "Due In " & rs.Fields("Status")
End If
If rs.Fields("Status") = "Monday" Then
strStatus = "Due In " & rs.Fields("Status")
End If
If rs.Fields("Status") = "On Hold" Then
strStatus = "At DMT Currently " & UCase(rs.Fields("Status")) (CAN I CHANGE THS UCASE TEXT YELLOW)
End If
If rs.Fields("Status") = "Planning" Then
strStatus = "Currently In Shipment " & rs.Fields("Status")
End If
strBody = strBody & rs.Fields("DelTo") & " - " & UCase(rs.Fields("Town")) & " - " & rs.Fields("PostCode") & _
" - " & rs.Fields("LiftType") & " - " & UCase(rs.Fields("SONumber")) & " - " & rs.Fields("LiftNo") & " - " & strStatus & "|"
rs.MoveNext
Loop
strA = "hope you are safe and well."
strB = "Would you be able to update us if the highlighted can be delivered ?"
strC = strBody
strKR = "With Kind Regards"
Set myItem = myApp.CreateItem(olMailItem)
With myItem
.subject = strSubj
.To = ""
.HTMLBody = strFontStart & strA & "<br>" & "<br>" & strB & "<br>" & "<br>" & strBoxStart & "<br>" & _
Replace(Replace(strC, "|", "<br>" & "<br>"), strStatus, "<mark>" & strStatus & "</mark>") & strBoxEnd & "<br>" & "<br>" & _
strKR & "<br>" & strFontEnd & "<br>" & "<br>" & _
"<P><IMG border=0 hspace=0 alt='' src='file://T:/DMT Ltd/Logo Media/" & "Mail Signature.jpg" & "' align=baseline></P>"
.ReadReceiptRequested = True
.Display
End With