Send looping Email (1 Viewer)

[looma123]

Hi, iam using the below code to send Monthly salary slips to employees, and it working fine but having 2 issues
1. if the employee email in null, its stopping every thing ( need to edit to ignore if email not avaliable or at least if i closed the outlook email it will continues)
2. added a command at the end "DoCmd.OpenQuery "dbo_SalarySheet_Detail_Query_delete", i want it to work if any issues happened during the process
3. for each employee i have to confirm sending in outlook,, is there any way to make it automatic

Private Sub Command0_Click()
DoCmd.OpenQuery "dbo_SalarySheet_Detail_Query_append"

Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String

Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [Email] FROM [dbo_SalarySheet_Detail_Query]", dbOpenSnapshot)



Debug.Print strTo

With rsAccountNumber

Do Until .EOF

DoCmd.OpenReport "Salary Slip", _
acViewPreview, _
WhereCondition:="EmployeeID = '" & !EmployeeID & "'", _
WindowMode:=acHidden

strTo = ![Email]
strSubject = "Salary Slip for the month of " & Month & ", " & Year & " "
strMessageText = "Dear Employee," & vbCrLf & "" & vbCrLf & "I trust this message finds you in good spirits. Attached herewith is your monthly salary Slip for the month of " & Month & ", " & Year & ". Kindly review the enclosed document to ensure the accuracy of all details. If you have any queries or require clarification regarding your salary or any related matter, please don't hesitate to contact the HR department." & vbCrLf & " " & vbCrLf & "We sincerely appreciate your ongoing commitment and efforts towards our organization. Your contributions are invaluable to our team. " & vbCrLf & "" & vbCrLf & "Warm regards," & vbCrLf & " " & vbCrLf & "HR Team"


DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="Salary Slip", _
OutputFormat:=acFormatPDF, _
to:=strTo, _
Subject:=strSubject, _
MessageText:=strMessageText, _
EditMessage:=True


DoCmd.close acReport, "Salary Slip", acSaveNo


.MoveNext

Loop

.close

End With
DoCmd.OpenQuery "dbo_SalarySheet_Detail_Query_delete"
End Sub

 

[arnelgp]

1. test for Null email address:

Private Sub Command0_Click()
DoCmd.OpenQuery "dbo_SalarySheet_Detail_Query_append"

Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String

Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [Email] FROM [dbo_SalarySheet_Detail_Query]", dbOpenSnapshot)



Debug.Print strTo

With rsAccountNumber

Do Until .EOF
If Not IsNull(!Email) Then
DoCmd.OpenReport "Salary Slip", _
acViewPreview, _
WhereCondition:="EmployeeID = '" & !EmployeeID & "'", _
WindowMode:=acHidden

strTo = ![Email]
strSubject = "Salary Slip for the month of " & Month & ", " & Year & " "
strMessageText = "Dear Employee," & vbCrLf & "" & vbCrLf & "I trust this message finds you in good spirits. Attached herewith is your monthly salary Slip for the month of " & Month & ", " & Year & ". Kindly review the enclosed document to ensure the accuracy of all details. If you have any queries or require clarification regarding your salary or any related matter, please don't hesitate to contact the HR department." & vbCrLf & " " & vbCrLf & "We sincerely appreciate your ongoing commitment and efforts towards our organization. Your contributions are invaluable to our team. " & vbCrLf & "" & vbCrLf & "Warm regards," & vbCrLf & " " & vbCrLf & "HR Team"


DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="Salary Slip", _
OutputFormat:=acFormatPDF, _
to:=strTo, _
Subject:=strSubject, _
MessageText:=strMessageText, _
EditMessage:=True


DoCmd.close acReport, "Salary Slip", acSaveNo

End If
.MoveNext

Loop

.close

End With
DoCmd.OpenQuery "dbo_SalarySheet_Detail_Query_delete"
End Sub

2. i don't know what you mean
3. this can stall your e-mail sending. imagine if you have many employees, each email needs your confirmation.
better to have a listbox on your form of all employees and only print those been selected on the list.

 

[June7]

1. Exclude records from recordset that don't have email: WHERE email IS NOT NULL
2. Why? This is a DELETE query? Why remove records? Why remove if any issues happened?
3. What do you mean by "confirm" - a MsgBox to accept/cancel or save data into table that send was successful?

 

[looma123]

thanks worked well. question no 2 and 3 solved by your suggestion for question no 2