VBA in Access Help needed 1

[annmariebette]

Hi there,
Once again I am calling out to the intellectuals who subcribe to Tek_Tips for help. My user has a query based on 2 tables that she uses to produce an Extracurricular APpproval Report to send out to all staff. This report (columnar style) lists each staff member at our school district and the extracurricular activities they've participated in for the school year. These activities are listed as yes/no controls. What she would like to do is transmit an email to the respective staff member and automatically send a copy of their info (which is a single page of the report) as an email attachment.

How do you do this? I am not a VBA guru so I'm struggling with looking up code and syntax.

I was able to do something like this by attaching a button command to a form which (using my limited VB knowledge) runs a report based on just that one individuals record and then transmits the report as an email attachment to that respective staff members email address. My user was happy with this but now she wants to be able to do this (globally) for everyone rather than click a button 233 times for the amount of staff in her database!!!

My code I have looks like this:
Private Sub cmdPrintRecord_Click()
Dim strReportName As String
Dim strCriteria As String
Dim strEmail As String

strReportName = "rptInstructorTEST"
strCriteria = "[InstructorID]=" & Me![InstructorId] & ""
strEmail = Me!
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

Dim txtTO, txtCC, txtBCC, txtSubject, txtMessage As String
txtTO = strEmail
txtCC = ""
txtBCC = ""
txtSubject = "Report of Activities for Previous Year"
txtMessage = ""
DoCmd.SendObject acSendReport, "rptInstructorTEST", acFormatRTF, txtTO, txtCC, txtBCC, txtSubject, txtMessage, False

End Sub

I tried placing this code as an event attached to the OnPrint Property of the Detail section of a report thinking it would transmit the email by record however, you cannot execute the email transmittal while the report object is still processing. I was able to get away with it using a command button on a form because the form object would stop when the report object ran and the report object completed when Outlook was running. I received a Tek-Tip from a wonderful gent called Hap who suggested I use VBA code to read the query one record at a time and process a report and email for every record read. I quess I would have to use some kind of If, then, else or Do Loop logic? I just do NOT know the language very well. There's a rush to get this done (as usual!). PLEASE can someone give me some code tips??? What's the lingo to do this?

I GREATLY appreciate any help I can get with this!
Ann Marie
Tech Support for a School District

 

[JeremyNYC]

If you're e-mailing this to a bunch of teachers, I would avoid all technology requirements for them, and put the data in text form in an e-mail. You can just build a recordset and build the message portion based on that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jebry]

Hi Anne Marie!

Try this:

Private Sub cmdPrintRecord_Click()
Dim strReportName As String
Dim strCriteria As String
Dim strEmail As String
Dim rst As DAO.Recordset
Dim txtTO, txtCC, txtBCC, txtSubject, txtMessage As String

Set rst=CurrentDb.OpenRecordset("YourIntructorTable", dbOpenDynaset)
rst.MoveFirst
strReportName = "rptInstructorTEST"
Do Until rst.EOF = True
strCriteria = "[InstructorID]=" & rst![InstructorId]
strEmail = rst!
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

txtTO = strEmail
txtCC = ""
txtBCC = ""
txtSubject = "Report of Activities for Previous Year"
txtMessage = ""
DoCmd.SendObject acSendReport, "rptInstructorTEST", acFormatRTF, txtTO, txtCC, txtBCC, txtSubject, txtMessage, False
rst.MoveNext
Loop

Set rst = Nothing

End Sub

This assumes that you have an instructors table with the ID and Email in it.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[longhair]

Ann,

is the email address in a table? if so you could loop through the table rather than use the 'Me!'.
you could also create a DAO.recordset and then loop through that.

regards,

longhair