Compile error: Next without a For statement

zJenkins

Board Regular
Joined
Jun 5, 2015
Messages
144
I'm getting a compile error "Next without a For statement". I got the code from an outside source and was wanting to make it work with what I'm doing, but I can't seem to solve the error. I guess it has to do with the construct of the nested if...then...else...statements.

The procedure is supposed to loop through each of the checkboxes within my listbox, if the checkbox is TRUE, then an email will be sent to the user. If the checkbox is FALSE, then it skips it and goes on to the next checkbox.

Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim curColumn   As Long
Dim ctrl As Control
Dim i As Integer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem


curColumn = 1
LastRow = Worksheets("sht_data").Cells(Rows.Count, curColumn).End(xlUp).Row


    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            For i = 7 To LastRow
                If ctrl.Value = True Then

                            Set OutApp = CreateObject("Outlook.Application")
                            Set OutMail = OutApp.CreateItem(0)
                            
                            toList = Cells(i, 6)
                            eSubject = "Hotshop Metrics"
                            eBody = "Please see your attached Hotshop metrics report"
                    
                            On Error Resume Next
                    
                            With OutMail
                                .To = toList
                                .CC = ""
                                .BCC = ""
                                .Subject = eSubject
                                .BodyFormat = olFormatHTML
                                .Display
                                .HTMLBody = eBody & vbCrLf & .HTMLBody
                                '.Send
                            End With
                    
                            On Error GoTo 0
                            Set OutMail = Nothing
                            Set OutApp = Nothing
                  
                Else: Next ctrl
            Next i
        End If
    Next ctrl
End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,629
Office Version
365
Platform
Windows
Change this
Code:
Else: Next ctrl
to
Code:
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,629
Office Version
365
Platform
Windows
Or maybe
Code:
   For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
               For i = 7 To Lastrow
                

                            Set OutApp = CreateObject("Outlook.Application")
                            Set OutMail = OutApp.CreateItem(0)
                            
                            toList = Cells(i, 6)
                            eSubject = "Hotshop Metrics"
                            eBody = "Please see your attached Hotshop metrics report"
                    
                            On Error Resume Next
                    
                            With OutMail
                                .To = toList
                                .CC = ""
                                .BCC = ""
                                .Subject = eSubject
                                .BodyFormat = olFormatHTML
                                .Display
                                .HTMLBody = eBody & vbCrLf & .HTMLBody
                                '.Send
                            End With
                    
                            On Error GoTo 0
                            Set OutMail = Nothing
                            Set OutApp = Nothing
                  
               
               Next i
            End If
        End If
    Next ctrl
 

zJenkins

Board Regular
Joined
Jun 5, 2015
Messages
144
Or maybe
Code:
   For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
               For i = 7 To Lastrow
                

                            Set OutApp = CreateObject("Outlook.Application")
                            Set OutMail = OutApp.CreateItem(0)
                            
                            toList = Cells(i, 6)
                            eSubject = "Hotshop Metrics"
                            eBody = "Please see your attached Hotshop metrics report"
                    
                            On Error Resume Next
                    
                            With OutMail
                                .To = toList
                                .CC = ""
                                .BCC = ""
                                .Subject = eSubject
                                .BodyFormat = olFormatHTML
                                .Display
                                .HTMLBody = eBody & vbCrLf & .HTMLBody
                                '.Send
                            End With
                    
                            On Error GoTo 0
                            Set OutMail = Nothing
                            Set OutApp = Nothing
                  
               
               Next i
            End If
        End If
    Next ctrl

Thanks Fluff! This did the trick

I appreciate the input from Mumps as well.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,629
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,219
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top