Runtime error: 424 "Object required" Help Please

M

MiyagiZama

New Member
Joined
Jul 26, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hello mates, Its me again

I have a inconvenient with this run time error, in my macro which send emails, but when I try to change the font and font size, this error appears, as you know I'm newbie in this world, could you help me with this problem please? c:

This is the code and screenshot of the pop up message

VBA Code: 
Sub combinacioncodigo()
'
' Declaramos variables
'
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Asunto As String
Dim BOL As String
Dim Msg As String
Dim MyFont
'
' Seleccionamos el rango de celdas a enviar Select
'
ActiveSheet.Range("A1:L42").Select
'
'
  ActiveWorkbook.EnvelopeVisible = True
'
'
'Asignamos valor a las variables y llamamos al envio
'
'
With ActiveSheet.MailEnvelope
BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
MyFont.Size = 22
        FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
        
    
        
        
       

       
       .Item.To = ThisWorkbook.Sheets("ReporteFinal").Range("O3").Value
       ' ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O4").Value & ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O5").Value VOLVER A PEGAR ESTO
       '.Item.cc = ThisWorkbook.Sheets("ReporteFinal").Range("O7").Value
       '.Item.bcc = "correo2@dominio.com"      'con copia oculta a...
       .Item.Subject = "Programacion Linea: "
       .Introduction = "Apreciables colegas, espero que se encuentren excelente el dia de hoy. " & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "El motivo de este correo es para informarle que tiene material en BOL desde el: " & FechaVencimiento & "." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Favor de apoyarnos con el plan para disminuir el numero de estas piezas: " & BOL & vbNewLine & vbNewLine & "Atentamente:" & vbNewLine & "Departamento de programacion" & vbNewLine & vbNewLine & vbNewLine & "ESTO ES UNA PRUEBA FAVOR DE OMITIR"
       .Item.Send
       
       
 End With
        
End Sub


1690471289400.png
1690471317517.png
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this:

VBA Code: 
Sub combinacioncodigo()
  ' Declaramos variables
  Dim OutlookApp As Outlook.Application
  Dim MItem As Outlook.MailItem
  Dim cell As Range
  Dim Asunto As String, FechaVencimiento As String
  Dim BOL As String, Msg As String
  Dim fSize As Double
  '
  ' Seleccionamos el rango de celdas a enviar Select
  ActiveSheet.Range("A1:L42").Select
  fSize = Selection.Cells(1).Font.Size
  Selection.Font.Size = 22
  ActiveWorkbook.EnvelopeVisible = True
  'Asignamos valor a las variables y llamamos al envio
  With ActiveSheet.MailEnvelope
    BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
   
    FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
    .Item.To = ThisWorkbook.Sheets("ReporteFinal").Range("O3").Value
    ' ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O4").Value & ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O5").Value VOLVER A PEGAR ESTO
    '.Item.cc = ThisWorkbook.Sheets("ReporteFinal").Range("O7").Value
    '.Item.bcc = "correo2@dominio.com"      'con copia oculta a...
    .Item.Subject = "Programacion Linea: "
    .Introduction = "Apreciables colegas, espero que se encuentren excelente el dia de hoy. " & _
      vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
      "El motivo de este correo es para informarle que tiene material en BOL desde el: " & _
      FechaVencimiento & "." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
      "Favor de apoyarnos con el plan para disminuir el numero de estas piezas: " & BOL & _
      vbNewLine & vbNewLine & "Atentamente:" & vbNewLine & "Departamento de programacion" & _
      vbNewLine & vbNewLine & vbNewLine & "ESTO ES UNA PRUEBA FAVOR DE OMITIR"
    .Item.Send
  End With
  Selection.Font.Size = fSize
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Sub combinacioncodigo() ' Declaramos variables Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim cell As Range Dim Asunto As String, FechaVencimiento As String Dim BOL As String, Msg As String Dim fSize As Double ' ' Seleccionamos el rango de celdas a enviar Select ActiveSheet.Range("A1:L42").Select fSize = Selection.Cells(1).Font.Size Selection.Font.Size = 22 ActiveWorkbook.EnvelopeVisible = True 'Asignamos valor a las variables y llamamos al envio With ActiveSheet.MailEnvelope BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0") FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy") .Item.To = ThisWorkbook.Sheets("ReporteFinal").Range("O3").Value ' ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O4").Value & ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O5").Value VOLVER A PEGAR ESTO '.Item.cc = ThisWorkbook.Sheets("ReporteFinal").Range("O7").Value '.Item.bcc = "correo2@dominio.com" 'con copia oculta a... .Item.Subject = "Programacion Linea: " .Introduction = "Apreciables colegas, espero que se encuentren excelente el dia de hoy. " & _ vbNewLine & vbNewLine & vbNewLine & vbNewLine & _ "El motivo de este correo es para informarle que tiene material en BOL desde el: " & _ FechaVencimiento & "." & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _ "Favor de apoyarnos con el plan para disminuir el numero de estas piezas: " & BOL & _ vbNewLine & vbNewLine & "Atentamente:" & vbNewLine & "Departamento de programacion" & _ vbNewLine & vbNewLine & vbNewLine & "ESTO ES UNA PRUEBA FAVOR DE OMITIR" .Item.Send End With Selection.Font.Size = fSize End Sub
Click to expand...
Yeah, It works but I need to change only the font size of the body message, with the font size of the table theres no problem, Could you assist to me with this please?
 
Upvote 0
MiyagiZama said:
I need to change only the font size of the body message
Click to expand...
I'm not sure if that's possible, since this way of sending an email has the message body in the "Introduction" section and this section doesn't have properties like font.

Instead of using the "Introduction" section, it occurs to me, that you add the body in the cells of the sheet, then those cells you can change the font and size.

I don't know if the sheet you are sending is "BOL" or "ReporteFinal" or it is another sheet.
But check the location of cells B3, C3, O3 as I am increasing 10 rows in the active sheet.
VBA Code: 
Sub combinacioncodigo()
  Dim FechaVencimiento As String
  Dim BOL As String
  '
  'aumentar 10 filas
  ActiveSheet.Rows("1:10").Insert
  FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
  BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
  Range("A1").Value = "Apreciables colegas, espero que se encuentren excelente el dia de hoy."
  Range("A3").Value = "El motivo de este correo es para informarle que tiene material en BOL desde el: " & FechaVencimiento
  Range("A5").Value = "Favor de apoyarnos con el plan para disminuir el numero de estas piezas: " & BOL
  Range("A7").Value = "Atentamente:"
  Range("A8").Value = "Departamento de programacion"
  Range("A10").Value = "ESTO ES UNA PRUEBA FAVOR DE OMITIR"
  Range("A1:A10").Font.Size = 22
 
  ActiveSheet.Range("A1:L52").Select                                      'aumentar a 10 filas
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Item.To = ThisWorkbook.Sheets("ReporteFinal").Range("O3").Value     'revisar la celda
    .Item.Subject = "Programacion Linea: "
    .Item.Send
  End With
 
  'borrar las 10 filas
  ActiveSheet.Rows("1:10").Delete
End Sub
:cool:
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
​
 
Upvote 1
Solution
DanteAmor said:
I'm not sure if that's possible, since this way of sending an email has the message body in the "Introduction" section and this section doesn't have properties like font.

Instead of using the "Introduction" section, it occurs to me, that you add the body in the cells of the sheet, then those cells you can change the font and size.

I don't know if the sheet you are sending is "BOL" or "ReporteFinal" or it is another sheet.
But check the location of cells B3, C3, O3 as I am increasing 10 rows in the active sheet.
VBA Code: 
Sub combinacioncodigo()
  Dim FechaVencimiento As String
  Dim BOL As String
  '
  'aumentar 10 filas
  ActiveSheet.Rows("1:10").Insert
  FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
  BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
  Range("A1").Value = "Apreciables colegas, espero que se encuentren excelente el dia de hoy."
  Range("A3").Value = "El motivo de este correo es para informarle que tiene material en BOL desde el: " & FechaVencimiento
  Range("A5").Value = "Favor de apoyarnos con el plan para disminuir el numero de estas piezas: " & BOL
  Range("A7").Value = "Atentamente:"
  Range("A8").Value = "Departamento de programacion"
  Range("A10").Value = "ESTO ES UNA PRUEBA FAVOR DE OMITIR"
  Range("A1:A10").Font.Size = 22
 
  ActiveSheet.Range("A1:L52").Select                                      'aumentar a 10 filas
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Item.To = ThisWorkbook.Sheets("ReporteFinal").Range("O3").Value     'revisar la celda
    .Item.Subject = "Programacion Linea: "
    .Item.Send
  End With
 
  'borrar las 10 filas
  ActiveSheet.Rows("1:10").Delete
End Sub
:cool:
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
​
Click to expand...
Thank you so much!!!!, It is work, I appreciate it
 
Upvote 0
Im glad to help you. Thanks for the feedback.

Please, you should mark the post that solved your question instead of marking your own answer as a solution. Thanks.
;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
I have a "Runtime error 440 : The object not support this method" in my macro
Replies
5
Views
426
Joe4
Joe4
M
  • Solved
Could you help me please with "run time error 1004 size property of the font class"
Replies
4
Views
359
DanteAmor
DanteAmor
M
  • Question
I have a RunTime error 1004
Replies
3
Views
362
DanteAmor
DanteAmor
M
  • Question
I want to put the signature below de temporaly screenshot but it appers above, could you assist to me please?
Replies
0
Views
209
MiyagiZama
M
M
  • Question
I want o put some strings in bold, could you assist to me please_
Replies
1
Views
290
Micron
M

Forum statistics

Threads
1,215,111
Messages
6,123,159
Members
449,098
Latest member
Doanvanhieu

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