Solved Email .HTMLBody to include totals from other Modules or Queries?

[TecknoFreak]

By that, I mean, I was hoping you would tell me you double-click on the form's name on the Navigation Pane, or you click on a button from your menu form, etc.

I created a form based on a report as I rather show the form and not the report to the rest of the db users.
But all is populated based on a Report that is populated as Closed using a query for each of the 2 Reports. The Report just Open as hidden to populate the Report data and then Closed once populated to convert to PDF before sending as attachment in an email.

The trigger to those Reports comes from 2 different VBA Class/Modules Objects

For IAAIMS Report

Private Sub Report_Load()
    GetData
End Sub


Sub GetData()
    Me.TMechLEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='MECH / LEF'"), 0)
    Me.TMechILEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='MECH / ILEF'"), 0)
    Me.TSysInstLEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / LEF'"), 0)
    Me.TSysInstILEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / ILEF'"), 0)
    Me.TSysInstAIL = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / AIL'"), 0)
    Me.TBonAssWTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / WTTE'"), 0)
    Me.TBonAssHTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / HTTE'"), 0)
    Me.TBonAssAIL = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / AIL'"), 0)
    Me.TApertBAND34 = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='APERTURE - 34'"), 0)
    Me.TApertBAND2 = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='APERTURE - LB'"), 0)
    Me.TBK4 = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='MATERIAL BK4'"), 0)
   
    FailMechLEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='MECH / LEF'" & "and [PassFail]='Fail'"), 0)
    FailMechILEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='MECH / ILEF'" & "and [PassFail]='Fail'"), 0)
    FailSysInstLEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / LEF'" & "and [PassFail]='Fail'"), 0)
    FailSysInstILEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / ILEF'" & "and [PassFail]='Fail'"), 0)
    FailSysInstAIL = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='SYS INS / AIL'" & "and [PassFail]='Fail'"), 0)
    FailBonAssWTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / WTTE'" & "and [PassFail]='Fail'"), 0)
    FailBonAssHTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / HTTE'" & "and [PassFail]='Fail'"), 0)
    FailBonAssAIL = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='BA / AIL'" & "and [PassFail]='Fail'"), 0)
    FailApertBAND34 = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='APERTURE - 34'" & "and [PassFail]='Fail'"), 0)
    FailApertBAND2 = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='APERTURE - LB'" & "and [PassFail]='Fail'"), 0)
   
    Me.CMechLEF = Me.TMechLEF - FailMechLEF
    Me.CMechILEF = Me.TMechILEF - FailMechILEF
    Me.CSysInstLEF = Me.TSysInstLEF - FailSysInstLEF
    Me.CSysInstILEF = Me.TSysInstILEF - FailSysInstILEF
    Me.CSysInstAIL = Me.TSysInstAIL - FailSysInstAIL
    Me.CBonAssWTTE = Me.TBonAssWTTE - FailBonAssWTTE
    Me.CBonAssHTTE = Me.TBonAssHTTE - FailBonAssHTTE
    Me.CBonAssAIL = Me.TBonAssAIL - FailBonAssAIL
    Me.CApertBAND34 = Me.TApertBAND34 - FailApertBAND34
    Me.CApertBAND2 = Me.TApertBAND2 - FailApertBAND2
   
    Me.WSToEd = Me.TMechLEF + Me.TMechILEF + Me.TBonAssWTTE + Me.TBonAssHTTE + Me.TBonAssAIL
    Me.WSToSI = Me.TSysInstLEF + Me.TSysInstILEF + Me.TSysInstAIL
End Sub

For IARIMS Report

Private Sub Report_Load()
    GetData
End Sub

Sub GetData()
    Me.TMechLEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='MECH / LEF'"), 0)
    Me.TMechILEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='MECH / ILEF'"), 0)
    Me.TBonAssWTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='BA / WTTE'"), 0)
    Me.TBonAssHTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='BA / HTTE'"), 0)
    Me.TBonAssAIL = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='BA / AIL'"), 0)
    Me.TCoreLEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='LEF CoreBond'"), 0)
    Me.TCoreILEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='ILEF CoreBond'"), 0)
    Me.TStriRADO = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='RADOME - Mid/Lam'"), 0) + Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='RADOME - Full Up'"), 0) + Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='RADOME - Stripped'"), 0)
    Me.TStriFLAP = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='FLAP - Coated'"), 0)
   
    Me.WTLEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='LEF Wedge'" & "and [PassFail]='Pass'"), 0)
    Me.WTILEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='ILEF Wedge'" & "and [PassFail]='Pass'"), 0)
    Me.WTWTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='WTTE Wedge'" & "and [PassFail]='Pass'"), 0)
    Me.WTAILE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='AIL Wedge'" & "and [PassFail]='Pass'"), 0)
    Me.WTHTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='HTTE Wedge'" & "and [PassFail]='Pass'"), 0)
   
    Me.WCLEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='LEF Wedge'" & "and [PassFail]='Fail'"), 0)
    Me.WCILEF = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='ILEF Wedge'" & "and [PassFail]='Fail'"), 0)
    Me.WCWTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='WTTE Wedge'" & "and [PassFail]='Fail'"), 0)
    Me.WCAILE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='AIL Wedge'" & "and [PassFail]='Fail'"), 0)
    Me.WCHTTE = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Wedges]='HTTE Wedge'" & "and [PassFail]='Fail'"), 0)

    Me.WSToPT = [TMechLEF] + [TMechILEF] + [TBonAssWTTE] + [TBonAssAIL] + [TBonAssHTTE] + [TCoreLEF] + [TCoreILEF] + [TStriRADO] + [TStriFLAP]
    Me.WSToWe = [WTLEF] + [WTILEF] + [WTWTTE] + [WTAILE] + [WTHTTE] + [WCLEF] + [WCILEF] + [WCWTTE] + [WCAILE] + [WCHTTE]
End Sub

Then those 2 Reports are loaded based on EmailReport_Click () from a button located in Dashboard.

DoCmd.OpenReport "R_IAAIMS_Mailing_Summary", acViewReport, , , acHidden
DoCmd.OutputTo acOutputReport, "R_IAAIMS_Mailing_Summary", acFormatPDF, "L:\Labs\LOGBOOK_DATABASE\Reports\IAAIMS Reports\IAAIMS Weekly Report.pdf", False
DoCmd.Close acReport, "R_IAAIMS_Mailing_Summary", acSaveNo

DoCmd.OpenReport "R_IARIMS_Mailing_Summary", acViewReport, , , acHidden
DoCmd.OutputTo acOutputReport, "R_IARIMS_Mailing_Summary", acFormatPDF, "L:\Labs\LOGBOOK_DATABASE\Reports\IARIMS Reports\IARIMS Weekly Report.pdf", False
DoCmd.Close acReport, "R_IARIMS_Mailing_Summary", acSaveNo

 

[TecknoFreak]

Im sooo sorry. Im so new to this that I dont even know how to explain my problem correctly

 

[TecknoFreak]

@theDBguy With your working code you provided me earlier as
& "Total QTY of Edges: <U>" & Forms!F_IAAIMSWeeklyReport.WSToEd.Value & "</U><br>" _

Is there a way to do this?

DoCmd.OpenForm "F_IAAIMSWeeklyReport", acNormal, , , acHidden
?????????????
DoCmd.Close acForm "F_IAAIMSWeeklyReport", acSaveNo

This is how the HTMLBody looks like

.HTMLBody = "Please refer to the attachment name to see the corresponding Range Report. <br><br>" _
                    & "<B>Notes:</B>" & "<br>" _
                    & "<U>Joe R.</U>" & " reports attached." & "<br>" _
                    & "<U>Jeff S.</U>" & " report below. " & "<br><br>" _
                    & "<B><U>IAAIMS Report</B></U><br>" _
                    & "Total QTY of Edges: <U>" & Forms!F_IAAIMSWeeklyReport.WSToEd.Value & "</U><br>" _
                    & "Total QTY of Sys Installs: <U>" & Forms!F_IAAIMSWeeklyReport.WSToSI.Value & "</U><br>" _
                    & "Total QTY of BK-4 Material: <U>" & Forms!F_IAAIMSWeeklyReport.TBK4.Value & "</U><br>" _
                    & "Total QTY CRO/MICAP's: <U>" & Forms!F_IAAIMSWeeklyReport.WSToCRO.Value & "</U><br><br><br>" _
                    & "<B><U>IARIMS Report</B></U><br>" _
                    & "Total QTY of Edges: <U>" & Forms!F_IARIMS_WeeklySummary.WSToPT.Value & "</U><br>" _
                    & "Total QTY of Wedges: <U>" & Forms!F_IARIMS_WeeklySummary.WSToWe.Value & "</U><br>" _
                    & "Total QTY CRO/MICAP's : <U>" & Forms!F_IARIMS_WeeklySummary.TCoated.Value & "</U><br><br><br>" _
                    & "<I>Report Generated by " & Environ("UserName") & " using the Ranges MS Access Database created by Angel L.</I>"

 

[theDBguy]

Is there a way to do this?

Sorry, do "what" exactly?

 

[TecknoFreak]

Sorry, do "what" exactly?

Was wondering if there is a way to DoCmd.OpenForm and DoCmd.Close acForm after getting values for the email body show previous post

 

[theDBguy]

Was wondering if there is a way to DoCmd.OpenForm and DoCmd.Close acForm after getting values for the email body show previous post

Right. You do it exactly like you showed there. Namely,

OpenForm
Generate Email
Close Form

 

[TecknoFreak]

Wow thank you @theDBguy , @Gasman for all the ideas. I finely found a very easy solution and that was making a Macro as below. (Thanks to theDBGuy for the code)
Now the email populates perfectly. Cant believe I could go this route. in Access.

 

[theDBguy]

Wow thank you @theDBguy , @Gasman for all the ideas. I finely found a very easy solution and that was making a Macro as below. (Thanks to theDBGuy for the code)
Now the email populates perfectly. Cant believe I could go this route. in Access.

View attachment 97225

Yay! Congratulations! Keep up the good work!

 

[TecknoFreak]

Yay! Congratulations! Keep up the good work!

No thank you. You saved me from 2 problems I had. Amazing community. Will do the best to also be part of it and help as I can same way you guys came to help me.

 

[TecknoFreak]

Okay, I just read through the posts. Are those two separate procedures populating an open form? If so, you should be able to refer to their values from your code by using a form reference. For example:

& "Total QTY of Edges: <U>" & Forms!FormName.WSToEd.Value & "</U><br>" _

Hope that helps...

Hello mate,
One more question

Based on that amazing code you created for me I was able to stylish the email body just a bit and looks amazing.
"Total QTY of RF Edges: <U><font color=""green"">" & Forms!F_IAAIMSWeeklyReport.WSToEd.Value & "</font></U><br>" _

I was wondering if in VBA there is a way to space out the values from text using dashes so is more eye catching. Made a lil sketch to how it is now and how I will like to look like.
So far I have tried so many options and none have worked

 

[theDBguy]

Since you are using HTML, it might look better to use a <TABLE> tag. But if you want to pursue your idea, take a look at the String() function.

 

[TecknoFreak]

Since you are using HTML, it might look better to use a <TABLE> tag. But if you want to pursue your idea, take a look at the String() function.

Is there an easy way to convert an html file into VBA?

I made a table in Outlook the way I want it to be when sending the report and saved the email as HTML . Can I use that format and apply that table in VBA? Not sure if is my language barrier but man my searches seen not to find any of my problems.

 

[Gasman]

You could set it up as a template with placeholders, then replace those with your values?

 

[TecknoFreak]

You could set it up as a template with placeholders, then replace those with your values?

Yeah I did read some like that but this language is way too advance for me. I dont know how to do that. I just read that is possible doing it this way

 

[Gasman]

Just google

2 Quick Methods to Create New Emails from a Template with Outlook VBA

Since that many users think it cumbersome to find and use Outlook email templates, we will exhibit 2 quick methods to create new emails using a template with Outlook VBA in this article.

www.datanumen.com

Then a simple replace for each value ?

 

[TecknoFreak]

Just google

2 Quick Methods to Create New Emails from a Template with Outlook VBA

Since that many users think it cumbersome to find and use Outlook email templates, we will exhibit 2 quick methods to create new emails using a template with Outlook VBA in this article.

www.datanumen.com

Then a simple replace for each value ?

There is no way I can use that template been made from Access msg = htmlbody.



I was able to create and convert the email into a Outlook template but there is no way I will be able to add access values mate.



What Im trying to do here is convert this below into a nice looking table like the one I did in Outlook

Private Sub EmailReport_Click()

Dim strPdfFile As String

Dim oApp As Object

Dim oEmail As Variant

Dim strAttach1 As String

Dim strAttach2 As String

Dim strAttach3 As String



    Set oApp = CreateObject("Outlook.application")

    Set oEmail = oApp.CreateItem(0)



'Output Reports

Rem <!-- Each Range Report need to match the report names to export. & _

IMPORTANT: If the DB change locations/paths make sure to change them here too, MS Access will & _

not do this for you. -->

DoCmd.OpenReport "R_610_Mailing_Summary", acViewReport, , , acHidden

DoCmd.OutputTo acOutputReport, "R_610_Mailing_Summary", acFormatPDF, "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\610 Reports\610 Range Weekly Report.pdf", False

DoCmd.Close acReport, "R_610_Mailing_Summary", acSaveNo



DoCmd.OpenReport "R_IAAIMS_Mailing_Summary", acViewReport, , , acHidden

DoCmd.OutputTo acOutputReport, "R_IAAIMS_Mailing_Summary", acFormatPDF, "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\IAAIMS Reports\IAAIMS RF Range Weekly Report.pdf", False

DoCmd.Close acReport, "R_IAAIMS_Mailing_Summary", acSaveNo



DoCmd.OpenReport "R_IARIMS_Mailing_Summary", acViewReport, , , acHidden

DoCmd.OutputTo acOutputReport, "R_IARIMS_Mailing_Summary", acFormatPDF, "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\IARIMS Reports\IARIMS RCS Range Weekly Report.pdf", False

DoCmd.Close acReport, "R_IARIMS_Mailing_Summary", acSaveNo



'Set Reports as Attachments

Rem <!-- make sure to correlate the attachments to each of the reports you wish to send -->

strAttach1 = "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\IAAIMS Reports\IAAIMS RF Range Weekly Report.pdf"

strAttach2 = "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\IARIMS Reports\IARIMS RCS Range Weekly Report.pdf"

strAttach3 = "P:\Eng\Labs & Ranges\LOGBOOK_DATABASE\Reports\610 Reports\610 Range Weekly Report.pdf"  ' ------------ (Not Emailing this Report but Generating)



'Generate email here

With oEmail



          .Display

          .To = "; john.o.smitty@gmail.com"

          .CC = "tom.h.aguello@gmail.com; "

          .Subject = "Ranges Summary Report (Week Ending) " & DateAdd("d", 1 - Weekday(Date, 6), Date)

          .HTMLBody = "Please refer to the attachment name to see the corresponding Range Report. <br><br>" _

                    & "<font color=""red""><B>Notes:</B></font>" & "<br>" _

                    & "<U>John Smitty</U>" & " reports attached." & "<br>" _

                    & "<U>Tom Aguello</U>" & " reports below. " & "<br><br><br>" _

                    & "<B><U><font color=""green""><li>IIMS Report</li></font></B></U><br>" _

                    & "Total QTY of RF Edges: <U><font color=""green"">" & Forms!F_IIMSWeeklyReport.WSToEd.Value & "</font></U><br>" _

                    & "Total QTY of Sys Installs: <U><font color=""green"">" & Forms!F_IIMSWeeklyReport.WSToSI.Value & "</font></U><br>" _

                    & "Total QTY of BK-4 Material: <U><font color=""green"">" & Forms!F_IIMSWeeklyReport.TBK4.Value & "</font></U><br>" _

                    & "Total QTY of CRO/MICAP's: <U><font color=""green"">" & Forms!F_IIMSWeeklyReport.TCRO.Value & "</font></U><br><br>" _

                    & "<B><U><font color=""orange""><li>IRIMS Report</li></font></B></U><br>" _

                    & "Total QTY of Edges: <U><font color=""orange"">" & Forms!F_IRIMS_WeeklySummary.WSToPT.Value & "</font></U><br>" _

                    & "Total QTY of Wedges: <U><font color=""orange"">" & Forms!F_IRIMS_WeeklySummary.WSToWe.Value & "</font></U><br>" _

                    & "Total QTY of Coated: <U><font color=""orange"">" & Forms!F_IRIMS_WeeklySummary.TCoated.Value & "</font></U><br><br><br>" _

                    & "<I>Report Generated by " & Environ("UserName") & " using the Ranges MS Access Database created by Angel Solis.</I>"

          .Attachments.Add strAttach1

          .Attachments.Add strAttach2

         



End With

End Sub

I dont think your solution will work for me

 

[Gasman]

Why not?, you create an email in your code, you do the same, but from a template?
I do not see the difference?

 

[TecknoFreak]

Why not?, you create an email in your code, you do the same, but from a template?
I do not see the difference?

What I don't get is how I create the table template on Outlook and can use it on Access so I be able to add the values shown on the Access email template above. How do I generate that table from Access and send it to Outlook looking like the Sketch I did few posts above.

 

[TecknoFreak]

Why not?, you create an email in your code, you do the same, but from a template?
I do not see the difference?

I see what you are saying but the emails created from Access will be automated to be sent every Friday's as soon I finish this. So I won't be able to change the value manually.

 

[TecknoFreak]

What I was asking is if there is a way to format this in VBA as html like I'm doing. Just doing it as tables like the sketch I did above