I added your recommended code, but I'm still getting all pages in the report instead of just the single employee. Did I enter it incorrectly or in the wrong place?
thanks again!
Code:
Option Compare Database
Public Sub Command21_Click()
Dim sTo As String
Dim sSub As String
Dim sBody As String
Dim strRep As String
Dim strDPath As String
Dim strFName As String
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM [01 - 03 - Commission Summary Report - Sage One];", dbOpenSnapshot)
If (rst.RecordCount <> 0) Then
rst.MoveLast
rst.MoveFirst
Else
MsgBox "There are no records in the table!"
GoTo EndLine
End If
With rst
While Not .EOF
' What report to send
strRep = "01 - 01 - Commission Summary Report - Sage One"
' Initial Path
strDPath = "C:\Users\DLSmith\Documents\Commissions Project\Reports Sent\"
' Filename
strFName = .Fields("Name") & "_Order_" & .Fields("Month") & .Fields("Year") & "_" & Format(Date, "mm-dd-yyyy")
'add file extension
strFName = strFName & ".pdf"
' Output report as pdf
DoCmd.OpenReport strRep, acViewPreview, "Name=" & rst!Name
DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
DoCmd.Close acReport, strRep
' Send the report to whoever
sTo = .Fields("Manager Email")
sSub = .Fields("Name") & "_Commission Statement_" & .Fields("Month") & "_" & .Fields("Year")
sBody = "First name from some field" & vbCrLf & vbCrLf
sBody = sBody & "Continue with message"
Send_Email strDPath & strFName, sTo, sSub, sBody
.MoveNext
Wend
End With
EndLine:
End Sub
' ----------------------------------------------------------------------------------------
Private Sub Send_Email(strDoc As String, sTo As String, sSub As String, sBody As String)
Dim sCC As String
Dim sBCC As String
Dim OutApp As Object
Dim OutMail As Object
Dim varPress As Variant
strMess = "You are about to send an email message to " & sTo & vbCrLf & vbCrLf
strMess = strMess & "Do you wish to continue?"
strStyle = vbYesNo
strTitle = "Send Notification"
varPress = MsgBox(strMess, strStyle, strTitle)
If varPress = vbYes Then
' Create the email
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
sCC = ""
sBCC = ""
With OutMail
.To = sTo
.CC = sCC
.BCC = sBCC
.Subject = sSub
.Body = sBody
.attachments.Add (strDoc)
'.Display ' THis will display the email, but not send it
.Send ' THis will send the email
End With
Set OutMail = Nothing
Set OutApp = Nothing
End If
End Sub