Macro excel not attaching power point presentation on email

Question

I´ve been trying to attach a power point presentation to a macro excel code to send mails, but when I run the code it only sends the body of the email and not the attached document.

The document is saved in a local folder so it shouldn't be an issue...


Sub sendEmailsToMultiplePersonsWithMultipleAttachments()



Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set sh = Sheets("Hoja1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)

    'path/file names are entered in the columns D:M in each row
    Set rng = sh.Cells(cell.Row, 1).Range("D1:M1")
    
    If cell.Value Like "?*@?*.?*" And _
    Application.WorksheetFunction.CountA(rng) > 0 Then
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
            .To = sh.Cells(cell.Row, 1).Value
            .CC = sh.Cells(cell.Row, 2).Value
            .Subject = "Boarder Logistics Corporations CHILE"
            .Body = sh.Cells(cell.Row, 3).Value
            
            For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                
                If Trim(FileCell.Value) <> "" Then
                    If Dir(FileCell.Value) <> "" Then
                         .Attachments.Add FileCell.Value
                    End If
                End If
            Next FileCell
            '.Send
            .Display
        End With
        
        Set OutMail = Nothing
    End If
Next cell

Set OutApp = Nothing

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With


End Sub

Eugene Astafiev · Answer

when I run the code it only sends the body of the email and not the attached document.

You need to check conditions under which the attachment may not be added to the mail item. In the code I see the following loop which iterates over cells and check conditions whether to add a file as an attachment or not:

For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                
  If Trim(FileCell.Value) <> "" Then
     If Dir(FileCell.Value) <> "" Then
       .Attachments.Add FileCell.Value
     End If
  End If
Next FileCell

The first point is that loop never iterates over cells.

Second, the if conditions could be false, so you will never get a file attached.

Third, the Attachments.Add method creates a new attachment in the Attachments collection. The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment. So, make sure that file path specified in the cell is valid and such file exists on the disk.

Macro excel not attaching power point presentation on email

1 Answers