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

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
Hello everyone,

So after couple of hrs of reading similar problems to mine in this forum I still haven't been able to find something close enough I can try. (Sorry I'm new to Access)

What I'm trying to do is get some values from another Module to fill the Email Body using values from 2 other modules but just cant find a way to make that to work. :( Everything else is working great!

This is the Email Report I'm working on
Code:
Option Compare Database

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:\folder\subfolder\LOGBOOK_DATABASE\Reports\IAAIMS Reports\IAAIMS Weekly Report.pdf"
strAttach2 = "P:\folder\subfolder\LOGBOOK_DATABASE\Reports\IARIMS Reports\IARIMS Weekly Report.pdf"
strAttach3 = "P:\folder\subfolder\LOGBOOK_DATABASE\Reports\610 Reports\610 Weekly Report.pdf"  ' ------------ (Not Emailing this Report but Generating)


'Generate email here
With oEmail
          .Display
          .To = "**************************"
          .CC = "*************************"
          .Subject = "Ranges Summary Report (Week Ending) " & Date - Weekday(Date) - 1
          .HTMLBody = "Please refer to the attachment name to see the corresponding Range Report. <br><br>" _
                    & "Attention: Jeff ******* <br><br>" _
                    & "<B><U>IAAIMS Report</B></U><br>" _
                    & "Total QTY of Edges: " & "<U>Me.WSToEd.Value</U><br>" _
                    & "Total QTY of Sys Installs: " & "<U>Me.WSToSI.Value</U><br>" _
                    & "Total QTY of BK-4 Material: " & "<U>Me.TBK4.Value</U><br><br>" _
                    & "<B><U>IARIMS Report</B></U><br>" _
                    & "Total QTY of Edges: " & "<U>Me.WSToPT.Value</U><br>" _
                    & "Total QTY of Wedges: " & "<U>Me.WSToWe.Value</U><br>" _
                    & "Total QTY CRO/MICAP's : " & "<U>Me.TCoatedEdges.Value</U><br><br><br>" _
                    & "<I>Report Generated by " & Environ("UserName") & " using the Ranges MS Access Database created by Angel S.</I>"
          .Attachments.Add strAttach1
          .Attachments.Add strAttach2
       
End With
End Sub
 
Last edited:

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
***This is the IAAIMS module I'm trying to get that data to populate IAAIMS Report above
Code:
Option Compare Database

Private Sub Form_Load()
    GetData
End Sub

Private Sub Mech_LEF_Total_Parts_BeforeUpdate(Cancel As Integer)
'SELECT Count(*) AS [Total Parts], [IAAIMS DATA ENTRY TABLE].Part
'FROM [IAAIMS DATA ENTRY TABLE]
'WHERE ((([IAAIMS DATA ENTRY TABLE].[Date Started]) Between Date - (6 + Weekday(Date, 7)) And Date - Weekday(Date, 7))
'GROUP BY [IAAIMS DATA ENTRY TABLE].Part
'HAVING ((([IAAIMS DATA ENTRY TABLE].Part)="MECH / LEF"),0));
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)
    FailCoatedLEF = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='LEF - Coated'" & "and [PassFail]='Fail'"), 0)
    FailCoatedWTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='WTTE - Coated'" & "and [PassFail]='Fail'"), 0)
    FailCoatedHTTE = Nz(DSum("[TotalParts]", "Q_IAAIMS_WeeklyReport", "[Part]='HTTE - Coated'" & "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


***This is the IARIMS module I'm trying to get that data to populate IARIMS Report above
Code:
Option Compare Database

Private Sub Form_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.TCoatedEdges = Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='LEF - Coated'"), 0) + Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='WTTE - Coated'"), 0) + Nz(DSum("[TotalParts]", "Q_IARIMS_WeeklyReport", "[Type]='HTTE - 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


***Attached is how the email looks like after generating the Email Report and also I included how the actual Reports looks like for both areas.

Any help will be sooooo much appreciated!
 

Attachments

  • Email.PNG
    Email.PNG
    29.5 KB · Views: 353
  • IAAIMS _Report.PNG
    IAAIMS _Report.PNG
    36.6 KB · Views: 357
  • IARIMS _Report.PNG
    IARIMS _Report.PNG
    35.5 KB · Views: 359

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
Try
Code:
"Total QTY of Edges: " & "<U>" & Me.WSToEd & "</U><br>"
Here is how I approached it. Please see attached.
Had to attach as site complained too large :(

HTH
 

Attachments

  • HTML email VBA.txt
    13.3 KB · Views: 363
Last edited:

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
Try
Code:
"Total QTY of Edges: " & "<U>" & Me.WSToEd & "</U><br>"
Here is how I approached it. Please see attached.
Had to attach as site complained too large :(

HTH
Hello Gasman,
I tried that but I get a syntax error on every line I edited. I'm sorry but I'm very new to VBA and Access. I looked your attachment and it gave me brainfarts. :eek:
 

Attachments

  • new_code.PNG
    new_code.PNG
    29.6 KB · Views: 319

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
Where is the email button located?
You still need the line continuation characters, unless you do it like I did?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
Well Me refers to the parent object where the code is?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
And the form values, come from where?
Please post the code as code and not half pic pictures.
 

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
And the form values, come from where?
Please post the code as code and not half pic pictures.
Sorry, didnt shared the code because is the first code shared in first post.
as for the values they come from 2 queries

Query for IAAIMS
Code:
SELECT Count(*) AS TotalParts, [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail
FROM [IAAIMS DATA ENTRY TABLE]
WHERE ((([IAAIMS DATA ENTRY TABLE].[Date Started]) Between Date()-(6+Weekday(Date(),7)) And Date()-Weekday(Date(),7)))
GROUP BY [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail;

Query for IARIMS
Code:
SELECT Count(*) AS TotalParts, [IARIMS Data Entry Edges].Type, [IARIMS Data Entry Edges].Wedges, [IARIMS Data Entry Edges].PassFail
FROM [IARIMS Data Entry Edges]
WHERE ((([IARIMS Data Entry Edges].[Date Started]) Between Date()-(6+Weekday(Date(),7)) And Date()-Weekday(Date(),7)))
GROUP BY [IARIMS Data Entry Edges].Type, [IARIMS Data Entry Edges].Wedges, [IARIMS Data Entry Edges].PassFail;

they show as per attachments below;
 

Attachments

  • IAAIMS_Query.png
    IAAIMS_Query.png
    11.6 KB · Views: 295
  • IARIMS_Query.PNG
    IARIMS_Query.PNG
    15.7 KB · Views: 220

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
Ok, you can only use me.whatever in the object where the code is, not somewhere else. Hence your problem.
Quickest way would be to set tempvars for those values, and use those instead.

Or you can do as I did and use a recordset for the values?
 

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
Ok, you can only use me.whatever in the object where the code is, not somewhere else. Hence your problem.
Quickest way would be to set tempvars for those values, and use those instead.

Or you can do as I did and use a recordset for the values?
Can you please give an example using my code in first post? :(
Im just sooo new trying to lean this stuff because I had this made for me a year ago. Now that the guy is no where to be found Im trying to learn the code to maintain the database myself.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
Not tonight, sorry. Posting on my phone, about to go to bed, and out all day tomorrow. Hopefully someone else will chip in.
Lesson to learn, get to understand code given you, exactly because of this
:(
 

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
Yeah lets hope someone can really help me with this. :(
Thanks sooo much for your time and tips Gasman
 

TecknoFreak

Member
Local time
Today, 10:31
Joined
Dec 21, 2021
Messages
57
I been reading about looping, can I loop those records to show on the email as total, count for that record? What will be the easiest approach to make this happen?

Thanks in advance for the very needed help
 

Attachments

  • Email.PNG.png
    Email.PNG.png
    29.5 KB · Views: 277

Gasman

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
OK, just about to go out for my first passenger of the day.
However a quick solution that I have mentioned before is to use TempVars to hold those values.
They will then be available anywhere in your session, until you close the database.
You still need to get the syntax correct though, but that appears to me as I suggested now that it complains about a form control that does not exist?

Failing that, upload enough of your DB to replicate the problem and instructions on how to use it.

Good luck.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:31
Joined
Oct 29, 2018
Messages
21,358
@theDBguy You think you can help me with this petition please?
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...
 

Users who are viewing this thread

Top Bottom