vba: 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 Email Account Setting Defaults to email.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("Email starting row number:", "EMAIL BLAST ROW NUMBER START", 3)
    If startRecord = "" Then
       startRecord = 3
    Else
        If startRecord < 3 Then
           startRecord = 3
        End If
    End If
    
 lastRecord = InputBox("Email ending row number (not less than 3):", "EMAIL 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 = "email.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("eMail 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 Email 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 successfully!" & vbNewLine & "Don't forget to change your Email 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