Invalid Next control variable reference

Hey There,

I am brand new to this forum but I was wondering if anyone can help. I keep receiving an Invalid Next control variable reference. I'm trying to completely automate an email system, I'm one final step away! I've attached my code, I keep receiving the error at the "Next Fnum" stage at the very end of the code..

Sub SendEmail()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim Addresslist As Scripting.Dictionary

'Fill in the path\folder where the files are
MyPath = "C:\Users\User\Documents\Expediting\Expediting Reports"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

Set OutApp = CreateObject("Outlook.Application")

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
On Error GoTo 0

If Not mybook Is Nothing Then
If mybook.Worksheets(1).Range("OP2").Value <> "" Then

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

For Each cell In Columns("R").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" Then
    On Error Resume Next
    Addresslist.Add cell.Value, cell.Value
    If Err.Number = 0 Then
    Set OutMail = OutApp.CreateItem(0)
    
With OutMail
.To = mybook.Worksheets(1).Range("P2").Value
.CC = cell.Value
.Subject = "Mosaic PO Expediting Report - Overdue Purchase Orders " & Format(Date, "dd/mm/yy")
.Body = "Good Day, " & vbLf _
& vbLf _
& "Please review the attached file"

.Attachments.Add mybook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'change to .Send if it works
End With
Set OutMail = Nothing

If Err.Number > 0 Then
ErrorYes = True
Err.Clear
On Error GoTo 0
End If

Else
ErrorYes = True
End If

'Save and close mybook
mybook.Close savechanges:=False
Else
'Not possible to open the workbook
ErrorYes = True
End If

Next Fnum
End If

If ErrorYes = True Then
MsgBox "There are problems in one or more files, possible problem:" _
& vbNewLine & "???????????????"
End If

Set OutApp = Nothing

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With

On Error Resume Next
Kill "C:\Users\dslack\Documents\Expediting\Expediting Reports\*.xls"
On Error GoTo 0

End Sub

It looks like you're using Fnum for 2 different methods - e.g. you have an IF statement that checks an open variable fnum, then you try to also use the same variable in a for/next loop. Try changing all the fnum's used in the for/next loop and next fnum to a unique variable name (xyz).

On a risk of sounding dumb, how exactly could I do that?

No dumb questions And actually, taking a look at this it looks like you might just have your If / End if statements placed incorrectly - e.g. you're invalid next because it can't find your initial for. If you look at your code you have 2 next/for loops. However, you 2 beggings for your foor loops (e.g. For fnum... and For each cell), but you only have 1 ending Next fnum.

Just like your If Then / End if statements have a beginning/end, so do your for/next loops.

You need to close the loop on your other For each cell loop. I'm not sure where you want that to end though. Read through your code carefully and make sure your if/end if statements are setup properly and that your for/next loops all close and process exactly what you want each of them to do.

Thanks MedPack! With your help I was able to figure out where the problem was by making sure the For/Next and If statements lined up correctly. Unfortunately the code was not doing exact what I hoped it would. I will be posting a new thread and closing this one to figure out how I can set this up to CC all email addresses in one of my worksheet columns. Thanks for your help!

Nice, glad you were able to figure out and no problem, happy to help .