Excel macro to send email with attachment

[atuljadhavnetafim]

Hi,

i am trying to send email with attachment (only one file, less than 50kb PDF file)
the email address mentioned E5 and E6
Subject mentioned in E7
File name mentioned in E8
Folder path mentioned in E9 (the macro should search the file in sub folder as well)

but when i run this code, excel hang up, and getting error system error &H80004005 (-2147467259)

can you find out error.

Sub SendEmailWithAttachment()
    ' Set email properties
    Dim ToAddress As String
    Dim CCAddress As String
    Dim SubjectLine As String
    Dim EmailBody As String
    Dim AttachmentFilePath As String
    
    ToAddress = Range("E5").Value
    CCAddress = Range("E6").Value
    SubjectLine = Range("E7").Value
    EmailBody = Range("E10").Value
    AttachmentFilePath = Range("E9").Value
    
    ' Create email object
    Dim outlookApp As Object
    Set outlookApp = CreateObject("Outlook.Application")
    Dim email As Object
    Set email = outlookApp.CreateItem(0)
    
    ' Add recipients, subject, and body
    email.To = ToAddress
    email.CC = CCAddress
    email.Subject = SubjectLine
    email.Body = EmailBody
    
    ' Attach the file(s) to the email
    Dim folderPath As String
    folderPath = "I:\Taxation\Indirect Taxation\2022-23\Q3\TDS Certificate\26Q\"
    Dim fileExtension As String
    fileExtension = "*.pdf"
    Dim files As Object
    Set files = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath).files
    Dim file As Object
    For Each file In files
        If file.Path Like folderPath & fileExtension Or file.Path Like folderPath & "**\" & fileExtension Then
            email.Attachments.Add file.Path
        End If
    Next file
    
    ' Send the email
    email.Send
    
    ' Clean up objects
    Set email = Nothing
    Set outlookApp = Nothing
    
    ' Show confirmation message
    MsgBox "Email sent successfully."
End Sub

 

[ChrisFoster]

The error you are encountering is a system error, which means that it may not be related to the VBA code itself. However, one issue in your code could be the following line:

EmailBody = Range("E10").Value

If cell E10 is empty, this line will throw a runtime error. To avoid this, you can add a check to make sure the cell is not empty before assigning its value to the EmailBody variable:

If Not IsEmpty(Range("E10")) Then
    EmailBody = Range("E10").Value
Else
    EmailBody = ""
End If

 

[atuljadhavnetafim]

Ok, i mentioned subject in E10 cell, but getting below error, my PDF file is not more than 50kb
it is network drive, is there any issue?

Error at this line email.Attachments.Add file.Path

Error at this line email.Attachments.Add file.Path