Solved Email .HTMLBody to include totals from other Modules or Queries? (1 Viewer)

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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
Code:
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
Code:
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.
Code:
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
 
Last edited:

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
Im sooo sorry. Im so new to this that I dont even know how to explain my problem correctly :cry:
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
@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?
Code:
DoCmd.OpenForm "F_IAAIMSWeeklyReport", acNormal, , , acHidden
?????????????
DoCmd.Close acForm "F_IAAIMSWeeklyReport", acSaveNo


This is how the HTMLBody looks like
Code:
.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

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
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,
Code:
OpenForm
Generate Email
Close Form
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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.

1640816778230.png
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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:
Code:
& "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

1640892805877.png
spaces-dashes.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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.

1640977197629.png
 

Attachments

  • RE Tables test.txt
    12.8 KB · Views: 123
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:44
Joined
Sep 21, 2011
Messages
14,047
You could set it up as a template with placeholders, then replace those with your values?
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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 :cry:
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
Just google


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

Code:
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 :(
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:44
Joined
Sep 21, 2011
Messages
14,047
Why not?, you create an email in your code, you do the same, but from a template?
I do not see the difference?
 

TecknoFreak

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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

Member
Local time
Today, 14:44
Joined
Dec 21, 2021
Messages
57
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
 

Users who are viewing this thread

Top Bottom