By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Generate report and email as attachment

P: 36
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?
Expand|Select|Wrap|Line Numbers
  1. Public Sub SendMail()
  2. 'Provides the Send Mail automation
  3. Dim dbs As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim strSubject As String
  6. Dim strEmailAddress As String
  7. Dim strEMailMsg As String
  8. Dim ingCounter As Integer
  9. Dim intCount As Integer
  10.  
  11. strSubject = "Latest Job Outcomes"
  12. strEmailAddress = "[Mail Addresses Go Here]"
  13. 'strEmailAddress,replace [Mail Addresses Go Here] above with valid
  14. 'e-mail addresses
  15. Set dbs = CurrentDb
  16. Set rst = dbs.OpenRecordset("qrySendMail")
  17.  
  18.  
  19. 'Count of unsent e-mails
  20. intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
  21. , "[ysnSentByMailToStaff]=0")
  22. 'If count of unsent e-mails is zero then the procedure will not run
  23. 'If count of unsent e-mails is greater than zero, msgbox will prompt
  24. 'to send mail.
  25.  
  26.     If intCount = 0 Then
  27.         MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
  28.         , vbInformation, "System Information"
  29.         Exit Sub
  30.     Else
  31.  
  32. rst.MoveFirst
  33. Do Until rst.EOF
  34.  
  35.     strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
  36.     & " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
  37.     & " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
  38.     & "Below are the details that have been submitted by the student:" _
  39.     & Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
  40.     & "Graham"
  41.  
  42.     'EMAIL USER DETAILS & ATT REPORT
  43.     DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
  44.     , , strSubject, strEMailMsg, False, False
  45.  
  46.  
  47.     rst.MoveNext
  48. Loop
  49. rst.Close
  50. Set rst = Nothing
  51. dbs.Close
  52. Set dbs = Nothing
  53.  
  54. 'Run update to update the sent mail check box
  55.     DoCmd.SetWarnings False
  56.     DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1 WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
  57.     DoCmd.SetWarnings True
  58.     MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
  59.    End If
  60. End Sub
  61.  
Mar 21 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
Why not use the report as the object to send ?
Just use the first two parameters of the command, and Access will attach it for you.

Nic;o)
Mar 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.