Skip a yes/no action within a loop in sending outlook email thru excel-CodePudding

Sub SendMailWithAttachment()


Dim Result As VbMsgBoxResult

Result = MsgBox("Make sure to change  Account Setting Defaults to .com" & vbNewLine & "Do you want to continue?", vbYesNo   vbQuestion   vbCritical   vbDefaultButton2, "EBLAST PRE-PROCEDURE")
If Result = vbNo Then
   Exit Sub

End If

On Error Resume Next

 
 Dim eBlast As Object
 Set eBlast = CreateObject("Outlook.Application")
 
   
 Dim r As Long
 Dim lastRow As Long
 
     lastRow = Sheets("eBLASTDATA").Range("A" & Rows.Count).End(xlUp).Row
     Dim startRecord As String
     Dim lastRecord As String
     
     startRecord = InputBox(" starting row number:", " BLAST ROW NUMBER START", 3)
        If startRecord = "" Then
           startRecord = 3
        Else
            If startRecord < 3 Then
               startRecord = 3
            End If
        End If
        
     lastRecord = InputBox(" ending row number (not less than 3):", " BLAST ROW NUMBER END", lastRow)
        If lastRecord = "" Then
            lastRecord = lastRow
        Else
            If lastRecord > lastRow Then
               lastRecord = lastRow
            End If
        End If
         
     For r = startRecord To lastRecord
         
         Dim olmail As Outlook.MailItem
         Set olmail = eBlast.CreateItem(olMailItem)
         
       Application.EnableEvents = False
       Application.ScreenUpdating = False

         
            With olmail
            
            .SentOnBehalfOfName = ".com"
            .Display
            .BodyFormat = olFormatHTML
            .To = Sheets("eBLASTDATA").Cells(r, 10).Value
            .CC = Sheets("eBLASTDATA").Cells(r, 11).Value
            .Subject = "Customer Statement as of " & Sheets("eBLASTDATA").Cells(1, 11).Value & " for Customer ID#" & Sheets("eBLASTDATA").Cells(r, 1).Value

            Dim eBlastBody1 As String
            Dim eBlastBody2 As String
            Dim Signature As String
            
         
              
            
            eBlastBody1 = "xxx"               
            eBlastBody2 = ""
            Signature = ThisWorkbook.Sheets(" Body").Cells(15, 1).Value
                      
            .HTMLBody = eBlastBody1 & eBlastBody2 & "<img src=" & Chr(34) & Signature & Chr(34) & ">"
            .Attachments.Add Sheets("eBLASTDATA").Cells(r, 12).Value
            


            '.Send
                       
           End With
    On Error Resume Next
   
    Dim TestResult As VbMsgBoxResult
    TestResult = MsgBox("Is this Test  Creation and not to be sent?", vbYesNo   vbQuestion   vbCritical   vbDefaultButton2, "EBLAST PRE-PROCEDURE")
    If TestResult = vbYes Then
    Exit Sub
        Else: olmail.Send
    End If

    
    Next r
       
    Application.EnableEvents = 1
    Application.ScreenUpdating = 1

    MsgBox "All the mails have been sent !" & vbNewLine & "Don't forget to change your  Account Settings default!"
   
        
 End Sub

How do I skip TestResult Action and continue the loop?

CodePudding user response：

Test for it not being a Test Email and just send it

Dim TestResult As VbMsgBoxResult
TestResult = MsgBox("Is this Test Email Creation and not to be sent?", vbYesNo   vbQuestion   vbCritical   vbDefaultButton2, "EBLAST PRE-PROCEDURE")
If TestResult <> vbYes Then
    olmail.Send
End If

CodePudding user response：

Your problem is this part:

 If TestResult = vbYes Then
    Exit Sub

What that's doing is, if you answer 'yes' to the messagebox (TestResult = vbYes) then it exits the macro altogether.

I'd recommend replacing that whole block with:

    Dim TestResult As VbMsgBoxResult
    TestResult = MsgBox("Is this Test  Creation and not to be sent?", vbYesNo   vbCritical   vbDefaultButton2, "EBLAST PRE-PROCEDURE")
    If TestResult = vbYes Then 'it's a test
        olmail.display 'display the email and move on.
    Else 'not a test
        olmail.Send 'send the email and move on
    End If