[problem] Send Emails to Multiple Persons with Multiple Attachments with VBA

[angoeyuan]

Hi all,
there's some problem with my VBA formula, I can send the email with the subject column and the body column. But can not automatically add attachments in the email.
the VBA code as follows.

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("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

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

'path/file names are entered in the columns E:M in each row
Set rng = sh.Cells(cell.Row, 1).Range("E1: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 = sh.Cells(cell.Row, 3).Value
.Body = sh.Cells(cell.Row, 4).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

 

[Logit]

You need to include a path to the attachments.