Send Emails with VBA attachment

Question

I declared a variable to go from line to line with a repeat loop For to write the emails.

Works all neat until you get to the append part of the file, it shows the error image below.

I have tried to change the path several times, change the name of the files, but nothing works.

Base structure:

Code:

Sub enviar_email()

Set objeto_outlook = CreateObject("Outlook.Application")

For linha = 2 To 6

    Set Email = objeto_outlook.createitem(0)
    
    Email.display
    
    Email.to = Cells(linha, 1).Value
    
    Email.Subject = "Testando"
    
    Email.Body = Cells(linha, 2).Value & "," & Chr(10) & Chr(10) _
    & Cells(linha, 3).Value & Chr(10) & Chr(10) _
    & "Att," & Chr(10) & "Rafael"
    
    Email.Attachments.Add (ThisWorkbook.Path & "C:\CoParticipacoes\Excel\Participacoes\" & Cells(linha, 2).Value & ".pdf")
    
    Email.send

Next

End Sub

Error:

Answer 1

It is necessary to adjust the search path of the attachments.

Combine the ThisWorkbook.Path with "C:\CoParticipacoes\Excel\Participacoes\" & Cells(linha, 2).Value & ".pdf" will generate an error.

ThisWorkbook.Path returns the path of the file itself that is running VBA. Therefore, we assume that your file is in: "C: Excel coparticipacoes", when combining you will receive the return:

C:\CoParticipacoes\ExcelC:\CoParticipacoes\Excel\Participacoes\

In your specific case, I believe that removing the ThisWorkbook.Path must solve.

Use the debugging tools, you should note this problem. Still, you can do a test like this, to see an example of how you are building the file location:

Sub verCaminho()
    MsgBox ThisWorkbook.Path & "C:\CoParticipacoes\Excel\Participacoes\" & Cells(2, 2).Value & ".pdf"
End Sub