VBA to send multiple emails with multiple attachments

[jespinda]

Greetings all. VBA novice and seeking some expertise assistance.
I have a list of multiple users, and each have varying file attachments. Most of the code is from searching, but it is not working as expected.
A = Send To
B = CC
C = Message
D-R = File names including the path

The code creates the emails as needed, but the files do not attach. No error message is displayed.
Any assistance would be appreciated.

Option Explicit

Sub Send_Email_V2()
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("Send_Email")

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:R1")

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 = "Updated Overtime Report Notification"
.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

 

[6StringJazzer]

I set up a file to test your code and it worked perfectly for me. So the answer has got to be in your data.

Just before the line that attaches the file, I would add a little debug code to see if the path is what you think it is.

Debug.Print "Row " & cell.Row & ". File path found in cell " & FileCell.Address & ": " & FileCell.Value
.Attachments.Add FileCell.Value

Then check the Immediate window after you run and see what shows up.

If the file does not exist or if it is otherwise a bad path, you should get an error. If the paths are all good but not getting attached then I'm stuck, because as I said the same code works for me.

(P.S. Code in a post is much easier to read if you use code tags so that formatting is preserved. After pasting in the code, select the code then click the VBA button on the edit controls.)

 

[jespinda]

Aloha Jeff. Mahalo for your quick response. As you suspected, it was the data. All is working as required. Mahalo Nui Loa!

 

[6StringJazzer]

He mea iki! Are you in Hawaii? I visited once several years ago and loved it. We spent a week in Maui, and another week on the Big Island.

 

[jespinda]

He mea iki! Are you in Hawaii? I visited once several years ago and loved it. We spent a week in Maui, and another week on the Big Island.

Aloha! Yes, I am. I live on Oahu, but used to live on the Big Island a few years ago. I live don the Hilo side. My father's family is from Lahaina, Maui.

 

[6StringJazzer]

We spent a day in Hilo. Charming little town.