Export report to separate PDF's

[Gasman]

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]

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...

 

[jillnoble]

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]

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]

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]

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

 

[MajP]

Did you add the reference to Outlook? Double check.

 

[Gasman]

Do you have Outlook in your references?

 

[jillnoble]

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]

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]

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]

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]

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.