Hello,

I am having trouble altering some code below to search column H of
spreadsheet CIRCUIT LIST for any cells whose value is "errror". And if
any are found, to create a message box giving the user the option to
send an email or correct the errors. Currently with the code below, it
works searching a specific cell only (H9). How can I search column H?


Also, the If response code below is not working. No matter if the yes
or no button is selected on the Message box, the email is still
displayed. What am I doing wrong ????

Thanks,

Oreg


Private Sub CommandButton2_Click()
If Sheets("CIRCUIT LIST").Range("H9").Value = "ERROR" Then
MsgBox "Errors still exist. Are you sure you want to mail out?",
vbYesNo

If Response = vbNo Then Exit Sub
Else
If Response = vbYes Then Resume Next
End If
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
Sheets("CIRCUIT_LIST").Visible = True
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
With Sheets("CIRCUIT_LIST").PageSetup
..Zoom = 90
..FitToPagesWide = 1
..FitToPagesTall = 1
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$27"
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$60"
Sheets("CIRCUIT_LIST").Copy
Set wb = ActiveWorkbook
With wb
..SaveAs strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
..To = "
..CC = ""
..BCC = ""
..Subject = "CIRCUITS AFFECTED / AT RISK"
..Body = Sheets("CIRCUIT_LIST").Range("Z24").Value
..Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
..Display 'or use .Display
End With
..ChangeFileAccess xlReadOnly
Kill .FullName
..Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Sheets("CIRCUIT_LIST").Visible = False
End Sub


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=385126