Automated mailing on VBA fails on second iteration

Question

I modified VBA code to send to X mail address X attachment. On the first iteration the code works perfectly, the mail is sent as is with the correct file. On the second iteration the code stops when attaching it's file (on objMail.Attachments.Add archivoFuente line) showing up this screen:

My code gets the main data from the first sheet which contains the mail address in the second column and the filename it's standardized from the name of the user and the file extension on the third column. With that done the filename it's attached to the directory, then I add body, subject and other things for the user to see on the mail, finally the mail is sent and I empty the strings variables to start the text iteration but then I encounter the error even with the file on the folder and with the same filename as standardized.

Sub bulkMail()
Dim outlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim archivoFuente, toMail, ccMail As String
Dim i, j As Integer
Dim fila As Long

fila = Cells(Rows.Count, 2).End(xlUp).Row

'On Error Resume Next

Set outlookApp = New Outlook.Application
Set objMail = outlookApp.CreateItem(olMailItem)

For i = 2 To fila

    toMail = Cells(i, 2) ' & ";"
    archivoFuente = "C:\Users\..." & Cells(i, 3)
    objMail.Attachments.Add archivoFuente ' on the second iteration, stops here

    ThisWorkbook.Save
    'archivoFuente = ThisWorkbook.FullName
    'objMail.Attachments.Add archivoFuente
    
    objMail.To = toMail
    objMail.Subject = "TEST"
    objMail.Body = "LOREM," & vbNewLine & "IPSUM." & vbNewLine & "BYE."
    
    objMail.Send
    
    toMail = ""
    archivoFuente = ""
    
Next i

MsgBox "DONE!"

End Sub

Appreciate any help!

Answer 1

Simple fix: move Set objMail = outlookApp.CreateItem(olMailItem) inside the loop.

You need a new mail item for each row. The code as is fails because you're trying to attach a file to an email you just sent (on the first iteration).

Other things you could fix:

• Dim archivoFuente, toMail, ccMail As String - only ccMail is a String. You are looking for Dim archivoFuente As String, toMail As String, ccMail As String.
• Dim i, j As Integer - should be Dim i As Long, j As Long. Excel has more rows than Integer can handle.