Export report to separate PDF's

[jillnoble]

Hi,

This discussion seems to have been covered several times in many places, so I'm sorry if I've out this in the wrong place! I've been trying for a few days but no matter what code I use I cannot get it to work. I am very unfamiliar with code as I only set this DB up as a favour for work, never used a DB before and that was 6 years ago! I've fudged my way through and I know my DB isn't perfect, far from it, but it does what we need it to do, its just when I come to a problem like this it really throws me...

Basically, I have a report which gets printed every week and each page is each employees wages and work from the previous week, its a breakdown for them to check, and we now want to go over to emailing them, their actual wages slips will be emailed from Sage but they all require these details too. If I could just get the report to export to separate PDF docs, I can then add their email onto the wage slip as a hyperlink and email from there. Seems a bit of a faff but still easier and more environmentally friendly than printing, put in envelopes and sticking labels on!

If there is anyone who could help me with a simple code for dummies (that's me ) it would be very much appreciated.

I tried to attached a very slimed down version of the DB which includes the report and the query/tables related to it but it's too large, I have created a link if you can use that? -Link to my DB

I'm just not sure where I should be putting the code, in the query, in a form or in the report?? There are a few examples of code already in there which I have tried to adapt but as I don't really understand it I'm probably doing it all wrong.

Many thanks in advance

 

[theDBguy]

Hi. To export a report to PDF, you could use the OutputTo method; but as you said, that would create one big PDF file for the entire report.

If you want to break it up into separate employee pages, you can use a loop to execute the OutputTo method by filtering the report to each employee ID.

 

[Ranman256]

the form has a listbox of people.
the report uses a query that looks at this listbox to get that persons key, and only opens that persons data
ie: select * from table where [personID]=forms!fMyForm!lstbox
a button to cycle thru a listbox creating a report for each person.

sub btnSend_click()
Dim i As Integer
dim vItm
For i = 0 To lstBox.ListCount - 1
   vItm= lstBox.ItemData(i) 'get next item in list data
   lstBox = vItm    'set listbox to the item

'now get values from field columns
   vName = lstBox.column(0)    'in vb, columns start with zero
   vEmail = lstBox.column(1)     'get email from listbox ,col.2

      'do stuff with it here.  make the file
    vFile = "c:\temp\Report_" & vName & ".pdf"

        'make a pdf report
    docmd.OutputTo acOutputQuery ,acQuery,acFormatPDF,vFile

        'email if needed
DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, vEmail, , , "Subject", "message"
Next
end sub

 

[jillnoble]

Hi. To export a report to PDF, you could use the OutputTo method; but as you said, that would create one big PDF file for the entire report.

If you want to break it up into separate employee pages, you can use a loop to execute the OutputTo method by filtering the report to each employee ID.

Thank you DBguy but I think this may be a bit above me. I can’t even get the outputto to work! I’ve googled it, Copied, pasted and amend to my own names etc on several different versions (I assume I put that in the vba of the report?) I think I may have to think of another way round. I may just have to print, scan and email. Thank you for your time though.

 

[jillnoble]

the form has a listbox of people.
the report uses a query that looks at this listbox to get that persons key, and only opens that persons data
ie: select * from table where [personID]=forms!fMyForm!lstbox
a button to cycle thru a listbox creating a report for each person.

sub btnSend_click()
Dim i As Integer
dim vItm
For i = 0 To lstBox.ListCount - 1
   vItm= lstBox.ItemData(i) 'get next item in list data
   lstBox = vItm    'set listbox to the item

'now get values from field columns
   vName = lstBox.column(0)    'in vb, columns start with zero
   vEmail = lstBox.column(1)     'get email from listbox ,col.2

      'do stuff with it here.  make the file
    vFile = "c:\temp\Report_" & vName & ".pdf"

        'make a pdf report
    docmd.OutputTo acOutputQuery ,acQuery,acFormatPDF,vFile

        'email if needed
DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, vEmail, , , "Subject", "message"
Next
end sub

Thank you so much but I’ve kinda lost the Will to live with it at the moment! This looks great but I don’t even know where to put it! Like I’ve said to thedbguy, I think I’m going quite a way out of my understanding. Thanks for trying though!

 

[Uncle Gizmo]

Hi Jill, I have removed the link to the DB because it contains lots of confidential information!

 

[jillnoble]

Hi Jill, I have removed the link to the DB because it contains lots of confidential information!

Ok Uncle Gizmo, no problem. I thought it would be ok as no addresses, email addresses, phone numbers etc (except mine) only random names. However, I totally understand. Thank you

 

[Uncle Gizmo]

only random names.

Well I don't know what you think you posted, but I have found the Company, it's location, managing director first name David, Company Secretary Teresa, Manager Keith Earns £100 /Day... I have the full Address & postcode. approximately:- Bungay |Suffolk the company telephone number ends in: 434

 

[jillnoble]

Well I don't know what you think you posted, but I have found the Company, it's location, managing director first name David, Company Secretary Teresa, Manager Keith Earns £100 /Day... I have the full Address & postcode. approximately:- Bungay |Suffolk the company telephone number ends in: 434

Arh, I see what u mean, once you know the company name you can get all those details from companies house anyway so didn’t think that would be a problem. But yes, see what u mean about Keith and the wages. My mistake and am very sorry. Just didn’t know how else to get the help I needed. Apologies.

 

[MajP]

I added a form

When you set the date it creates the following.

You must put a folder in the same folder as the database called exactly "PaySlips".

You must import what I send you into your database before trying to open it.

Here is the code if interested

Public Sub LoopPaySlips(startDate As Date, endDate As Date)
  Dim EmpID As String
  Dim EmpName As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "Select Distinct [Employee ID] from qryPaySlips where [Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(endDate, "mm/dd/yyyy") & "#"
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    EmpID = rs![Employee ID]
    CreatePaySlip startDate, endDate, EmpID
    rs.MoveNext
  Loop
 
End Sub

Public Sub CreatePaySlip(startDate As Date, endDate As Date, EmpID As String)
  Const rptName = "rptExportPaySlips"
  Const FolderName = "payslips"
  Dim EmpName As String
  Dim strPath As String
  Dim pathAndFile As String
  Dim strWhere As String
 
  strPath = CurrentProject.Path
  strPath = strPath & "\" & FolderName & "\"
  'strWhere = "[Employee ID] = '" & EmpID & "'"
  strWhere = "([Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(endDate, "mm/dd/yyyy") & "#) AND [Employee ID] = '" & EmpID & "'"
  EmpName = Replace(GetEmpName(EmpID), " ", "_")
  Debug.Print strWhere
  DoCmd.OpenReport rptName, acViewPreview, , strWhere, acHidden
  pathAndFile = strPath & EmpName & Format(endDate, "YYYYMMDD") & ".pdf"
  Debug.Print pathAndFile
  DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, pathAndFile
  DoCmd.Close acReport, rptName
End Sub


Public Function GetEmpName(EmpID As String) As String
  GetEmpName = Nz(DLookup("[Employee Name]", "[Tubes Employees Database]", "[employee id] = '" & EmpID & "'"), "ID Not Found")
End Function

 

[MajP]

So here is the Knight Daniel report. Is this what is expected?

 

[jillnoble]

So here is the Knight Daniel report. Is this what is expected?

That is exactly what I need, thank you so much for coming to my rescue yet again!!

Not wanting to push my luck, but I have this found this command on the internet -
DoCmd.SendObject acsendNoObject,,,"jillnoble@tubesscaffolding.co.uk",,,"Wage Slip Breakdown","Please find attached your wage slip Breakdown for this week",True
Is there anywhere I can slot this in so it exports them to email, or is it much more complicated than that?
Thanks again for everyhing

 

[MajP]

Is there anywhere I can slot this in so it exports them to email, or is it much more complicated than that?

Doing this is a little more involved, but not complicated. There is a ton of code out there to send an attachment via email since this is a very common task. In the code CreatePaySlips after it saves a file to disk you have everything you need to call a new function MailPaySlips (to be developed). You would pass to that function the full path of the recently created file and the EmployeeID. Here is a new function to get the email once you pass the employee id to the MailPaySlips procedure.

Public Function GetEmpEmail(EmpID As String) As String
  GetEmpEmail = Nz(DLookup("[Email Address]", "[Tubes Employees Database]", "[employee id] = '" & EmpID & "'"), "ID Not Found")
End Function

I do not have time right now to find the code and put this together, but may be able to later. Or you can post in a new thread / or search for code for sending an email with an attachment. It is very common code since people do this a lot. You have what you need since you have the email address and path to attachment for each person. You just call this procedure right after the file is saved within the loop.

 

[Uncle Gizmo]

can slot this in so it exports them to email, or is it much more complicated than that?

Have a look at Gina Whipp's webpage, she describes how you can send reports via email. Nice explanation:-

Access Diva Send eMail to Multiple Recipients

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[Uncle Gizmo]

There's another example here from granite:-

Emailing a different report to each recipient

 

[jillnoble]

Doing this is a little more involved, but not complicated. There is a ton of code out there to send an attachment via email since this is a very common task. In the code CreatePaySlips after it saves a file to disk you have everything you need to call a new function MailPaySlips (to be developed). You would pass to that function the full path of the recently created file and the EmployeeID. Here is a new function to get the email once you pass the employee id to the MailPaySlips procedure.

Public Function GetEmpEmail(EmpID As String) As String
  GetEmpEmail = Nz(DLookup("[Email Address]", "[Tubes Employees Database]", "[employee id] = '" & EmpID & "'"), "ID Not Found")
End Function

I do not have time right now to find the code and put this together, but may be able to later. Or you can post in a new thread / or search for code for sending an email with an attachment. It is very common code since people do this a lot. You have what you need since you have the email address and path to attachment for each person. You just call this procedure right after the file is saved within the loop.

You're all brilliant - Thank you!

 

[jillnoble]

There's another example here from granite:-

Emailing a different report to each recipient

You're all brilliant - Thank you!

 

[MajP]

Add this code to what was previously given. On the form you pick your date ranges. This will save the individual payslips to a file and email the reports out. I left a message box to show the email worked, but you can remove that if you want. I tested replacing the employees email with my email in the table and all emails sent.

Option Compare Database
Option Explicit
Dim OutlookApp As Outlook.Application
Public Sub LoopPaySlips(startDate As Date, enddate As Date)
  Dim empID As String
  Dim EmpName As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "Select Distinct [Employee ID] from qryPaySlips where [Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(enddate, "mm/dd/yyyy") & "#"
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Set OutlookApp = New Outlook.Application
  Do While Not rs.EOF
    empID = rs![Employee ID]
    CreatePaySlip startDate, enddate, empID
    rs.MoveNext
  Loop
  Set OutlookApp = Nothing
End Sub

Public Sub CreatePaySlip(startDate As Date, enddate As Date, empID As String)
  Const rptName = "rptExportPaySlips"
  Const FolderName = "payslips"
  Dim EmpName As String
  Dim strPath As String
  Dim pathAndFile As String
  Dim strWhere As String

  strPath = CurrentProject.Path
  strPath = strPath & "\" & FolderName & "\"
  strWhere = "([Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(enddate, "mm/dd/yyyy") & "#) AND [Employee ID] = '" & empID & "'"
  EmpName = Replace(GetEmpName(empID), " ", "_")
  Debug.Print strWhere
  DoCmd.OpenReport rptName, acViewPreview, , strWhere, acHidden
  pathAndFile = strPath & EmpName & Format(enddate, "YYYYMMDD") & ".pdf"
  Debug.Print pathAndFile
  DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, pathAndFile
  SendEmail pathAndFile, empID, startDate, enddate
  DoCmd.Close acReport, rptName
End Sub
Public Function GetEmpEmail(empID As String) As String
  GetEmpEmail = Nz(DLookup("[Email Address]", "[Tubes Employees Database]", "[employee id] = '" & empID & "'"), "ID Not Found")
End Function

Public Function GetEmpName(empID As String) As String
  GetEmpName = Nz(DLookup("[Employee Name]", "[Tubes Employees Database]", "[employee id] = '" & empID & "'"), "ID Not Found")
End Function


Public Sub SendEmail(filepath As String, empID As String, startDate As Date, enddate As Date)
  Dim empEmail As String
  Dim oEmail As Outlook.MailItem

  empEmail = GetEmpEmail(empID)
  Set oEmail = OutlookApp.CreateItem(olMailItem)
  With oEmail
    .Recipients.Add empEmail
    .Subject = "Pay slip for period from " & startDate & " to " & enddate
    .Body = "Here is your pay slip. Thank you for your service to the company."
    .Attachments.Add filepath
    .Send
  End With
  MsgBox "Email sent to " & GetEmpName(empID)
  Set oEmail = Nothing
End Sub

 

[jillnoble]

Option Compare Database Option Explicit Dim OutlookApp As Outlook.Application Public Sub LoopPaySlips(startDate As Date, enddate As Date) Dim empID As String Dim EmpName As String Dim rs As DAO.Recordset Dim strSql As String strSql = "Select Distinct [Employee ID] from qryPaySlips where [Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(enddate, "mm/dd/yyyy") & "#" Debug.Print strSql Set rs = CurrentDb.OpenRecordset(strSql) Set OutlookApp = New Outlook.Application Do While Not rs.EOF empID = rs![Employee ID] CreatePaySlip startDate, enddate, empID rs.MoveNext Loop Set OutlookApp = Nothing End Sub Public Sub CreatePaySlip(startDate As Date, enddate As Date, empID As String) Const rptName = "rptExportPaySlips" Const FolderName = "payslips" Dim EmpName As String Dim strPath As String Dim pathAndFile As String Dim strWhere As String strPath = CurrentProject.Path strPath = strPath & "\" & FolderName & "\" strWhere = "([Date] between #" & Format(startDate, "mm/dd/yyyy") & "# AND #" & Format(enddate, "mm/dd/yyyy") & "#) AND [Employee ID] = '" & empID & "'" EmpName = Replace(GetEmpName(empID), " ", "_") Debug.Print strWhere DoCmd.OpenReport rptName, acViewPreview, , strWhere, acHidden pathAndFile = strPath & EmpName & Format(enddate, "YYYYMMDD") & ".pdf" Debug.Print pathAndFile DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, pathAndFile SendEmail pathAndFile, empID, startDate, enddate DoCmd.Close acReport, rptName End Sub Public Function GetEmpEmail(empID As String) As String GetEmpEmail = Nz(DLookup("[Email Address]", "[Tubes Employees Database]", "[employee id] = '" & empID & "'"), "ID Not Found") End Function Public Function GetEmpName(empID As String) As String GetEmpName = Nz(DLookup("[Employee Name]", "[Tubes Employees Database]", "[employee id] = '" & empID & "'"), "ID Not Found") End Function Public Sub SendEmail(filepath As String, empID As String, startDate As Date, enddate As Date) Dim empEmail As String Dim oEmail As Outlook.MailItem empEmail = GetEmpEmail(empID) Set oEmail = OutlookApp.CreateItem(olMailItem) With oEmail .Recipients.Add empEmail .Subject = "Pay slip for period from " & startDate & " to " & enddate .Body = "Here is your pay slip. Thank you for your service to the company." .Attachments.Add filepath .Send End With MsgBox "Email sent to " & GetEmpName(empID) Set oEmail = Nothing End Sub

Thank you so much again for holding my hand through this but I tried this and got the attached, I've tried going onto the other posts/the internet to investigate and changed bits around/add/deleted bits but I always seem to come up against other errors. Honestly, I really appreciated everything you've done and do not expect you to spend anymore of your time on it, as I said before it's a step to far for me I think. The PDF export is great, just got to try and integrate with my DB.

 

[MajP]

Sorry I forgot to mention to add a reference to Outlook. Go to Tools, References. Scroll to outlook and check it.
Should work after that. I do not think I put in any error checking for bad data so that may need to be added. Things like no email entered.