Run-time error '424': Object required

[Damian37]

Hello all,
I'm attempting to have automatic emails be sent out with an attachment, however, when I attempt to run the code I receive a Run-time error. I've changed my code around, and I'm unsure what object missing. I essentially want all stores with a status of open that doesn't currently have a POC name or POC email within columns G & H. If those two criteria are met, then an automatic email will be created using the email addresses within columns S & U. Unsure what I'm missing. The code I'm using is below:

Private Sub CommandButton1_Click()

  Dim OutLookApp As Object
  Dim OutLookMail As Object
  Dim cell As Range
 
  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMail = OutLookApp.CreateItem(0)
 

  Worksheets("AllData").Activate

 
  For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value = "Open" And _
        LCase(Cells(cell.Row, "G").Value) = "" Then
        With OutMail
        .To = Cells(cell.Row, "S").Value
        .CC = Cells(cell.Row, "U").Value
        .BCC = "John_Doe@generic.com; John_Doe2@generic.com;"
        .Subject = "Missing store POC"
        .HTMLBody = "To Whom It May Concern,<p>" _
            & "Please be advised the store POC information is currently missing for stores in your area. " _
            & "If available, please provide current store POC information in order" _
            & "for automatic emails to be sent to the correct store POC.<p>" _
            & "Please note: If the store POC field remains empty, all emails will be" _
            & "directed to ROMs and FMMs.<p>" _
            & "Please email updated documentation to John_Doe@generic.com<p>" 
            
    
    .Display
    '.Send
    End With
     .Attachments.Add ActiveWorkbook.FullName
    
    Set OutLookMail = Nothing
    
 
        End If
        
Next cell
    'End If
    
        'Next iCounter
cleanup:
Set OutLookApp = Nothing
Application.ScreenUpdating = True
 

End Sub

All help is greatly appreciated!!! Thank you!

D

 

[COwen]

First of all, you have

If MailDest = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" Then

But you already made MailDest = "" above so that is a little bit redundant.

 

[Damian37]

First of all, you have

But you already made MailDest = "" above so that is a little bit redundant.

I just changed that to:

If Cells(iCounter, 28).Offset(0, -20) = "" And Cells(iCounter, 28).Offset(0, -23) = Open Then

Set OutlookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem

However, that didn't seem to do anything.

 

[COwen]

Try changing it back to the previous but omitting the part about MailDest. Try not to check for multiple criteria in one if statement. I have found that to cause problems in the past with my own code.

 

[DataBlake]

you can loop through your code using F8
see why the if statement is not being activated
my guess would be that both conditions are not being being met
either Cells(iCounter, 28).Offset(0, -20) is not = to "" (maybe you had a formula that you used "" for instead of NA() )
or the Cells(iCounter, 28).Offset(0, -23) is not = open

also is open a string?

If Cells(iCounter, 28).Offset(0, -20) = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" Then

 

[Damian37]

I've just tried to save the code within a commandbutton, however when I click on the command button, all it does is direct me to the AllData tab, as though it's beginning to run through to locate all records that meet the criteria, but it just stays on the tab. I've also tried to run the code directly from VBA, and nothing happens. Here's the code I'm using:

Private Sub CommandButton1_Click()

  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim iCounter As Integer
  Dim MailDest As String
  Dim MailDest2 As String

  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)


  Worksheets("AllData").Activate


  For iCounter = 1 To WorksheetFunction.CountA(Columns(28))
  'For Each cell In Worksheets("Data").Columns(32).Cells.SpecialCells(xlCellTypeConstants)

     MailDest = ""

     If Len(Cells(iCounter, 28).Offset(0, -1)) > 0 Then
     If MailDest = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" Then

        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
       
        MailDest = Cells(iCounter, 19).Value
        MailDest2 = Cells(iCounter, 21).Value
       
        .To = MailDest
        .CC = MailDest2
        .BCC = "John_Doe@generic.com; John_Doe2@generic.com"
        .Subject = "Missing store POC"
        .HTMLBody = "To Whom It May Concern,<p>" _
            & "Please be advised the store POC information is currently missing for stores in your area. " _
            & "If available, please provide current store POC information in order" _
            & "for automatic emails to be sent to the correct store POC.<p>" _
            & "Please note: If the store POC field remains empty, all emails will be" _
            & "directed to ROMs and FMMs.<p>" _
               
    .Display
    .Attachments.Add ActiveWorkbook.FullName
   
    End With
         
      End If
       
    End If
   
        Next iCounter
       
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub

I managed to get the multiple emails back, and they now have the attachment file to go with them, however, the code isn't working when I click on the button. Nor does it work when I try to run it directly in the editor for Sheet1 code. It only works when I run it directly through the editor, but within the module. Is there a way to get the button I've created to point to the code within the module1 code?

 

[Damian37]

you can loop through your code using F8
see why the if statement is not being activated
my guess would be that both conditions are not being being met
either Cells(iCounter, 28).Offset(0, -20) is not = to "" (maybe you had a formula that you used "" for instead of NA() )
or the Cells(iCounter, 28).Offset(0, -23) is not = open

also is open a string?

If Cells(iCounter, 28).Offset(0, -20) = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" Then

The code seems to only work when I keep the redundant MailDest = "" within the code. If I try to change it to another iCounter code, it doesn't work. I'm fine keeping it with the MailDest, but I would very much like to only have 1 email go to all recipients than to have multiple emails sent to each recipient. Any ideas?

 

[COwen]

Is there a way to get the button I've created to point to the code within the module1 code?

Put your code into a standard module and then assign it to a button on the worksheet (.Form control or Shape [like a rectangle]).

 

[COwen]

You have a for next loop that is sending an email every time "Done" is found. Try using the following code:

Dim SendMail as Boolean 'before any macros use the dim statement.

Sub Find_Done_Send_Email()

  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim iCounter As Integer
  Dim MailDest As String
  Dim MailDest2 As String

  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)


  Worksheets("AllData").Activate
Sendmail = False

  For iCounter = 1 To WorksheetFunction.CountA(Columns(28))
  'For Each cell In Worksheets("Data").Columns(32).Cells.SpecialCells(xlCellTypeConstants)

     MailDest = ""

     If Len(Cells(iCounter, 28).Offset(0, -1)) > 0 Then
     If MailDest = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" Then
    SendMail = True
     End If
     
    End If
    Next iCounter
   
    If SendMail = True then
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
     
        MailDest = Cells(iCounter, 19).Value
        MailDest2 = Cells(iCounter, 21).Value
     
        .To = MailDest
        .CC = MailDest2
        .BCC = "John_Doe@generic.com; John_Doe2@generic.com"
        .Subject = "Missing store POC"
        .HTMLBody = "To Whom It May Concern,<p>" _
            & "Please be advised the store POC information is currently missing for stores in your area. " _
            & "If available, please provide current store POC information in order" _
            & "for automatic emails to be sent to the correct store POC.<p>" _
            & "Please note: If the store POC field remains empty, all emails will be" _
            & "directed to ROMs and FMMs.<p>" _
             
    .Display
    .Attachments.Add ActiveWorkbook.FullName
 
    End With
    End If
   
 
       
     
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub

This way it should only send one email if done is found.

Let me know how this works.

 

[Damian37]

Put your code into a standard module and then assign it to a button on the worksheet (.Form control or Shape [like a rectangle]).

I've tried pointing the module to the button I've created, however nothing I've tried has worked.

 

[COwen]

Is your button ActiveX or Form control?