heres the code i was using. Yesterday it worked perfectly. I have the email
in my inbox to prove it. Today when i use it i get this error. Run Time Error
1004 Application defined or object defined error. I haven't changed a thing
in the file.

Private Sub Send2_Click()

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs .Sheets(1).Range("B4").Value _
& ".xls"
ActiveWorkbook.SendMail "", _
"Inactive Employee Attendance Data"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End With
End Sub


Thanks for the help.

I realized i have no value in B4. Which leads me to this question. If there
is no value in B4 can I fix the macro to bring up a message box that says
"Name Not Defined"?

"Qaspec" wrote:

heres the code i was using. Yesterday it worked perfectly. I have the email
in my inbox to prove it. Today when i use it i get this error. Run Time Error
1004 Application defined or object defined error. I haven't changed a thing
in the file.

Private Sub Send2_Click()

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs .Sheets(1).Range("B4").Value _
& ".xls"
ActiveWorkbook.SendMail "", _
"Inactive Employee Attendance Data"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End With
End Sub


Thanks for the help.

Yes....simply add this

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
End If

How do I also get the macro to quit running if the massage box is displayed?
I'm trying to avoid the user seeing the Run Time error. The message box would
hint the user that a value needs to be placed in B4. So if no value is in B4
then the message box should come up and the macro should stop running.

"Chip" wrote:

Yes....simply add this

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
End If

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
End Sub
End If

Heres what it looks like now and i get a compile error.
Appreciate all the help.

Private Sub Send2_Click()

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
ActiveSheet.Copy
Set wb = ActiveWorkbook
If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox ("Name Not Defined")
End Sub
End If
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs .Sheets(1).Range("B4").Value _
& ".xls"
ActiveWorkbook.SendMail "", _
"Inactive Employee Attendance Data"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End With
End Sub

"Chip" wrote:

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
End Sub
End If

I was stupid...change the "End Sub" in the If Statement to "Exit Sub":

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
Exit Sub
End If

Works Perfect. Thanks.

"Chip" wrote:

I was stupid...change the "End Sub" in the If Statement to "Exit Sub":

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
Exit Sub
End If

I think that should be EXIT SUB, not End Sub.


On 24 Feb 2005 11:36:44 -0800, "Chip" wrote:

If IsEmpty(Sheets(1).Range("B4")) Then
MsgBox("Name Not Defined")
End Sub
End If