ok, i have made a few more mods to the code, but i am still getting "run time error 3061 too few parameters expected 11". It is highlighting the line "Set rst = dbs.OpenRecordset(strSQL)" Here is my modified code. Can anyone help with this error i am receiving?
Code:
Private Sub Command65_Click()
Dim dbs As Database
Dim rst As Recordset
Dim messagebody As String
Dim strSQL As String
strSQL = "SELECT Forms![Attendance Data Entry]![Employee Name] AS [Employee Name], [Employee Data Table].EMAIL_ADDRESS, Forms![Attendance Data Entry]!Coach AS [Coach Name], Forms![Attendance Data Entry]!Combo18 AS [Exception Code], Forms![Attendance Data Entry]!Dattefield AS [Date of Exception], Forms![Attendance Data Entry]!ShiftType AS SameDay_PTO, Forms![Attendance Data Entry]!SD_ATO_StartTime AS SameDayATO_Start, Forms![Attendance Data Entry]!SD_ATO_EndTime AS SameDayATO_End, Forms![Attendance Data Entry]!SA_Start_Time AS Shift_ADJ_Start, Forms![Attendance Data Entry]!SA_EndTime AS Shift_ADJ_End, Forms![Attendance Data Entry]!Coach_Email AS Coach_Email, Forms![Attendance Data Entry]!Manager_Email AS Manager_Email"
strSQL = strSQL & " FROM [Employee Data Table]"
strSQL = strSQL & " GROUP BY Forms![Attendance Data Entry]![Employee Name], [Employee Data Table].EMAIL_ADDRESS, Forms![Attendance Data Entry]!Coach, Forms![Attendance Data Entry]!Combo18, Forms![Attendance Data Entry]!Dattefield, Forms![Attendance Data Entry]!ShiftType, Forms![Attendance Data Entry]!SD_ATO_StartTime, Forms![Attendance Data Entry]!SD_ATO_EndTime, Forms![Attendance Data Entry]!SA_Start_Time, Forms![Attendance Data Entry]!SA_EndTime, Forms![Attendance Data Entry]!Coach_Email, Forms![Attendance Data Entry]!Manager_Email; "
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
messagebody = " "
If rst![Exception Code] = "Same Day ATO" Then
messagebody = "" & rst![Employee Name] & " was given " & rst![Exception Code] & " for " & rst![Date of Exception] & " from " & rst![SameDayATO_Start] & " to " & rst![SameDayATO_End] & vbCrLf
End If
'Debug.Print messagebody
' pass details to function
xyz = Mail_report(messagebody)
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Function Mail_report(body_txt As String)
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object
Dim subject As String
Dim dbs As Database
Dim rst As Recordset
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Attendance Append")
subject = rst![Employee Name] & rst![Exception Code] & rst![Date of Exception]
On Error Resume Next
With OutMail
.To = "Email@email.com"
.CC = "" 'CC_email
.BCC = ""
.subject = subject
.Body = body_txt
'.Attachments.Add path ' path could hold a filename to attach
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Set rst = Nothing
Set dbs = Nothing
Mail_ActiveSheet = True
End Function