VBA Errors - SendObject

New Contributor



I'm a complete and total VBA novice. I'm trying to learn but it's making want to scream into a cushion. I would really appreciate some help.


I have a database all set up for student information incl timetables. I need to send out the timetables to the students at the beginning of the year. I worked out how to send individual reports to the listed email using a macro connected to button with an on click event - that works perfectly.


Problem is I don't want our admin assistant to have to go through each of the hundreds of students individually and send the report. I would like her to be able to click a button and for access to loop through all of the students from first to last, generating emails along the way.


As I said, I'm a complete novice here, but I want to learn how to do this. At the moment I just need help with how to set up the DoCmd.SendObject function. I've got this (below) but access isn't happy and I can't pinpoint the problem. The error that comes up is: Compile Error: Expected: expression.


DoCmd.SendObject(acSendReport,"ImmersionRFiltered",acFormatPDF,([Forms]![ImmersionIndF]![Email]),,,"IMPORTANT: Module 201 Immersion Week Timetable",,True,,)




Ps. if anyone could point me in the direction of a really beginner level guide that would be fantastic.


3 Replies
best response confirmed by PGregers (New Contributor)


you may need to Open a Recordset (your student table) and loop through each record and send the email:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("yourStudentTable")
With rs
    If Not (.BOF And .EOF) Then
    End If
    Do Until .EOF
        DoCmd.SendObject acSendReport, "ImmersionRFiltered", acFormatPDF, !Email & "", , , "IMPORTANT: Module 201 Immersion Week Timetable"
End With
Set rs = Nothing
Set db = Nothing
You are an absolute genius! Thanks so much, that works perfectly.
You can delete lines 7-9. They don't add anything. The test in line 10 suffices.