Solved Email .HTMLBody to include totals from other Modules or Queries? (2 Viewers)

Gasman

Enthusiastic Amateur
Local time
Today, 01:28
Joined
Sep 21, 2011
Messages
14,048
If you create an outlook template, there is not a lot to change?
You could have the .to, .subject all set in the template?
All you would literally do is replace each placemarker with one of those values IN CODE!!! and just send the email?

You were creating the outlook email from access in the first place? Access does not create emails, it always uses another program.
That link I posted shows you how to create a new email from a saved template.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:28
Joined
Sep 21, 2011
Messages
14,048
Here is how I did it in one of my emails.
However I did not bother with fancy lines/boxes. I have always been more concerned on function rather than cosmetics.

Code:
    ' Set up HTML tags
    strPad = "<tr><td>"
    strEndPad = "</td></tr>"
    strPadCol = "</td><td>"
    strBlankLine = "<tr></tr>"


            ' Now add the variable data
            With objOutlookMsg
                .HTMLBody = .HTMLBody & strPad & str3rdPartyType & strPadCol & str3rdParty & strEndPad
                .HTMLBody = .HTMLBody & strPad & strDatetype & strPadCol & strDate & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Method:" & strPadCol & strMethod & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Reference:" & strPadCol & strRef & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Amount:" & strPadCol & strAmount & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Balance:" & strPadCol & strBalance & strEndPad
                ' Add any notes if they exist
                If Len(strNotes) > 0 Then
                    .HTMLBody = .HTMLBody & strPad & "Notes:" & strPadCol & strNotes & strEndPad

                End If
'                ' Add blank line for next set
                .HTMLBody = .HTMLBody & strBlankLine & strBlankLine
            End With

That produced an email like below

1641032092207.png
 

TecknoFreak

Member
Local time
Yesterday, 18:28
Joined
Dec 21, 2021
Messages
57
Here is how I did it in one of my emails.
However I did not bother with fancy lines/boxes. I have always been more concerned on function rather than cosmetics.

Code:
    ' Set up HTML tags
    strPad = "<tr><td>"
    strEndPad = "</td></tr>"
    strPadCol = "</td><td>"
    strBlankLine = "<tr></tr>"


            ' Now add the variable data
            With objOutlookMsg
                .HTMLBody = .HTMLBody & strPad & str3rdPartyType & strPadCol & str3rdParty & strEndPad
                .HTMLBody = .HTMLBody & strPad & strDatetype & strPadCol & strDate & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Method:" & strPadCol & strMethod & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Reference:" & strPadCol & strRef & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Amount:" & strPadCol & strAmount & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Balance:" & strPadCol & strBalance & strEndPad
                ' Add any notes if they exist
                If Len(strNotes) > 0 Then
                    .HTMLBody = .HTMLBody & strPad & "Notes:" & strPadCol & strNotes & strEndPad

                End If
'                ' Add blank line for next set
                .HTMLBody = .HTMLBody & strBlankLine & strBlankLine
            End With

That produced an email like below

View attachment 97255
I will give it a shot. Looks amazing 👏
I will post my final results here when done

Thanks so much mate ❤
 

TecknoFreak

Member
Local time
Yesterday, 18:28
Joined
Dec 21, 2021
Messages
57
OK, thanks to all the people that helped me here and also all the people in this forum to keep so much valuable information for others to reach and learn from it I was able to figure out the html table in the email report. I will share my results below

The body looks like this

1641061966694.png


and here is the code I was able to figured out that worked perfectly for me.

HTML:
.HTMLBody = "<HTML> <head> Please refer to the attachment name to see the corresponding Department Report. <br><br>" _
                    & "<font color=""red""><B>Notes:</B></font>" & "<br>" _
                    & "<U>Joe Sandoval</U>" & " reports attached." & "<br>" _
                    & "<U>Jeff Stones</U>" & " reports below. " & "<br><br><br>" _
                    & "<style>table, th, td {border: 2px solid ; border-collapse:" _
                        & "collapse;}</style></head> <BODY >" _
                    & "<table style=""width:30%;"">" _
                   & "<tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #E8F5E9 ;""><b> IAAIMS Report </b></td></tr>" _
                   & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of RF Edges</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.WSToEd.Value & " </td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Sys Installs</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.WSToSI.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of BK-4 Material</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.TBK4.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of CRO/MICAP's </th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.TCRO.Value & "</td></tr></table><br><br>" _
                & "<table width=""30%""><tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #FFEFD5;""><b> IARIMS Report</b></td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Edges </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.WSToPT.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Wedges</th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.WSToWe.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Coated </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.TCoated.Value & "</td></tr>" _
                & "</table> </BODY></HTML><br><br><br>" _
                & "<I>Report Generated by " & Environ("UserName") & " using the Ranges MS Access Database created by Angel S.</I> <br><br>"

                  .Attachments.Add strAttach1
                  .Attachments.Add strAttach2

I hope this piece of code will help someone in the long run.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:28
Joined
Oct 29, 2018
Messages
21,358
OK, thanks to all the people that helped me here and also all the people in this forum to keep so much valuable information for others to reach and learn from it I was able to figure out the html table in the email report. I will share my results below

The body looks like this

View attachment 97257

and here is the code I was able to figured out that worked perfectly for me.

HTML:
.HTMLBody = "<HTML> <head> Please refer to the attachment name to see the corresponding Range Report. <br><br>" _
                    & "<font color=""red""><B>Notes:</B></font>" & "<br>" _
                    & "<U>Joe Rugg</U>" & " reports attached." & "<br>" _
                    & "<U>Jeff Schae</U>" & " reports below. " & "<br><br><br>" _
                    & "<style>table, th, td {border: 2px solid ; border-collapse:" _
                        & "collapse;}</style></head> <BODY >" _
                    & "<table style=""width:30%;"">" _
                   & "<tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #E8F5E9 ;""><b> IAAIMS Report </b></td></tr>" _
                   & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of RF Edges</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.WSToEd.Value & " </td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Sys Installs</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.WSToSI.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of BK-4 Material</th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.TBK4.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of CRO/MICAP's </th><td style=""text-align: center; width: 30%; background-color: #E8F5E9"">" & Forms!F_IAAIMSWeeklyReport.TCRO.Value & "</td></tr></table><br><br>" _
                & "<table width=""30%""><tr><td colspan=""2"" style=""text-align: center; color: black; background-color: #FFEFD5;""><b> IARIMS Report</b></td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Edges </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.WSToPT.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Wedges</th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.WSToWe.Value & "</td></tr>" _
                & "<tr><th style=""text-align: left; font-weight:normal;"">Total QTY of Coated </th><td style=""text-align: center; width: 30%; background-color: #FFEFD5;"">" & Forms!F_IARIMS_WeeklySummary.TCoated.Value & "</td></tr>" _
                & "</table> </BODY></HTML><br><br><br>" _
                & "<I>Report Generated by " & Environ("UserName") & " using the Ranges MS Access Database created by Angel S.</I> <br><br>"

                  .Attachments.Add strAttach1
                  .Attachments.Add strAttach2

I hope this piece of code will help someone in the long run.
Hi. Thanks for sharing. Good luck with your project.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:28
Joined
Sep 21, 2011
Messages
14,048
That code does not appear to match the output?, but I get the idea. :)

Well done for persevering. (y)
 

TecknoFreak

Member
Local time
Yesterday, 18:28
Joined
Dec 21, 2021
Messages
57
That code does not appear to match the output?, but I get the idea. :)

Well done for persevering. (y)
Yeah is a bit of more info in the code. The goal was the table so thats why I just screenshot the table and not the entire email sorry. 😬
 

Users who are viewing this thread

Top Bottom