Automating Email and Report Scheme 1

[gal4y]

I have to send email messages to Company TAC Officers/TACNCOs every so often about upcoming briefings.

I have a list of cadets (in table cadets) with their personnel information plus the company/regiment that they are assigned to (e.g., A1, C2, etc). Comp_reg can go from A to H and 1 to 4 (32 different possibilities).

The following code will email the TAC, and TACNCO a list of assigned cadets (if any) and their presentation date, time, and project name. The report that is emailed is based on a query that has a criteria for A1.

How can I automate this where I don't have to make 32 different reports? Because I have to do this 4 times a semester, I don't want to change fields in the report from IPR_date#2 to IPR_date#3, etc.

Private Sub Command162_Click()
On Error GoTo Err_Command162_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim SQLString As String
SQLString = "SELECT DISTINCT [TAC_Email] & '@usma.edu' AS T_Email, [TACNCO_Email] & '@usma.edu' AS TN_Email FROM
Cadets INNER JOIN TAC_Email ON Cadets.TAC_Lastname =
TAC_Email.TAC_Lastname WHERE (((Cadets.Comp_Reg)='A1'))"

Set rst = db.OpenRecordset(SQLString)
rst.MoveFirst

DoCmd.SendObject acSendReport, "ReportA1",
acFormatHTML, rst!T_Email, rst!TN_Email, , "Invitation to
SE402 IPR", "This is an excellent opportunity...", True

rst.Close
db.Close

Exit_Command162_Click:
Exit Sub

Err_Command162_Click:
MsgBox Err.Description
Resume Exit_Command162_Click

End Sub

Thank you in advance
Greg

 

[KyleS]

Hi Greg,

First off, you really should give your button a more meaningful name, so down the road when someone else has to take care of this they'll know just by looking at the code which button it's for (i.e. "cmdMailReport&quot

Anyway...

To avoid having to make 32 different reports (1 for each company/regiment) just make a form with a combo box so the user can select the Comp_Reg they want to run the report for and change the criteria in the query to something like this: Forms!NewFormName!ComboBoxName and then make the code look like this:

Private Sub Command162_Click()
On Error GoTo Err_Command162_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim SQLString As String
SQLString = "SELECT DISTINCT [TAC_Email] & '@usma.edu' AS T_Email, [TACNCO_Email] & '@usma.edu' AS TN_Email FROM
Cadets INNER JOIN TAC_Email ON Cadets.TAC_Lastname =
TAC_Email.TAC_Lastname WHERE (((Cadets.Comp_Reg)='" & Forms!NewFormName!ComboBoxName & "'))"

Set rst = db.OpenRecordset(SQLString)
rst.MoveFirst

DoCmd.SendObject acSendReport, "ReportA1",
acFormatHTML, rst!T_Email, rst!TN_Email, , "Invitation to
SE402 IPR", "This is an excellent opportunity...", True

rst.Close
db.Close

Exit_Command162_Click:
Exit Sub

Err_Command162_Click:
MsgBox Err.Description
Resume Exit_Command162_Click

End Sub
As far as not having to change all the labels and record sources for the report when you run this for a different quarter, that gets a bit more complicated.
Does your table have areas for first, second, third and fourth quarter results?
So they have their own fields? If so (I am assuming so otherwise, how is this an issue?)
The creat your fields for all four quarters on top of each other and put an option field ("Select which quarter to run this report for:&quot and then in the OnOpen event of the report do something like this:

If Forms!NewFormName!OptionButtonName = 1 Then
lbl1stQuarter.Visible = True
txt1stQuarter.Visible = True
lbl2ndQuarter.Visible = False
txt2ndQuarter.Visible = False
lbl3rdQuarter.Visible = False
txt3rdQuarter.Visible = False
lbl4thQuarter.Visible = False
txt4thQuarter.Visible = False
Elseif Forms!NewFormName!OptionButtonName = 2 Then
lbl1stQuarter.Visible = False
txt1stQuarter.Visible = False
lbl2ndQuarter.Visible = True
txt2ndQuarter.Visible = True
lbl3rdQuarter.Visible = False
txt3rdQuarter.Visible = False
lbl4thQuarter.Visible = False
txt4thQuarter.Visible = False
ElseIf.....(You get the idea)

So run your query to bring in all the information and only make the information you want to see visible on the report...

Let me know if this helps... Kyle :

 

[gal4y]

Kyle,

Thanks for the help. I don't want to add any forms to the process. I would like to click on one button and run the animal.

In this situation, the query is running in the code. I don't have any queries or forms per se.
Here is some more information

The report contains:
Lastname, Firstname, IPRDate#2, IPRTime#2, Project Name.

The report has a filter on it for example for Company_Regiment: A1. It will look through the cadet table for cadets assigned to A1 and generate the appropriate report. The sql statement is the record source.

The code currently works for A1.

Question1: Is there a way to add a loop to generate a paramter to replace "ReportA1" with one that contains the sql statement for a generic report that filters based on company_regiment.

Question2: Is there a way to add a loop that will execute the code for A1, then A2, A3, A4, B1, .... H4. This is so I don't have to generate 32 different reports.

Question3: ReportA1 has 5 fields. 2 of the change based on IPRdate and IPRtime. Once IPR#2 is done, I would want it to change to IPR#3, etc. If question 1 and 2 are statisfied, then I can easily change the one report field sources.

Thank you for any assistance, in advance.

Greg

 

[KyleS]

OK, let me make sure I'm going in the direction you want... So you want to hit the one button and then have Access run the report for all 32 TACs and e-mail the appropriate people. As far as looping through the code you have here, that's not too much of a problem. To do that I would create a table (I know I know, but you'll only have to do this once and it will make your code A LOT neater) with all 32 TACs then create a recordset from that table and look through it. Replacing in your code the "A1" in the SQL statement with '" & rstXYZ!Tac & "' and "ReportA1" with a string variable = "Report" & rstXYZ!Tac

As far as getting a genecir SQL statement to run your reprt, I would need to see the SQL in the report in order to help with that. Kyle :

 

[gal4y]

Here is my report SQL:

SELECT DISTINCTROW Cadets.Comp_Reg, Cadets.LastName, Cadets.FirstName, Cadets.[IPR#3 Date], Cadets.[IPR#3 Time], [Project List].[Project Name] FROM Cadets INNER JOIN [Project List] ON (Cadets.[Design Group #] = [Project List].[Design Group #]) AND (Cadets.Hour = [Project List].Hour) ORDER BY Cadets.Comp_Reg;

I activated the filter box: [Comp_reg]='A1'

I am working on the loop

Thanks

Greg

 

[gal4y]

Kyle,

I figured it out with some help.
Thanks for you assistance
Here is the code I used.

Private Sub SendEmail_Report_Click()
On Error GoTo Err_SendEmail_Report_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim SQLString As String
Dim Test As String
Dim rpt As Report
Dim i As Integer
Dim msg As String

msg = "This is an invitation to an IPR." & Chr(13) & Chr(10) & "Cadet's name, Date/time and the project name are located in the enclosure" & Chr(13) & Chr(10) _
& "" & Chr(13) & Chr(10) _
& "This is a great opportunity to see cadets apply critical thinking and leadership." & Chr(13) & Chr(10) _
& "I welcome you to come and sit in on the IPR and ask questions." & Chr(13) & Chr(10) _
& "The IPR is a 25 minute presentation. Groups range for 2 to 4 cadets." & Chr(13) & Chr(10) _
& "Location: C7 Mahan Hall." & Chr(13) & Chr(10) _
& "" & Chr(13) & Chr(10) _
& "Thank you" & Chr(13) & Chr(10) _

'Stop

For j = 65 To 72
For i = 1 To 4
DoCmd.OpenReport "ReportIPR3", acViewDesign, , , acHidden
Set rpt = Reports("ReportIPR3&quot
rpt.Filter = "[Comp_Reg] = '" & Chr(j) & i & "'"


SQLString = "SELECT DISTINCT [TAC_Email] & '@usma.edu' AS T_Email, [TACNCO_Email] & '@usma.edu' AS TN_Email FROM Cadets INNER JOIN TAC_Email ON Cadets.Comp_Reg = TAC_Email.Comp_Reg WHERE (((Cadets.Comp_Reg)='" & Chr(j) & i & "'))"

Set rst = db.OpenRecordset(SQLString)
rst.MoveFirst
While Not rst.EOF
Debug.Print "rst!T_Email is " & rst!T_Email
Debug.Print "rst!TN_Email is " & rst!TN_Email

DoCmd.SendObject acSendReport, "ReportIPR3", acFormatHTML, rst!T_Email, rst!TN_Email, , "Invitation to SE402 IPR#3", msg, True

rst.MoveNext
Wend

rst.Close
Next i
Next j

db.Close

Exit_SendEmail_Report_Click:
Exit Sub

Err_SendEmail_Report_Click:
MsgBox Err.Description
Resume Exit_SendEmail_Report_Click

End Sub

 

[KyleS]

Greg,
I'm glad you were able to get it to work, sorry I wasn't more help. Kyle :