'For without Next' error -Email/Message Box Combination

*shudder*

Active Member
Joined
Aug 20, 2009
Messages
489
Hi Guys,

I have built the following macro by but get the message 'For without Next' indicated on the End Sub line. I can't seem to figure out what the problem is using 'Help', any ideas on why this is tripping out?

Code:
Private Sub Workbook_Open()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
Ans = MsgBox("Your declaration is now due to be sent, do you wish to send this now ?", vbQuestion + vbYesNo)
If Ans = vbYes Then
    With Sheets("Sheet1")
For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value = "Yes" _
           And LCase(Cells(cell.Row, "F").Value) <> "sent" Then
            Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = ThisWorkbook.Sheets("Sheet2").Range("D3").Value
        .CC = ""
        .Subject = ThisWorkbook.Sheets("Sheet2").Range("A1").Value
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0
    Cells(cell.Row, "F").Value = "sent"
    Set OutMail = Nothing
    Set OutApp = Nothing
    End If
End Sub

Thanks

Stuart
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Is it?

Code:
Private Sub Workbook_Open()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim Ans As VbMsgBoxResult
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
Ans = MsgBox("Your declaration is now due to be sent, do you wish to send this now ?", vbQuestion + vbYesNo)
If Ans = vbYes Then
    With Sheets("Sheet1")
        For Each cell In .Columns("E").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value = "Yes" And LCase(.Cells(cell.Row, "F").Value) <> "sent" Then
                Set OutMail = OutApp.CreateItem(0)
            End If
        Next cell
    End With
    On Error Resume Next
    With OutMail
        .To = ThisWorkbook.Sheets("Sheet2").Range("D3").Value
        .CC = ""
        .Subject = ThisWorkbook.Sheets("Sheet2").Range("A1").Value & " - Vehicle Changes"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0
    Cells(cell.Row, "F").Value = "sent"
    Set OutMail = Nothing
    Set OutApp = Nothing
End If
End Sub
 

*shudder*

Active Member
Joined
Aug 20, 2009
Messages
489

ADVERTISEMENT

Unfortunately not :mad:

I have already got a working email macro within this workbook, can I utilise the message box answer to trigger another macro?

Something like this:

Code:
Private Sub Workbook_Open()
 
    Dim cell As Range
    Dim Ans As VbMsgBoxResult
 
Ans = MsgBox("Your declaration is now due to be sent, do you wish to send this now ?", vbQuestion + vbYesNo)
If Ans = vbYes Then
    With Sheets("Sheet1")
    Column E = "yes" & Comumn F <> "Sent" Then
    'Send Macro - Sub Email()
    Cells(cell.Row, "F").Value = "Sent"
 
End If
End Sub

Column B - I have a date
Column E - I have a date formula based on B to trigger yes/no
Column F - Macro puts 'sent' into relevant cell if yes is selected through the messge box

Thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If column E contains formulas that explains the previous error (there are no constants in column E).

Try

Rich (BB code):
For Each cell In .Columns("E").Cells.SpecialCells(xlCellTypeFormulas)
 

*shudder*

Active Member
Joined
Aug 20, 2009
Messages
489

ADVERTISEMENT

Thanks Peter, that now accomplishes creation of the email however it is still bugging out on this line:

Code:
Cells(cell.Row, "F").Value = "sent"

I have tried amending to:

Code:
ActiveWorkbook.Sheets("Sheet1").Cells(cell.Row, "F").Value = "sent"

To no avail, any suggestions?

Cheers

Stuart
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
What is the error code/message?

Is the sheet protected?

Try adding this line before that line

Code:
MsgBox cell.Row
 

*shudder*

Active Member
Joined
Aug 20, 2009
Messages
489
Tried that and came up with the same error code:

Error ‘91’
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Object variable or With Block variable not set

and no the sheet is not protected (I have learnt to not protect or hide sheets until I know the macro works lol)
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I'm afraid I'm a bit stumped. Did you try the MsgBox?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top