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?
Thanks
Stuart
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: