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

 

[DataBlake]

what is highlighted when the error pops up?

 

[COwen]

Do you have the Microsoft Outlook Object Library enabled?

 

[COwen]

If not, follow the steps in the screen shots...

 

[Damian37]

what is highlighted when the error pops up?

Hi DataBlake,
I got the automatic emails to work, however an email is created for every instance that meets one criteria within the AllData tab. I would like just one email to be created to let the recipients know they have missing data. I would also like the file referenced to be attached to the email. When I attempt to add the following code:

.Attachments.Add ActiveWorkbook.FullName

I receive the following error: Compile error: Invalid or unqualified reference. I've pasted the entire code below:

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, 1).Offset(0, -0)) > 0 Then
     If MailDest = "" And Cells(iCounter, 28).Offset(0, -23) = "Open" _
     And Cells(iCounter, 28).Offset(0, -20) = "" 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
    End With
    
    .Attachments.Add ActiveWorkbook.FullName
    
    
        
      End If
        
    End If
    
        Next iCounter
        
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
 

End Sub

When I attempt to add another criteria, I no longer get the emails.

 

[COwen]

Your

.Attachments.Add ActiveWorkbook.FullName

Is outside of the With loop. Try adding it underneath .Display.

 

[Damian37]

Your

Is outside of the With loop. Try adding it underneath .Display.

Can you tell what I'm doing wrong by trying to add more than one criteria in order to create an email? When I only had the code below:

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

it would create emails for every instance there was an "Open" status, however, I need it to only reference records where the status is Open and there is no information within column H which is the POC email?

 

[COwen]

So, you only need the email to tell you when there is a cell(s) with open status---not give you a rundown of every instance?

 

[Damian37]

Your

Is outside of the With loop. Try adding it underneath .Display.

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

 

[Damian37]

So, you only need the email to tell you when there is a cell(s) with open status---not give you a rundown of every instance?

I need an emal when there's an open status, and there's a blank cell in column H within the same row.