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

[*shudder*]

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?

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

 

[VoG]

Is it?

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*]

Thanks Peter,

That gets me to the message box, then bugs out on this line:

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

Do I need to specify the sheet where the value is to be placed?

I got the macro from here and modified to my requirements:

http://www.rondebruin.nl/mail/folder3/message.htm - Example 2

Cheers

Stuart

 

[VoG]

Does it create an e-mail?

 

[*shudder*]

Unfortunately not

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:

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]

If column E contains formulas that explains the previous error (there are no constants in column E).

Try

For Each cell In .Columns("E").Cells.SpecialCells(xlCellTypeFormulas)

 

[*shudder*]

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

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

I have tried amending to:

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

To no avail, any suggestions?

Cheers

Stuart

 

[VoG]

What is the error code/message?

Is the sheet protected?

Try adding this line before that line

MsgBox cell.Row

 

[*shudder*]

Tried that and came up with the same error code:

Error ‘91’
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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)

 

[VoG]

I'm afraid I'm a bit stumped. Did you try the MsgBox?