Hiding HTMLBody table rows if values are 0

TecknoFreak · Mar 7, 2022

Hello guys,

So I created a Report that generates an HTML table and send on Outlook while populating values from a Form.
My question is; Is it possible to hide a Row if a Value on any Column is 0?

HTML:

'Generate email here

With oEmail
          .Display
          .To = "jeff.1@gmail.com; jeff.2@gmail.com; jeff.3@gmail.com; jeff.4@gmail.com"
          .CC = "jeff.5@gmail.com; jeff.6@gmail.com; jeff.7@gmail.com; jeff.8@gmail.com"
          .Subject = "Summary Report (Week Ending) " & DateAdd("d", 1 - Weekday(Date, 6), Date)
          .HTMLBody = "<HTML> <head> Please refer to the attachment name to see the corresponding Report. <br><br>" _

                    & "<font color=""red""><B>Notes:</B></font>" & "<br>" _
                    & "<U>Jeff 1</U>" & " reports attached." & "<br>" _
                    & "<U>Jeff 2</U>" & " reports below. " & "<br><br><br>" _
                    & "<style>table, th, td {border: 2px solid; border-collapse: collapse; table-layout: auto; width: 200px;}" & "</style></head> <BODY >" _
                    & "<table style=""width:fixed;"">" _
                & "<tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #E8F5E9 ;""><b> IIMS Report </b></td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">TD Edges</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.WSToEd.Value & " </td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">BA Edges (RTVS)</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.WSToBA.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">CAB Edges (RTVS)</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.WSToCBA.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">Sys Installs</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.WSToSI.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">BK-9 Material</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.TBK4.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">CRA/MICIP's </th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IIMSnewWeeklyReport.TCRO.Value & "</td></tr></table><br><br>" _
                & "<table width=""fixed""><tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #FFEFD5;""><b> IRMS Report</b></td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">TSC Edges </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IRMS_newWeeklySummary.WSToPT.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">TSC Wedges</th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IRMS_newWeeklySummary.WSToWe.Value & "</td></tr>" _
                    & "<tr><th style=""text-align: left; font-weight:normal;"">CRA/MICIP's  </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IRMS_newWeeklySummary.TCoated.Value & "</td></tr>" _
                    & "</table> </BODY></HTML><br><br><br>" _
                & "<I>Report Generated by " & Environ("UserName") & " using the <a href=""" & "C:\****************""" & "> MS Access Database</a> created by A.Solis.</I> <br><br>"

                  .Attachments.Add strAttach1
                  .Attachments.Add strAttach2

End With

Minty · Mar 7, 2022

Can't you do that in the underlying query that gathers the data for this? e.g. not pass the data into the process in the first place?

theDBguy · Mar 7, 2022

I agree. Although it's possible to hide something, it's much easier to just leave them out in the first place, so nothing needs to hide.

plog · Mar 7, 2022

Yes, its real simple actually, which makes me question your question. Instead of assigning a big long string to .HTMLBody do it line by line, then when it comes time to add the data, test it for 0 and add it if it isn't:

Code:

.HTMLBody = "<HTML> <head> Please refer to the attachment name to see the corresponding Report. <br><br>"
.HTMLBody = .HTMLBody & "<font color=""red""><B>Notes:</B></font>" & "<br>"
.HTMLBody = .HTMLBody & "<U>Jeff 1</U>" & " reports attached." & "<br>" _
...
...
...
if (VariableValue1 <> 0) Then .HTMLBody = .HTMLBody & "<tr><th>Header Code1</th><td>" & VariableValue1 & "</td></tr>"
if (VariableValue2 <> 0) Then .HTMLBody = .HTMLBody & "<tr><th>Header Code2</th><td>" & VariableValue2 & </td></tr>"
...
...

Hiding HTMLBody table rows if values are 0

TecknoFreak

Member

Minty

AWF VIP

theDBguy

I’m here to help

plog

Banishment Pending

Similar threads

Users who are viewing this thread