Change Query Criteria and send object to Email (1 Viewer)

[U0707828]

Greetings,

I have a db that tracks multiple projects and each mth I need to send reports for these projects to the managers. My predecessor would export the data to excel and slice and dice the desired projects out into separate excel sheets and send via email.

I know this can be easier. I have created a query that captures the project data and places it in the correct order. This qry is named 'FY15 Project Data'. The main field in the qry that I wish to limit is [FY15 Project Data].[Project]. My projects are short text such as 3000266 or 3000273 (different variations).

After the qry is filtered by Project I would like to send the results in excel form to an email.

This is what I have for the emailing portion, but I'm having trouble with the filtering portion

'------------------------------------------------------------
' Email_FY15_Projects
'
'------------------------------------------------------------
Function Email_FY15_Projects()
On Error GoTo Email_FY15_Projects_Err
DoCmd.SendObject acQuery, "FY15 Project", "ExcelWorkbook(*.xlsx)", "email address", "", "", "Monthly Project Report", "Please review Text......", True, ""

Email_FY15_Projects_Exit:
Exit Function
Email_FY15_Projects_Err:
MsgBox Error$
Resume Email_FY15_Projects_Exit
End Function

I'm not new to access, but very new to coding in vba.

I'm also assuming this is the way to accomplish my task, but if there is another way please share.

Thanks.

 

[pbaldy]

I often use this method, along with the loop:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm