+ Reply to Thread
Results 1 to 8 of 8

Application-Defined or Object-Defined error

Hybrid View

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Application-Defined or Object-Defined error

    I am trying to adapt a loop I used in a different sub to look through my sheet and find the rows where a conditional formatting rule I made has highlighted the cells yellow and send an email out. However, I am getting an application-defined or object-defined error when I try to run it. Any suggestions on how to fix this? Thank you in advance!

    
    Sub Reminder()
        Sheets("INVOICES").Activate
        
        Dim start_cell As Range
        Dim sheet As Worksheet
        Set sheet = Worksheets("INVOICES")
        Set start_cell = sheet.Range("B1")
        Dim i As Integer
        
        Dim rng As Range
        Dim iMsg As Object
        Dim iConf As Object
        Dim Flds As Variant
        Dim TextBody As String
        Dim ApprovedTextBody As String
        Dim Finfo As String
        Dim FilterIndex As Integer
        Dim FileName As Variant
        Dim Title As String
        
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        
        Finfo = "All Files (*.*),*.*"
        Title = "E-Mail Attachment: Select file to attach."
    
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
    
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
    
     'mail server details
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mail_server
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
        
    Do While Len(start_cell.Cells(i, 1).Value) > 0
            If cells("B:U").FormatConditions(1).Formula1 Then
    
                TextBody = "Hello," & vbNewLine & vbNewLine & _
                    "The attached invoice is still awaiting approval. Kindly review and approve it as " & _
                    "soon as you get a chance. " & vbNewLine & vbNewLine & Cells(start_cell, 5).Text & _
                    " Inv " & Cells(start_cell, 4).Text & vbNewLine & "JSID " & _
                    Cells(start_cell, 1).Text & vbNewLine & Cells(start_cell, 9).Text & vbNewLine & _
                    Cells(start_cell, 6).Text & " " & Cells(start_cell, 7).Text & vbNewLine & vbNewLine & _
                    "Thank you," & vbNewLine & "cschoyer"
        
                    With iMsg
                    Set .Configuration = iConf
                        .To = Cells(start_cell, 12).Text + "@xyz.com"
                        .CC = "cschoyer@xyz.com"
                        .BCC = ""
                        .From = """cschoyer"" <cschoyer@xyz.com>"
                        .Subject = "Pending Approval " & Cells(start_cell, 5).Text & " Inv " & _
                                Cells(start_cell, 4).Text & " JSID " & Cells(start_cell, 1).Text
                        .TextBody = TextBody
                        .AddAttachment invoice_file_name
                        .Send
                    End With
            End If
        i = i + 1
        Loop
        
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    I get the bug on the line "Do While Len(start_cell.Cells(i, 1).Value) > 0"

    Thank you again for any help !

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Application-Defined or Object-Defined error

    The variable i is not initialized, so it's zero, which is invalid when you're starting in row 1.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Application-Defined or Object-Defined error

    I have changed start_cell to B3 (where my data starts) but I am still getting the error. I think its because of the "If Range("B:U").FormatConditions(1).Formula1 Then" line. I am trying to find the rows that meet my conditional formatting rule, but I'm not sure how to call upon the rule. Is it possible to name conditional formatting rules?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Application-Defined or Object-Defined error

    I have changed start_cell to B3 (where my data starts) but I am still getting the error.
    You're now getting an error on a different line of code, no?

  5. #5
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Application-Defined or Object-Defined error

    I'm sorry. Yes, now I am getting a type mismatch error on the line: "If Range("B:U").FormatConditions(1).Formula1 Then"

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Application-Defined or Object-Defined error

    It's very messy trying to figure out what conditional formatting is doing for the general case. I suggest you duplicate whatever logic is used in conditional formatting in code.

  7. #7
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Application-Defined or Object-Defined error

    This code works! Thanks for your advise.

    Sub Reminder()
        Dim i As Integer
        Dim Sendmsg
        Dim rng As Range
        Dim iMsg As Object
        Dim iConf As Object
        Dim Flds As Variant
        Dim TextBody As String
        Dim ApprovedTextBody As String
        Dim Finfo As String
        Dim FilterIndex As Integer
        Dim FileName As Variant
        Dim Title As String
        
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        
        Finfo = "All Files (*.*),*.*"
        Title = "E-Mail Attachment: Select file to attach."
    
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
    
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
    
     'mail server details
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mail_server
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
    
        i = 1
        Do While Worksheets("INVOICES").Cells(i, 3) <> ""
            If Worksheets("INVOICES").Cells(i, 12).Value <= Date + 7 And Worksheets( _
            "INVOICES").Cells(i, 18) = "" Then
            
                Worksheets("INVOICES").Rows(i).Interior.Color = vbYellow
    
                TextBody = "Hello," & vbNewLine & vbNewLine & _
                    "The attached invoice is still awaiting approval. Kindly advise if this invoice is " & _
                    "approved for payment as soon as you get a chance. " & vbNewLine & vbNewLine & _
                    Worksheets("INVOICES").Cells(i, 6).Text & " Inv " & _
                    Worksheets("INVOICES").Cells(i, 5).Text & vbNewLine & "JSID " & _
                    Worksheets("INVOICES").Cells(i, 3).Text & vbNewLine & _
                    Worksheets("INVOICES").Cells(i, 11).Text & vbNewLine & _
                    Worksheets("INVOICES").Cells(i, 7).Text & " " & Worksheets("INVOICES").Cells(i, 8).Text & _
                    vbNewLine & vbNewLine & "Thank you," & vbNewLine & "cschoyer"
        
                    With iMsg
                    Set .Configuration = iConf
                        .To = Worksheets("INVOICES").Cells(i, 13).Text + "@xyz.com"
                        .CC = "cschoyer@xyz.com"
                        .BCC = ""
                        .From = """cschoyer"" <cschoyer@xyz.com>"
                        .Subject = "Pending Approval " & Worksheets("INVOICES").Cells(i, 6).Text & " Inv " & _
                                 Worksheets("INVOICES").Cells(i, 5).Text & " JSID " & _
                                 Worksheets("INVOICES").Cells(i, 3).Text
                        .TextBody = TextBody
                        .Send
                    End With
            End If
        i = i + 1
        Loop
                Sendmsg = MsgBox(Prompt:="Are you sure you're ready to send?", _
                Buttons:=vbYesNo, Title:="Warning")
                If Sendmsg = vbNo Then
                Exit Sub
                Else
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
                End If
    End Sub

  8. #8
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Application-Defined or Object-Defined error

    I am working on that now. Thanks for the advise! I will post back once (if) I get it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1