Export report to separate PDF's (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 19:11
Joined
Sep 21, 2011
Messages
14,046
Don't give up, that is not going to get you anywhere? :(
You have put the code in the wrong place.?
The Options near to the Sub should not be there. You only have them once and always at the top of a module.
The Dim Outlook needs to be inside that sub ?

Plus it needs to be referenced, however I cannot see you using it in that sub?, so is it meant to be public?, in which case all those three lines should replace the two at the top.?
 

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
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.

View attachment 91776
Thank you - Do you ever sleep! I've added that but still getting errors. If you're determined to help, which is appreciated, I have reattached your version with the code and reference added...
 

Attachments

  • MajPExport (1).accdb
    576 KB · Views: 118

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
Don't give up, that is not going to get you anywhere? :(
You have put the code in the wrong place.?
The Options near to the Sub should not be there. You only have them once and always at the top of a module.
The Dim Outlook needs to be inside that sub ?

Plus it needs to be referenced, however I cannot see you using it in that sub?, so is it meant to be public?, in which case all those three lines should replace the two at the top.?
OMG!! That worked! Thank you so much! 😊😊😊😊😊😊😊
 

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
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.

View attachment 91776
Don't worry about previous response, I think I've sussed it due to Gasman's comment. If I actually knew anything about code I would've realised that. I'd just like to say you're my hero!! 🦸‍♂️
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:11
Joined
May 21, 2018
Messages
8,463
Not sure if you are good to go or not. You mixed the form code with the code for the module. The solution is a working solution, so at worst it just needs some very minor tweeking. Using you db I can save and email as requested, so I know it works.
 

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
Hiya, I thought I was sorted, even did a trial run at the weekend but now coming to run them for real and getting the attached errors..

This is the code on the form (not sure I uploaded it properly)...

Any ideas please?

Option Compare Database
Option Explicit
Dim OutlookApp As Outlook.Application


Private Sub cmdSave_Click()
If IsDate(Me.txtStart) And IsDate(Me.txtEnd) And Me.txtEnd >= Me.txtStart Then
LoopPaySlips Me.txtStart, Me.txtEnd
MsgBox "Complete. Check export folder"
DoCmd.Close acForm, Me.Name
Else
MsgBox "Check valid dates"
End If
End Sub

Private Sub Form_Load()
Me.txtStart = Date - Weekday(Date, vbMonday) + 1
Me.txtEnd = Date - Weekday(Date, vbMonday) + 6
End Sub


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
 

Attachments

  • 2021-05-25 (1).png
    2021-05-25 (1).png
    97.9 KB · Views: 110
  • 2021-05-25 (2).png
    2021-05-25 (2).png
    107.3 KB · Views: 117

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:11
Joined
May 21, 2018
Messages
8,463
Did you add the reference to Outlook? Double check.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:11
Joined
Sep 21, 2011
Messages
14,046
Do you have Outlook in your references?
 

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
Did you add the reference to Outlook? Double check.
Omg! I thought I'd checked and double checked! Now getting an issue where it stop when no email address entered (we have some names on the DB which do not receive Pay slips) but not an issue, I can just put mine in there!! Thanks again
 

jillnoble

Member
Local time
Today, 19:11
Joined
Dec 7, 2020
Messages
37
Thing is, if I try and rerun it everyone up to the issue gets the same thing again! Is there anyway of putting them in my drafts so I can then send???
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:11
Joined
Sep 21, 2011
Messages
14,046
Well as you cannot send an email to someone with no email address, include that in your criteria to weed them out, then decide what you want to do with those?. Perhaps post the payslip?
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:11
Joined
Sep 21, 2011
Messages
14,046
Thing is, if I try and rerun it everyone up to the issue gets the same thing again! Is there anyway of putting them in my drafts so I can then send???
Go offline, then delete them and start again.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:11
Joined
May 21, 2018
Messages
8,463
I am on my phone so i cannot look at the code. But i think if no email found the function returns "No Email Found". So you can put an if check to not mail those. That way you can still save the file to disk.
 

Users who are viewing this thread

Top Bottom