Send Access report in body of Outlook email (1 Viewer)

[bwc]

The Master Sergeant wanted me to run a report at the end of the day, and then email it to him… easy enough. He also wants a summary of the report in the body of the email. :banghead: There must be a way to have access do this for me. So… I started with this code from Twinnyfo, http://bytes.com/topic/access/answers/943464-send-access-report-body-outlook-email , read many tips from Allen Brown, http://www.allenbrowne.com/AllenBrown, and many threads on this site, and this the code that I have come up with.
This code requires
table for report data – (mine is tblClinicData)
and the report (rptTotal)

[FONT=Calibri][SIZE=3]Option Compare Database[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Option Explicit[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Private Sub cmdSendRpt_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Call sendRpt[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]Function sendRpt()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]On Error GoTo errHandler[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]' populate tblClinicSum[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.SetWarnings False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.OpenQuery "qryClinicSumAppd"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.SetWarnings True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.Close acQuery, "qryClinicDataCt"[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]'Recordset variables[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Dim db As Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rst As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strSQL As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strSubject, strBody, strAddresses, strCC As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set db = CurrentDb()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strSQL = "" & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     "SELECT *" & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     "FROM tblClinicSum " & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     ";"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If Not rst.EOF Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]' build email body - grand totals from rptTotal[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strBody = strBody & "ALCON:" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strBody = strBody & "Attached are CRTC and CGTACC" & " " & Me![rptHdrDate] & ":" & vbCrLf & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strBody = strBody & "" & Me![txtTotGrd] & " combined contacts : Average Time " & Me![txtAvgTimeGrd] & " minutes" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtPhaGrd] > 0 Then strBody = strBody & "" & Me![txtPhaGrd] & " PHA's (" & Me![txt40Grd] & " over 40)" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtHivGrd] > 0 Then strBody = strBody & "" & Me![txtHivGrd] & " HIV Blood draws" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtHepGrd] > 0 Then strBody = strBody & "" & Me![txtHepGrd] & " Hepatitis A/B vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtFluGrd] > 0 Then strBody = strBody & "" & Me![txtFluGrd] & " Influenza vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtTdapGrd] > 0 Then strBody = strBody & "" & Me![txtTdapGrd] & " Tdap vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtProGrd] > 0 Then strBody = strBody & "" & Me![txtProGrd] & " Profile reviews" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Me![txtOthGrd] > 0 Then strBody = strBody & "" & Me![txtOthGrd] & " other" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strBody = strBody & "_____________" & vbCrLf & vbCrLf[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   rst.MoveFirst[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]' build email body - clinic totals[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Do While Not rst.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     strBody = strBody & rst("clstrLocation") & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       rst("avgMinutes") & " average minutes" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("pha") > 0 Then strBody = strBody & rst("pha") & " PHA's"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         If rst("over40") > 0 Then strBody = strBody & " (" & rst("over40") & " over 40)" & vbCrLf Else strBody = strBody & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("hiv") > 0 Then strBody = strBody & rst("hiv") & " HIV Blood draws" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("flu") > 0 Then strBody = strBody & rst("flu") & " Influenza vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("hep") > 0 Then strBody = strBody & rst("hep") & " Hepatitis A/B vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("tdap") > 0 Then strBody = strBody & rst("tdap") & " Tdap vaccinations" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("profile") > 0 Then strBody = strBody & rst("profile") & " Profile reviews" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If rst("other") > 0 Then strBody = strBody & rst("other") & " other" & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     strBody = strBody & vbCrLf & "_____________" & vbCrLf & vbCrLf[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     rst.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Loop[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strSubject = "" & [Forms]![frmQryByForm]![cbTotal] & "" & "_weeklyClinicTotals" & ""[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strAddresses = "chris[EMAIL="chris@mail.mil"]@mail.mil[/EMAIL]"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strCC = "" & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     "[EMAIL="dean.@mail.mil"]dean.@mail.mil[/EMAIL];" & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          DoCmd.SendObject acSendReport, "rptTotal", acFormatPDF, strAddresses, strCC, , strSubject, strBody, True, False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]errHandler:[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     If Err.Number = 2501 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         Resume Next[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'            Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'    VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Function[/FONT][/SIZE]

query to base crosstab query from (qryClinicData)

SELECT tblClinicData.cldatexamDate, Format([cldatTimeOut]-[cldatTimeIn],"Short Time") AS timeAtClinc, DateDiff("n",[cldatTimeIn],[cldatTimeOut]) AS Minutes, tblClinicData.cldatTimeIn, tblClinicData.cldatTimeOut, tblClinicData.clstrLocation, tblClinicData.clstrPHA, tblClinicData.clstrHIV, tblClinicData.clstrOver40, tblClinicData.clstrFlu, tblClinicData.clstrHep, tblClinicData.clstrTdap, tblClinicData.clstrProfile, tblClinicData.clstrOther
FROM tblClinicData;

crosstab query for summary (qryClinicDataCt)

PARAMETERS [Forms]![frmQryByForm].[cbTotal] DateTime;
TRANSFORM Round(Avg([qryClinicData].[Minutes]),0) AS clinicTime
SELECT qryClinicData.clstrLocation, Round(Avg([qryClinicData].[Minutes]),0) AS avgMinutes, -Sum(qryClinicData.clstrPHA) AS pha, -Sum(qryClinicData.clstrOver40) AS over40, -Sum(qryClinicData.clstrHIV) AS hiv, -Sum(qryClinicData.clstrHep) AS hep, -Sum(qryClinicData.clstrFlu) AS flu, -Sum(qryClinicData.clstrTdap) AS tdap, -Sum(qryClinicData.clstrProfile) AS profile, -Sum(qryClinicData.clstrOther) AS other
FROM qryClinicData
WHERE (((qryClinicData.cldatexamDate) Between [Forms]![frmQryByForm].[cbTotal] And [Forms]![frmQryByForm].[cbTotal]-6))
GROUP BY qryClinicData.clstrLocation
PIVOT qryClinicData.timeAtClinc;

table for data summary (tblClinicSum)
append query to populate summary table (qryClinicSumAppd)

INSERT INTO tblClinicSum ( clstrLocation, avgMinutes, pha, over40, hiv, hep, flu, tdap, profile, other )
SELECT DISTINCT qryClinicDataCt.clstrLocation, qryClinicDataCt.avgMinutes, qryClinicDataCt.pha, qryClinicDataCt.over40, qryClinicDataCt.hiv, qryClinicDataCt.hep, qryClinicDataCt.flu, qryClinicDataCt.tdap, qryClinicDataCt.profile, qryClinicDataCt.other
FROM qryClinicDataCt
GROUP BY qryClinicDataCt.clstrLocation, qryClinicDataCt.avgMinutes, qryClinicDataCt.pha, qryClinicDataCt.over40, qryClinicDataCt.hiv, qryClinicDataCt.hep, qryClinicDataCt.flu, qryClinicDataCt.tdap, qryClinicDataCt.profile, qryClinicDataCt.other;

form for selecting date for the report (frmQryByForm)

Option Compare Database
Option Explicit
Private Sub cmdTotal_Click()
Dim dbs As Database  ' clear old data from tblClinicSum
Dim sql As String
Set dbs = CurrentDb
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryClinicDataCt"
    sql = "Delete * from tblClinicSum"
      DoCmd.RunSQL sql
  DoCmd.SetWarnings True
    If IsNull([Forms]![frmQryByForm].[cbTotal]) Then
        MsgBox "You must select a week!!!", vbOKOnly, "Warning"
    Else
        DoCmd.OpenReport "rptTotal", acViewReport, , "[cldatexamDate] Between #" & [Forms]![frmQryByForm].[cbTotal] & "# And " & _
          "#" & [Forms]![frmQryByForm].[cbTotal] - 6 & "#", acNormal
    End If
End Sub

I want to thank all that have posted threads that has helped me with this project and in return I post what I have in hopes that it will help someone else with their access endeavors.