Access report to pdf, split by company grouping and e-mailed to company

[Piers]

I have an access report with groupings for approx 40 companys, i would like to know the vba code to PDf the report split and save the report by each company grouping and then sent to an relevant e-mail address. I new to writing VBA code so any help would be appreciated.

[TheSmileyCoder]

What version of access are you using? Access 2007 and later I believe has a built in pdf converter, though I have never tried it.

To output each group by itself would involve looping through them in VBA code, printing of each report with a WHERE clause to filter by that group. Adding it to an email is yet another issue.

[Piers]

Yeah its access 2007 i'm using, its the looping code that i need the help with both the pdf creation and the e-mail i think i can work out. but if someone could with the looping code i'd be there

[TheSmileyCoder]

What you would need to do is load a recordset of the companies, and loop through it. It could look like this, assuming you have a company table tbl_Company with primary autonumber key, KEY_Company:

'Load recordset
  Dim strSQL as string
  strSQL="SELECT * FROM tbl_Company" 'You can add a where clause if you wish
  Dim rsComp as Dao.Recordset
  set rsComp=CurrentDB.OpenRecordSet(strSQL,dbopendynaset)
 
 
  Dim strReportName as string
  strReportName="rep_Example"
 
'Loop over records
Do while not rsComp .EOF 
  'To open a report
    docmd.OpenReport strReportName ,acViewNormal,,"KEY_Company=" & rsComp!KEY_Company,acHidden 
 
  rsComp.MoveNext
Loop
 
'Cleanup
  Set rsComp=Nothing
 
 

This would print you a report for each company. How to modify that code to output pdfs instead I don't know as I dont use Ac2007.