CDO Problems excel 2016

[barandaran]

Hello people of the forum.
Using one of the many models on the web for sending emails using CDO, I'm creating a macro, with attachments, to a list of people.
Everything works Caaasi Well, because sends the emails, but presents the problem that repeats the file attached.
That is to say, when using a FOR loop: The first mail sends you 1 attachment, but then the second sends 2, to third 3 and so on.
I put the object variable = Nothing but it does not recognize it. This is the part of the code with problem
If someone has had the same problem, please.
:
Dim Email As CDO. Message
Set Email = CreateObject ( "CDO. Message ")
.
.
For I = 6 To Range ( "E " & Rows. Count). End (xlUp). Row
With Email. Configuration. Fields
.
.
.
.
End With
.
.
With Email
. To = cells (I, "E ")
. From = Mail
. Subject = "Visit Notice "
FormaNum = cells (I, "D ")
. TextBody = "visit notice " & Format (FormaNum, "#, # # #, # # #. 00 ") & "Cancel appointment before " & Cells (3, "E ")
. AddAttachment cells (28, "c ") ' Cells (28, "c ") Path PDF file
. AddAttachment (RUTAANEXO1)
' . Attachments. Add Range ( "C28 "). Value ' produces error missmacth
' . Attachment. Delete
' . Send
' Email = Nothing ' does not recognize nothing. Not cleaning the attaches? He repeats them
' If Not Email Is Nothing Then
' Set Email = Nothing
' End If
End With
Next
.
.
.
.

 

[John_w]

Move this line inside the loop:

Set Email = CreateObject("CDO.Message")

and put this line after the With Email .... End With:

Set Email = Nothing

Please use CODE tags when posting VBA code.

 

[barandaran]

Move this line inside the loop:

Set Email = CreateObject("CDO.Message")

and put this line after the With Email .... End With:

Set Email = Nothing

Please use CODE tags when posting VBA code.

Thank John. A question, because in all the examples, on the web, do not put the SET inside as your indica, but at the beginning?

 

[John_w]

Did it work? I didn't test my change, but because of the problem you describe with sending multiple emails, I thought that creating a new instance of the CDO.Message object each time would fix it.

 

[barandaran]

Did it work? I didn't test my change, but because of the problem you describe with sending multiple emails, I thought that creating a new instance of the CDO.Message object each time would fix it.

Yes. Works. Is that I thought (correct me if I'm wrong), which was 1 single object that was rewritten N times; And not N different objects, as many as mail send. Doesn't that make the macro heavy and slow?

 

[xenou]

an email object is not an easy one to rewrite (it is relatively complex with many fields besides only the ones you are using). So in short, you want to recreate the email on each loop as the simplest way to "clear" the object and start with a clean email.

With VBA, always use SET with objects:

WRONG: email = Nothing
RIGHT:Set email = Nothing