I need automated emails to continue after one is cancelled

[magster06]

Hello all,

I have an email alert for the user that will let him know when someones doctors notice will expire. Once the user receives the alert, he then can click "send" and Access will auto email the individuals that have slips ready to expire (this works fine).

The problem is this:

Let say the user has 3 emails to send out, but the user decides that he only needs to send one.

Right now when the user trys to cancel the emails that he does not want to send, then he receives a message that the "SendObject was cancelled" and the email process stops for the other emails.

Is there a way to cancel one email and let the other emails continue?

This is the code for generating the emails:

Dim db As DAO.Database
    Dim varTo As Variant        '-- Address for SendObject
    Dim strText As String        '-- E-mail text
    Dim strSubject As String     '-- Subject line of e-mail
    
    On Error GoTo Err_cmdSendEmail_Click
    
    Set db = CurrentDb
    
    'Subject line for the email(s) to be sent out
    strSubject = ":: Light Duty Doctors Excuse Expiration ::"
    
    'Body of the auto-generated email
    strText = "My records indicate that your doctor's excuse is 14 days from expiration." & Chr$(13) & Chr$(13) & _
         "Members that would like the Chief to consider an extension of time for their" & Chr$(13) & _
        "light-duty assignment are to submit a doctor's slip that details:" & Chr(13) & Chr(13) & Chr(13) & _
        "1. Duty Restrictions." & Chr(13) & _
        "2. Whether the disability is temporary or permanent." & Chr(13) & _
        "3. Expected date of return to full duty." & Chr(13) & Chr(13) & Chr(13) & Chr(13) & _
               "Administration Officer's Liaison"
               
       
    
    With Me.Form.RecordsetClone
        Do Until .EOF
            varTo = ![Email Address]
            DoCmd.SendObject , , acFormatTXT, varTo, , , strSubject, strText, -1
            .Edit
            ![Email Sent] = True 'this will tick the checkbox
            ![Email Sent Date] = Now()
            .Update
            .MoveNext
        Loop
    End With
    
    Exit Sub
    Set db = Nothing
    
Exit_cmdSendEmail_Click:
    Set db = Nothing
    Exit Sub
Err_cmdSendEmail_Click:
    MsgBox Err.Description
    Resume Exit_cmdSendEmail_Click

 

[pr2-eugin]

Hello magster06, The reason is because you have asked the Compiler to show what error it is and stop the whole process. What you should do is, if Cancelled was hit i.e. if error 2501 which is the SendObject was cancelled error is encountered, then what you need to do is move to the next record.. So in that case you have to rewrite your code as..

    Dim db As DAO.Database
    Dim varTo As Variant        '-- Address for SendObject
    Dim strText As String        '-- E-mail text
    Dim strSubject As String     '-- Subject line of e-mail
    
    On Error GoTo Err_cmdSendEmail_Click
    
    Set db = CurrentDb
    
    'Subject line for the email(s) to be sent out
    strSubject = ":: Light Duty Doctors Excuse Expiration ::"
    
    'Body of the auto-generated email
    strText =    "My records indicate that your doctor's excuse is 14 days from expiration." & vbCrLf & vbCrLf & _
                "Members that would like the Chief to consider an extension of time for their" & vbCrLf & _
                "light-duty assignment are to submit a doctor's slip that details:" & vbCrLf & vbCrLf & vbCrLf & _
                "1. Duty Restrictions." & vbCrLf & _
                "2. Whether the disability is temporary or permanent." & vbCrLf & _
                "3. Expected date of return to full duty." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & _
                "Administration Officer's Liaison"

    With Me.Form.RecordsetClone
        Do Until .EOF
            varTo = ![Email Address]
            DoCmd.SendObject , , acFormatTXT, varTo, , , strSubject, strText, -1
            .Edit
            ![Email Sent] = True 'this will tick the checkbox
            ![Email Sent Date] = Now()
            .Update
[COLOR=Blue][B]tryAnother:[/B][/COLOR]
            .MoveNext
        Loop
    End With

Exit_cmdSendEmail_Click:
    Set db = Nothing
    Exit Sub
Err_cmdSendEmail_Click:
   [B][COLOR=Blue] If Err.Number <> 2501 Then
        Call MsgBox("The following error Occurred" & vbCrLf & Err.Number & " : " & Err.Description)
        Resume Exit_cmdSendEmail_Click
    Else
        Resume tryAnother
    End If[/COLOR][/B]

I have highlighted the code I added.. Try it out, and see what happens..

You might also notice that I have replaced all Chr$(13)/Chr(13) with vbCrLf.. Just a better way of giving line feeds in my opinion.. You sure can stick with the Chr() but just a suggestion..

 

[magster06]

pr2-eugin,

It works great! I have also changed out all of the Chr(13)'s as you suggested.

As always, thanks for the help. It is greatly appreciated!

 

[pr2-eugin]

Happy to Help..