I want the email send to be able to stop at a particular place in the record

[shyley]

Hello everyone, please i need your help, i have a code in the module and am trying to be able to set the vba code to able to stop at the end of record, i have tried the the Myset.EOF which is not working for me because it keeps runing with no ending, i don't maybe theres other ways to this kindly help
this is the code
Function SendMyEmail()

Dim MySet As ADODB.Recordset

Set MySet = New ADODB.Recordset
MySet.Open "qryStaffSalary", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF


DoCmd.SendObject acReport, "rptStaffSalary", "SnapshotFormat(*.snp)", , "", "", "Payslip", "Please find attached your payslip", True, ""

MySet.MoveNext
Loop

End Function

i don't know how to explain this but if you can relate kindly help
many thanks

 

[oleronesoftwares]

Hi, the record you want it to stop at must have an identifier that will be referenced in the vba code.

Alternatively if the record has a value different from others e.g date field that is today, while others have date field before today, you can have the query "qryStaffSalary" exclude the record, by putting it in the where clause.

 

[isladogs]

Filter your query to just show the specific record required

 

[Gasman]

Another way is open the report with criteria, send, then close, repeat until EOF ?

It has been asked many times here, so a search here should bring up the method?

Edit: This is the method I was thinking of

Output to separate PDF files based on field

Hi there, A bit of background on this report. It is a payroll report that for security reasons I can't build in Cognos. The data is coming from an Excel sheet which will be updated by payroll staff and they wish the output to be in pdf format on a network drive based on the department. They...

www.access-programmers.co.uk

 

[shyley]

Hi, the record you want it to stop at must have an identifier that will be referenced in the vba code.

Alternatively if the record has a value different from others e.g date field that is today, while others have date field before today, you can have the query "qryStaffSalary" exclude the record, by putting it in the where clause.

yes, am a beginner in access, i don't know how to right it, can you help please

many thanks

 

[shyley]

My guess is that you have a table containing your staff payslip information.

You have a single report which shows the payslip details for all staff members.
It shows every payslip, but you want to send only the page that relates to the email recipient.

Is that correct? If not, please provide more information....

yes sir, its correct

 

[shyley]

Filter your query to just show the specific record required

i have filter but its not saving when i close and reopen its still go back to show everything and also the reports won't get the filter

 

[Gasman]

In this YouTube video I demonstrate how to pass a filter into a Report from a Form no code required in the Report:-

@Uncle Gizmo However in this case the criteria is coming from a recordset?

 

[Gasman]

Hi Paul,
In this example the Filter is applied to the Report, no need to change the report record source.

Nice, another way of doing it.
I like the way you are prepared to show your mistakes as well. Well done!