Run-time error '424': Object required

D

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365
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:

VBA Code: 
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
 
Sort by date Sort by votes
COwen said:
You have a for next loop that is sending an email every time "Done" is found. Try using the following code:
VBA 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.
Click to expand...
I'm going through the code using F8, but no emails are being created. It just keeps going to Next iCounter, and then loops again without ever hitting the email body. I tried moving the Next iCounter line below the last End If, but that doesn't seem to make any difference.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Damian37 said:
I'm going through the code using F8, but no emails are being created. It just keeps going to Next iCounter, and then loops again without ever hitting the email body. I tried moving the Next iCounter line below the last End If, but that doesn't seem to make any difference.
Click to expand...
Yes, it will loop through all of the iCounter cells until it has gone through all of them.

Damian37 said:
ActiveX
Click to expand...
Go to the developer tab click "Design Mode" and delete your button. Click Insert (in the Developer tab and insert a form control button (the top options) and assign the macro to it. Use the following code to test.

VBA 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
    Else
    MsgBox "'Done' was not found."
    End If
 

      
    
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub
 
Upvote 0
COwen said:
Yes, it will loop through all of the iCounter cells until it has gone through all of them.


Go to the developer tab click "Design Mode" and delete your button. Click Insert (in the Developer tab and insert a form control button (the top options) and assign the macro to it. Use the following code to test.

VBA 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
    Else
    MsgBox "'Done' was not found."
    End If


    
  
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub
Click to expand...
Thank you for the code changes. I updated my code, and now the To and Cc fields are blank within the email. I changed around my original code, and I have it creating only 1 email, however, it only chooses the first sets of email addresses within column 19 and 21. For some reason it doesn't search through all of the data to create emails for the other email addresses within the data. The email address are based on individual stores. 10 stores may have the same email addresses within column 19 and 21, but the 11th store will have different values within those columns. Is there anything in the following code that you can see that would stop the code searching through all the data?
VBA Code: 
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(29))

     'MailDest = ""

     If Len(Cells(iCounter, 29).Offset(0, -0)) > 0 Then
     If MailDest = "" And Cells(iCounter, 29).Offset(0, -0) = "True" Then

        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
       
        MailDest = Cells(iCounter, 19).Value
        MailDest2 = Cells(iCounter, 21).Value
       
        .To = MailDest
        .CC = MailDest2
        .BCC = "Damian_Velez@cable.comcast.com; Casey_Montgomery@comcast.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 xyz@cable.abc.com<p>" _
            & "Thank You,<p>" & "<b>xyz</b><br>" _
            & "Sr. Analyst, Mobile Logistics<br>" & "abc- Headquarters<br>" _
            & "5800 S some St.<br>" & "Greenwood Village, CO. 80111<br>" _
            & "Phone: "
   
    .Display
     .Attachments.Add ActiveWorkbook.FullName
   
    End With
           
   
    '.Send

        End If
       

    End If
   
        Next iCounter
       
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub
 
Last edited by a moderator:
Upvote 0
COwen said:
Yes, it will loop through all of the iCounter cells until it has gone through all of them.


Go to the developer tab click "Design Mode" and delete your button. Click Insert (in the Developer tab and insert a form control button (the top options) and assign the macro to it. Use the following code to test.

VBA 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
    Else
    MsgBox "'Done' was not found."
    End If


    
  
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing


End Sub
Click to expand...

There isn't anything I can do about the duplicate email addresses, because there are unique store numbers associated with those email address. I'm trying to only use one record to grab the email address, and then move on to the next unique email address and create 1 email for that email address, and so on, until all records have been reviewed and emails have been created for each unique email address within the data sheet. I did find this code snippet online. Wondering if this would help in identifying unique email addresses:

VBA Code: 
For Each cell In sh.columns("S").Cells.SpecialCells(xlCellTypeConstants)

    set rng = sh.Cells(cell.Row, 1).Range("A1:AC1")
 
Last edited:
Upvote 0
I would recommend posting what your data looks like & what your overview of what you are trying to accomplish step by step is.
You are jumping from issue to issue and this post is all over the place.
It started as an object reference error, moved to an if statement loop, and now were somehow changing the entire script and dealing with ranges and special cells i'm not informed of.
 
Upvote 0
DataBlake said:
I would recommend posting what your data looks like & what your overview of what you are trying to accomplish step by step is.
You are jumping from issue to issue and this post is all over the place.
It started as an object reference error, moved to an if statement loop, and now were somehow changing the entire script and dealing with ranges and special cells i'm not informed of.
Click to expand...

I think @DataBlake is right, we need to see your data. upload a screenshot or use XL2BB. The issues have jumped all around. It may be better to create a thread for each issue because people who are stronger in each issue will not be able to find an IF statement or For, Next loop problem in "Run-time error '424': Object required"
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA code to send an email if a certain IF Criteria is met that loops when the spreadsheet is open
Replies
1
Views
239
joeyc123
J
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
153
harmless92
H
V
  • Solved
Email Body
Replies
9
Views
383
Trixterz
T
M
  • Question
Extract data from outlook emails using excel vba
Replies
1
Views
1K
RoryA
RoryA
T
  • Solved
VBA to email pay slip
2
Replies
12
Views
1K
tayomi
T

Forum statistics

Threads
1,214,983
Messages
6,122,591
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top