emailing worksheet

[Oreg]

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 = "jbrack@att.com"
.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

[Bunter_22@hotmail.com]

Hi Oreg,

The reason that your message box's do not work is that you don't use
response what you would use is say "if msgbox("...") = vbno then" etc.

What I have done is rewritten the first few lines of your code to
incorperate the msgbox problem and then the whole column porblem as
below:

Private Sub CommandButton2_Click()
Dim ErrorExists As Boolean
Range("H1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "ERROR" Then
ErrorExists = True
End If
ActiveCell.Offset(1, 0).Select
Loop
If ErrorExists = True Then
If MsgBox("Errors still exist. Are you sure you want to mail
out?", _
vbYesNo) = vbNo Then End
End if
End if
'All of the rest of your code.

What this should do is run through column H beginning at H1 until it
hits a cell that has no information in it. If it finds any errors it
displays your message box requesting if the user wants to end. If they
don't it then runs through the rest of your code.

Any problems with it then give me a shout.

James

[Oreg]

Bunter_22,

Thanks for the help. Works great. Solved all my troubles.

Oreg