Dear Richard Rost,

I am disparate I need your help Richard! I have a workflow written in VBA code to send emails with details about employees who are on probation.  Ideally we would like this workflow to send emails to the HR Coordinator by simply opening the relevant employee s form and clicking on the  Send Email  button. The email should pick up the Manager s name from the rst.Fields( Mgr s Name ) as set up in the code below. The problem I am facing is that if I put reference to some Form control in the Design View of the Query it does not work. I am getting the error message:  Run-time error  3061  Too few parameters. Expected 1 . However, if I but if I put reference to the value in the Query s Design View i.e. the employee number it works. The HR Co-odinator is not that technical and she does not want to go through the trouble of going to the Design View to populate a employee number each time she wants to send an email.

How to I correct the VBA code to work. Below is the VBA code.

Thanks

Shep

Private Sub Command84_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTo As String
Dim strSubject As String
Dim strMessage As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Probation Details_emailing", dbOpenForwardOnly)
Do While Not rst.EOF
strTo = rst.Fields("HR Coordinator Email")
strSubject = "Probation Report" 'could be made available
strSubject = "Probation Report" 'could be made available
strMessage = "Dear " & rst.Fields("Mgr's Name") & vbNewLine & vbNewLine & _
"Employee name probation period ends on date.  Are you happy for me to issue a congratulatory letter?" & vbNewLine & vbNewLine & "FYI - I shall send the letter out on your behalf and it will have the following wording.If you would like to amend or add any comments please include them in your reply.  I will also arrange for a copy to be placed onto the employee's personnel file." & vbNewLine & vbNewLine & _
"I am pleased to confirm that you have successfully completed your probation period with PickSP Consulting Ltd on (date will be entered by HR) and I would like to take this opportunity to wish you every success in your future employment with us." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Many Thanks for your time." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Joseph Jones" & vbNewLine & "HR Co-ordinator" & vbNewLine & vbNewLine & "222222/ 7777777777" & vbNewLine & "Email: [email protected]"

DoCmd.SendObject acQuery, "Probation Details_emailing", "ExcelWorkbook(*.xlsx)", strTo, "", "", "Probation Report", strMessage, False, ""
  
  rst.MoveNext
  Loop
'close the objects
  rst.Close
  dbs.Close
  
'destroy the variables
  Set rst = Nothing
  Set dbs = Nothing

End Sub

---

Reply from Alex Hedley:

Hi Shep,

Firstly try not to have spaces in your FIELD and QUERY names, it can cause issues when used in code.

I'd change the FIELD from ("Mgr's Name") to ("MgrsName") in the TABLE.

Does the QUERY "Probation Details_emailing" contain these Fields you are trying to access in your Recordset?