Send Report Through Access Form

[andrew.abaye]

Hello Folks,

I have created a form which allows me to print Access Report based on the criteria supplied on the combobox of the form which works fine. I have a second to email the report to the user and I have created a query which have the recipients email addresses. I expect the function to pick the email address of the recipient from the query and send the report. The function lunches outlook with the file in attachement but it does not pick the email address of the user from the file.

This is the code running on the Email button. I need help please.

Private Sub email_payslip_Click()
On Error Resume Next
Dim stDocName As String
Dim StaffEmail As String
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("qryemployeeEmail", dbOpenDynaset)
rs.MoveFirst

StaffEmail = rs(email)
stDocName = "EmployeePaySlip"
StaffID = Employeebox
Payserial = Payserialbox
Do
Employeebox = rs(employee_id)
Payserialbox = rs(PaYserialNumber)
'DoCmd.OpenReport stDocName, acPreview
DoCmd.SendObject acSendReport, stDocName, "PDFFormat(*.pdf)", StaffEmail, "", "", "Monthly PaySlip", "Kindly find your Monthly Payslip attached. Thank you.", False

rs.MoveNext
Loop Until rs.EOF
End Sub

 

[theDBguy]

StaffEmail = rs(email)

Maybe you meant to use?

StaffEmail = rs("email")

 

[andrew.abaye]

Maybe you meant to use?

StaffEmail = rs("email")

Thanks so much for pointing out an in my code. I have made that correction now. I now have a popup saying a program is trying to send email on your behalf which should be but when I click allow I don't see anything in outbox or in the inbox of the recipient. I have attached my final code and the screenshot here. Please help

Private Sub email_payslip_Click()
On Error Resume Next
Dim stDocName As String
Dim StaffEmail As String
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("qryemployeeEmail", dbOpenDynaset)
rs.MoveFirst

StaffEmail = rs("email")
stDocName = "EmployeePaySlip"
StaffID = Employeebox
Payserial = Payserialbox
Do
Employeebox = rs(employee_id)
Payserialbox = rs(PaYserialNumber)
'DoCmd.OpenReport stDocName, acPreview
DoCmd.SendObject acSendReport, stDocName, "PDFFormat(*.pdf)", ("email"), "", "", "Monthly PaySlip", "Kindly find your Monthly Payslip attached. Thank you.", False

rs.MoveNext
Loop Until rs.EOF
End Sub

 

[arnelgp]

i have this answer from ChatGPT:

 

[Gasman]

You would be using StaffEmail surely and not "email" which is just a literal and not even a valid email address?

 

[andrew.abaye]

You would be using StaffEmail surely and not "email" which is just a literal and not even a valid email address?

I have tried both same behaviour. It does same thing but nothing happens in my outlook and the staff member don't get any email as well

 

[andrew.abaye]

i have this answer from ChatGPT:

View attachment 114775

Thank you. I was simply saying that nothing happens on the Outlook side of thing. It least it should launch outlook if it was not open and the message should be in outbox and when it is sent it will be in sent folder

 

[Gasman]

Are your parameters correct?

DoCmd.SendObject method (Access)

Office VBA reference topic

learn.microsoft.com

The pdf part does not look correct according to that link.?
You cannot make up your own parameters.

 

[arnelgp]

Thank you. I was simply saying that nothing happens on the Outlook side of thing. It least it should launch outlook if it was not open and the message should be in outbox and when it is sent it will be in sent folder

Outlook was lunch, the mere fact that you received the Security Warning from Outlook on post #3.
you should have Click the "Allow" button on the warning message.

 

[andrew.abaye]

Outlook was lunch, the mere fact that you received the Security Warning from Outlook on post #3.
you should have Click the "Allow" button on the warning message.

I did click on the allow and I expected to see the sent message in the sent folder and in the recipient inbox which did not happen

 

[Gasman]

Are you using new outlook at all?
Have you corrected any arguments as per the link I posted?
Google the issue, there are plenty of links, and see if any might apply to you.
You could upload just enough for someone to test for you?