Range Validation Error

[randersonTM]

Trying to valdate a couple of cells before I allow the file to be saved and sent via email.

I keep getting the error:
(References the line of code "PName = Range("A3:D3") on Debug)

Run-time error '91':
Object variable or With block variable not set

Not sure what my error is or if there's an easier way to validate the cells.
Any help is greatly appreciated.

Thanks

Sub Email_Sheet()
   Dim oApp As Object
   Dim oMail As Object
   Dim LWorkbook As Workbook
   Dim LFileName As String
   Dim PName As Range
   Dim PCode As Range
   Dim PLot As Range
   Dim PPlan As Range
   Dim PElev As Range
      
   PName = Range("A3:D3")
   PCode = Range("F3:G3")
   PLot = Range("I3:J3")
   PPlan = Range("L3")
   PElev = Range("N3")
     
   If ActiveSheet.PName.Value = "" Then
     MsgBox "Cannot send until required cells have been completed!"
     Cancel = True
      
   ElseIf ActiveSheet.PCode.Value = "" Then
    MsgBox "Cannot send until required cells have been completed!"
    Cancel = True
   
   ElseIf ActiveSheet.PLot.Value = "" Then
    MsgBox "Cannot send until required cells have been completed!"
    Cancel = True
   
   ElseIf ActiveSheet.PPlan.Value = "" Then
    MsgBox "Cannot send until required cells have been completed!"
    Cancel = True
   
   ElseIf ActiveSheet.PElev.Value = "" Then
    MsgBox "Cannot send until required cells have been completed!"
    Cancel = True
      
   Else
   
   LFileName = LWorkbook.Worksheets(1).Name
   On Error Resume Next
   Kill LFileName
   On Error GoTo 0
   LWorkbook.SaveAs Filename:=LFileName
   
   Set oApp = CreateObject("Outlook.Application")
   Set oMail = oApp.CreateItem(0)
   
   With oMail
      .To = "EMAIL"
      .Subject = "Field Purchasing Error Form - Test Example"
      .body = "The file is attached"
      .Attachments.Add LWorkbook.FullName
      .Send
   End With
   
   LWorkbook.ChangeFileAccess Mode:=xlReadOnly
   Kill LWorkbook.FullName
   LWorkbook.Close SaveChanges:=False
   
   Application.ScreenUpdating = True
   Set oMail = Nothing
   Set oApp = Nothing
   
   End If
   
End Sub

 

[Smitty]

You can check the count of filled cells in those ranges:

PName = Application.WorksheetFunction.CountA(Range("A3:D3"))
If PName < 4 then

HTH,

 

[Rick Rothstein]

Trying to valdate a couple of cells before I allow the file to be saved and sent via email.

I keep getting the error:
(References the line of code "PName = Range("A3:D3") on Debug)

Run-time error '91':
Object variable or With block variable not set

Not sure what my error is or if there's an easier way to validate the cells.
Any help is greatly appreciated.

Thanks

Sub Email_Sheet()
   .....
   .....
   Dim PName As Range
   Dim PCode As Range
   Dim PLot As Range
   Dim PPlan As Range
   Dim PElev As Range
      
   Set PName = Range("A3:D3")
   Set PCode = Range("F3:G3")
   Set PLot = Range("I3:J3")
   Set PPlan = Range("L3")
   Set PElev = Range("N3")
   ....
   ....   
End Sub

Objects, such as Range variables do not have values assigned to them, they have references set to them... you are missing the Set keyword that indicates this to VB.