Could you help me please with "run time error 1004 size property of the font class"

M

MiyagiZama

New Member
Joined
Jul 26, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hello mates!, hope you´re fine

I have a trouble with my excel macro due to Mail.Evelope doesn't have font size proprieties, and someone on the forum suggest me to put the message that I want to send in a cell and after that change the font size of these cell where I put the message but when I tried to run the macro for first time after turn on the pc, appears a pop up message which says "run time error 1004 size property of the font class", but after that I press in debug and run again the macro and it runs without trouble, this error only appears the first time after turn on my device, Could you help me with please c:

Here's the VBA code


VBA Code: 
Sub BenditoCodigoJalaPOFAVO()
'
' Declaramos variables
'
Dim Asunto As String
Dim Correo As String
Dim Destinatario As String
Dim BOL As String
Dim FechaVencimiento As String
Dim Msg As String
Dim LastRow As Long
Dim Linea As String
'
'
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("A:A").Copy
Sheets("ReporteF").Activate
Cells(1, 1).Select
ActiveSheet.Paste
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("C:C").Copy
Sheets("ReporteF").Activate
Cells(1, 2).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("D:D").Copy
Sheets("ReporteF").Activate
Cells(1, 3).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("G:G").Copy
Sheets("ReporteF").Activate
Cells(1, 4).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("H:H").Copy
Sheets("ReporteF").Activate
Cells(1, 5).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("E:E").Copy
Sheets("ReporteF").Activate
Cells(1, 6).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("F:F").Copy
Sheets("ReporteF").Activate
Cells(1, 7).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("K:K").Copy
Sheets("ReporteF").Activate
Cells(1, 8).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("M:M").Copy
Sheets("ReporteF").Activate
Cells(1, 9).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("I:I").Copy
Sheets("ReporteF").Activate
Cells(1, 10).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("Q:Q").Copy
Sheets("ReporteF").Activate
Cells(1, 11).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("TablaCrudo").Activate
Sheets("TablaCrudo").Range("AB:AB").Copy
Sheets("ReporteF").Activate
Cells(1, 12).Select
ActiveSheet.Paste
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'AJUSTAMOS LAS CELDAS CON AUTOFIT Y LAS CENTRAMOS A LA CELDA
'
ActiveSheet.Columns("A:L").AutoFit
'
'EMPIEZA LA SEGUNDA MACRO
'
'
'DECLARAMOS EL RANGO QUE QUEREMOS COPIAR CON EL FOR
'
LastRow = ThisWorkbook.Sheets("ReporteF").Range("A1").CurrentRegion.Rows.Count
Sheets("ReporteF").Activate
Range("A:L").Select
'
'CENTRAMOS EL TEXTO DE LAS CELDAS
'
With Range("B:L")
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlBottom

  End With
  '
'
'Aumentar 10 filas
    ActiveSheet.Rows("1:11").Insert
'
'
'LLAMAMOS AL ENVIO
'
'
ActiveWorkbook.EnvelopeVisible = True
'
'Asignamos valor a las variables y llamamos al envio
'
With ActiveSheet.MailEnvelope
BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
'ThisWorkbook.Sheets("BOL").Range("B3").Font.Bold = True
FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
'ThisWorkbook.Sheets("BOL").Range("C3").Font.Bold = True
Linea = Format(ThisWorkbook.Sheets("BOL").Range("J3").Value, "#,##0")
'
'
'ESCRIBIMOS NUESTRO MENSAJE EN LAS CELDAS QUE AÑADIMOS YA QUE EL MAIL ENVELOPE NO TIENE ATRIBUTOS PARA CAMBIAR TAMAÑO O COLOR DE FONT
'POSTERIORMENTE LE AUMENTAMOS EL TAMAÑO Y PONEMOS EN NEGRITAS EL TEXTO QUE DESEAMOS
'
  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 = 16
  Range("A10").Font.Bold = True
'
'
       .Item.To = ThisWorkbook.Sheets("BOL").Range("F3").Value
       '& ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O4").Value & ";" & ThisWorkbook.Sheets("ReporteFinal").Range("O5").Value
       '.Item.cc = ThisWorkbook.Sheets("ReporteFinal").Range("O7").Value
       '.Item.bcc = "correo2@dominio.com"      'con copia oculta a...
       .Item.Subject = "Programacion Linea: " & 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
'
'BORRAR LAS FILAS QUE AÑADIMOS Y LAS COLUMNAS DE LA TABLA QUE ENVIAMOS POR CORREO
'
  ActiveSheet.Columns("A:L").Delete
  '
 '
 MsgBox "Se logrooooooo"
'
End Sub
 

Attachments

  • 1691174195868.png
    1691174195868.png
    6.9 KB · Views: 7
  • 1691174255505.png
    1691174255505.png
    13.8 KB · Views: 9

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Check it like this, putting the cell data before the EnvelopeVisible statement

I simplified your code a bit.

VBA Code: 
Sub BenditoCodigoJalaPOFAVO()
  ' Declaramos variables
  Dim Asunto As String, Correo As String, Destinatario As String
  Dim BOL As String, FechaVencimiento As String
  Dim Msg As String, Linea As String
  Dim shT As Worksheet, shR As Worksheet
  '
  Set shT = Sheets("TablaCrudo")
  Set shR = Sheets("ReporteF")
  
  shT.Range("A:A").Copy shR.Cells(1, 1)
  shT.Range("C:C").Copy shR.Cells(1, 2)
  shT.Range("D:D").Copy shR.Cells(1, 3)
  shT.Range("G:G").Copy shR.Cells(1, 4)
  shT.Range("H:H").Copy shR.Cells(1, 5)
  shT.Range("E:E").Copy shR.Cells(1, 6)
  shT.Range("F:F").Copy shR.Cells(1, 7)
  shT.Range("K:K").Copy shR.Cells(1, 8)
  shT.Range("M:M").Copy shR.Cells(1, 9)
  shT.Range("I:I").Copy shR.Cells(1, 10)
  shT.Range("Q:Q").Copy shR.Cells(1, 11)
  shT.Range("AB:AB").Copy shR.Cells(1, 12)
  shR.Activate
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  '
  'AJUSTAMOS LAS CELDAS CON AUTOFIT Y LAS CENTRAMOS A LA CELDA
  ActiveSheet.Columns("A:L").AutoFit
  '
  'EMPIEZA LA SEGUNDA MACRO
  'CENTRAMOS EL TEXTO DE LAS CELDAS
  With Range("B:L")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
  End With
  '
  'Aumentar 10 filas
  'ESCRIBIMOS NUESTRO MENSAJE EN LAS CELDAS QUE AÑADIMOS YA QUE EL MAIL ENVELOPE NO TIENE ATRIBUTOS PARA CAMBIAR TAMAÑO O COLOR DE FONT
  'POSTERIORMENTE LE AUMENTAMOS EL TAMAÑO Y PONEMOS EN NEGRITAS EL TEXTO QUE DESEAMOS
  ActiveSheet.Rows("1:11").Insert
  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 = 16
  Range("A10").Font.Bold = True
  
  'LLAMAMOS AL ENVIO
  ActiveWorkbook.EnvelopeVisible = True
  'Asignamos valor a las variables y llamamos al envio
  With ActiveSheet.MailEnvelope
    BOL = Format(ThisWorkbook.Sheets("BOL").Range("B3").Value, "#,##0")
    'ThisWorkbook.Sheets("BOL").Range("B3").Font.Bold = True
    FechaVencimiento = Format(ThisWorkbook.Sheets("BOL").Range("C3").Value, "dd/mmm/yyyy")
    'ThisWorkbook.Sheets("BOL").Range("C3").Font.Bold = True
    Linea = Format(ThisWorkbook.Sheets("BOL").Range("J3").Value, "#,##0")
    '
    .Item.To = ThisWorkbook.Sheets("BOL").Range("F3").Value
    .Item.Subject = "Programacion Linea: " & Linea
    .Item.Send
  End With
  '
  'BORRAR LAS FILAS QUE AÑADIMOS Y LAS COLUMNAS DE LA TABLA QUE ENVIAMOS POR CORREO
  ActiveSheet.Columns("A:L").Delete
  MsgBox "Se logrooooooo"
End Sub

Note: It's good to put comments between the lines, but putting too many comments makes it hard to read the code.
:cool:
 
Upvote 0
Sorry, I was missing some data, try the following:

VBA Code: 
Sub BenditoCodigoJalaPOFAVO()
  ' Declaramos variables
  Dim Dest As String, BOL As String, FechaVencimiento As String, Linea As String
  Dim shT As Worksheet, shR As Worksheet
  '
  Set shT = Sheets("TablaCrudo")
  Set shR = Sheets("ReporteF")
  
  shT.Range("A:A").Copy shR.Cells(1, 1)
  shT.Range("C:C").Copy shR.Cells(1, 2)
  shT.Range("D:D").Copy shR.Cells(1, 3)
  shT.Range("G:G").Copy shR.Cells(1, 4)
  shT.Range("H:H").Copy shR.Cells(1, 5)
  shT.Range("E:E").Copy shR.Cells(1, 6)
  shT.Range("F:F").Copy shR.Cells(1, 7)
  shT.Range("K:K").Copy shR.Cells(1, 8)
  shT.Range("M:M").Copy shR.Cells(1, 9)
  shT.Range("I:I").Copy shR.Cells(1, 10)
  shT.Range("Q:Q").Copy shR.Cells(1, 11)
  shT.Range("AB:AB").Copy shR.Cells(1, 12)
  shR.Activate
  
  'AJUSTAMOS LAS CELDAS CON AUTOFIT
  ActiveSheet.Columns("A:L").AutoFit
  '
  'CENTRAMOS EL TEXTO DE LAS CELDAS
  With Range("B:L")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
  End With
  '
  'Aumentar 10 filas
  'ESCRIBIMOS NUESTRO MENSAJE EN LAS CELDAS QUE AÑADIMOS YA QUE EL MAIL ENVELOPE NO TIENE ATRIBUTOS PARA CAMBIAR TAMAÑO O COLOR DE FONT
  'POSTERIORMENTE LE AUMENTAMOS EL TAMAÑO Y PONEMOS EN NEGRITAS EL TEXTO QUE DESEAMOS
  ActiveSheet.Rows("1:11").Insert
  
  'Asignamos valor a las variables y llamamos al envio
  With ThisWorkbook.Sheets("BOL")
    BOL = Format(.Range("B3").Value, "#,##0")
    FechaVencimiento = Format(.Range("C3").Value, "dd/mmm/yyyy")
    Linea = Format(.Range("J3").Value, "#,##0")
    Dest = .Range("F3").Value
  End With
  
  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 = 16
  Range("A10").Font.Bold = True
  
  'LLAMAMOS AL ENVIO
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Item.To = Dest
    .Item.Subject = "Programacion Linea: " & Linea
    .Item.Send
  End With
  '
  'BORRAR LAS FILAS QUE AÑADIMOS Y LAS COLUMNAS DE LA TABLA QUE ENVIAMOS POR CORREO
  ActiveSheet.Columns("A:L").Delete
  ActiveWorkbook.EnvelopeVisible = False
  MsgBox "Se logrooooooo"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

🤗
 
Last edited:
Upvote 1
Solution
DanteAmor said:
Sorry, I was missing some data, try the following:

VBA Code: 
Sub BenditoCodigoJalaPOFAVO()
  ' Declaramos variables
  Dim Dest As String, BOL As String, FechaVencimiento As String, Linea As String
  Dim shT As Worksheet, shR As Worksheet
  '
  Set shT = Sheets("TablaCrudo")
  Set shR = Sheets("ReporteF")
 
  shT.Range("A:A").Copy shR.Cells(1, 1)
  shT.Range("C:C").Copy shR.Cells(1, 2)
  shT.Range("D:D").Copy shR.Cells(1, 3)
  shT.Range("G:G").Copy shR.Cells(1, 4)
  shT.Range("H:H").Copy shR.Cells(1, 5)
  shT.Range("E:E").Copy shR.Cells(1, 6)
  shT.Range("F:F").Copy shR.Cells(1, 7)
  shT.Range("K:K").Copy shR.Cells(1, 8)
  shT.Range("M:M").Copy shR.Cells(1, 9)
  shT.Range("I:I").Copy shR.Cells(1, 10)
  shT.Range("Q:Q").Copy shR.Cells(1, 11)
  shT.Range("AB:AB").Copy shR.Cells(1, 12)
  shR.Activate
 
  'AJUSTAMOS LAS CELDAS CON AUTOFIT
  ActiveSheet.Columns("A:L").AutoFit
  '
  'CENTRAMOS EL TEXTO DE LAS CELDAS
  With Range("B:L")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
  End With
  '
  'Aumentar 10 filas
  'ESCRIBIMOS NUESTRO MENSAJE EN LAS CELDAS QUE AÑADIMOS YA QUE EL MAIL ENVELOPE NO TIENE ATRIBUTOS PARA CAMBIAR TAMAÑO O COLOR DE FONT
  'POSTERIORMENTE LE AUMENTAMOS EL TAMAÑO Y PONEMOS EN NEGRITAS EL TEXTO QUE DESEAMOS
  ActiveSheet.Rows("1:11").Insert
 
  'Asignamos valor a las variables y llamamos al envio
  With ThisWorkbook.Sheets("BOL")
    BOL = Format(.Range("B3").Value, "#,##0")
    FechaVencimiento = Format(.Range("C3").Value, "dd/mmm/yyyy")
    Linea = Format(.Range("J3").Value, "#,##0")
    Dest = .Range("F3").Value
  End With
 
  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 = 16
  Range("A10").Font.Bold = True
 
  'LLAMAMOS AL ENVIO
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Item.To = Dest
    .Item.Subject = "Programacion Linea: " & Linea
    .Item.Send
  End With
  '
  'BORRAR LAS FILAS QUE AÑADIMOS Y LAS COLUMNAS DE LA TABLA QUE ENVIAMOS POR CORREO
  ActiveSheet.Columns("A:L").Delete
  ActiveWorkbook.EnvelopeVisible = False
  MsgBox "Se logrooooooo"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

🤗
Click to expand...
Excellent, thank you so much!!, It works without any problems, but I like to learn more about the VBA errors so, could you said to me which was the issue that my macro had?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Runtime error: 424 "Object required" Help Please
Replies
6
Views
604
MiyagiZama
M
M
  • Solved
I have a "Runtime error 440 : The object not support this method" in my macro
Replies
5
Views
842
Joe4
Joe4
M
  • Question Question
I have a RunTime error 1004
Replies
3
Views
549
DanteAmor
DanteAmor
M
  • Question Question
How to accept with code the next pop up "a program is trying to send an email message on your behalf"
Replies
0
Views
318
MiyagiZama
M
M
  • Question Question
I want to put the signature below de temporaly screenshot but it appers above, could you assist to me please?
Replies
0
Views
375
MiyagiZama
M

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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