VBA + Outlook

[Edson Santos 294]

Guys, good morning/afternoon/evening,

I'm having an issue with Excel VBA while automating an email send-out.

My code sends approximately 17/18 emails and then crashes with the following error:

I'm not sure what might be happening, can you help me? I don't think it's Microsoft limiting the sends per time period because I just close the error and click again to run the macro, and it sends another 17/18 normally.

Here's the code for you to take a look at.

Sub enviar_email()

Dim w As Worksheet
Dim nRows As Long

Set objeto_outlook = CreateObject("Outlook.Application")
Set w = Sheets("Templ.")

nRows = w.Cells(w.Rows.Count, 1).End(xlUp).Row


For linha = 7 To nRows

Sheets("Templ.").Select

Set Email = objeto_outlook.createitem(0)
Email.display

Email.To = Cells(linha, 2).Value
'cópia-->Email.cc =
'Cópia oculta --> Email.bcc =

Sheets("Corpo de email").Select

Email.Subject = Cells(2, 1)

Sheets("Templ.").Select

'As duas linhas abaixo são variáveis que serão usadas no email em HTML
texto1 = "Olá, " & Cells(linha, 3) & "," & "<br>"


'A linha abaixo começa o email de fato
Sheets("Corpo de email").Select
Email.htmlbody = texto1 & RangetoHTML(Range("corpo2")) _
& Email.htmlbody
'A linha acima coloca a assinatura no corpo do email

Email.Attachments.Add ("\\ctbn33\_CS\PMD\Acompanhamento Dashboards\myBühler\Enviar e-mail automaticamente\myBühler.pdf")
Email.send

Sheets("Templ.").Select

Next


End Sub

 

[Edson Santos 294]

Oh, I forgot to send the error screenshot, here it is:

Traduction: Error in execution time '440':
There is no support in the object for this method.