I have this code which generates an email for new records that have not been sent before. I want to alter it so it will attach a copy of the record formatted as a report which looks loosly like the original form.
How do i change the code to send an attachment and do i need to do anything special with the report to ensure it generates the right report for the specific email?
Any ideas?
-
Public Sub SendMail()
-
'Provides the Send Mail automation
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strSubject As String
-
Dim strEmailAddress As String
-
Dim strEMailMsg As String
-
Dim ingCounter As Integer
-
Dim intCount As Integer
-
-
strSubject = "Latest Job Outcomes"
-
strEmailAddress = "[Mail Addresses Go Here]"
-
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
-
'e-mail addresses
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("qrySendMail")
-
-
-
'Count of unsent e-mails
-
intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
-
, "[ysnSentByMailToStaff]=0")
-
'If count of unsent e-mails is zero then the procedure will not run
-
'If count of unsent e-mails is greater than zero, msgbox will prompt
-
'to send mail.
-
-
If intCount = 0 Then
-
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
-
, vbInformation, "System Information"
-
Exit Sub
-
Else
-
-
rst.MoveFirst
-
Do Until rst.EOF
-
-
strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
-
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
-
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
-
& "Below are the details that have been submitted by the student:" _
-
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
-
& "Graham"
-
-
'EMAIL USER DETAILS & ATT REPORT
-
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
-
, , strSubject, strEMailMsg, False, False
-
-
-
rst.MoveNext
-
Loop
-
rst.Close
-
Set rst = Nothing
-
dbs.Close
-
Set dbs = Nothing
-
-
'Run update to update the sent mail check box
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1 WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
-
DoCmd.SetWarnings True
-
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
-
End If
-
End Sub
-
