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

Sounds like either `Trim(FileCell.Value) <> ""` or `Dir(FileCell.Value) <> ""` is false. — BigBen, Sep 07 '22 at 16:38
Do also have any leading spaces etc that need to be trimmed on checking for `dir` ? — Nathan_Sav, Sep 07 '22 at 16:53
You'll need to step through your code and figure it out - difficult to know for sure where the problem is without seeing any of your data. If you're not getting an error then @BigBen's suggestions seem likely. — Tim Williams, Sep 07 '22 at 17:42
To restate the previous comments, the value in FileCell should contain a complete and valid file path. If you paste that path to the address bar in File Explorer and hit enter, does the file open? — ChrisB, Sep 07 '22 at 17:44
Try using this code to open the file immediately after the line `For Each FileCell`. If the full file path in FileCell.Value is not valid, the file won't open. `CreateObject("Shell.Application").Open ("FileCell.Value")` — ChrisB, Sep 07 '22 at 17:50
Thank you guys fror the reply! I'm just starting to code so this helps a lot! — Xiomycv, Sep 07 '22 at 18:36

score 0 · Answer 1 · answered Sep 07 '22 at 21:46

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.

I was dumb enough to nop copy the direct source but instead I copied the file source lol so that was the issue. Thanks anyways! learned a lot — Xiomycv, Sep 08 '22 at 14:17

Macro excel not attaching power point presentation on email

1 Answers1