Afternoon all,

Been having a little problem with some code im trying to run. I picked up a code sample from www.rondebruin.nl earlier that should allow me to create a list of recipients and file locations and then i run the code and voila all the relevent attachments get sent to the relevent people. However i set this up and before i went to lunch the code seemed to be working, when i came back it wont work anymore (no beer was consumed during lunch for the record). Moost of the files are being attached but some of them are not being picke up. I will post the code below and try to point to where i believe it is going wrong.

Im sure it is something really simple that i am overlooking, i have made a couple of small amendments to the code but i cant see anything i have missed.

Anyway here it is:

Code:
Sub Send_Files()
'Working in 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range
 
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
 
    Set sh = Sheets("Sheet1")
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
    For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
 
        'Enter the file names in the C:E column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("C1:E1")
 
        If cell.Value Like "?*@?*.?*" And _
           Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)
 
            With OutMail
                .To = cell.Value
                .Subject = cell.Offset(0, 4).Value
                .Body = cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
                "Please find attached your reports. Should you have any questions please let me know." & vbNewLine & vbNewLine & _
                "Regards" & vbNewLine & vbNewLine & _
                "Chris Durant" & vbNewLine & _
                "Senior Reporting Analyst" & vbNewLine & _
                "Planning Team - Strategy And Marketing" & vbNewLine & _
                "RBS Insurance,3rd Floor, Phase 2, Churchill Court, Bromley, MP 41" & vbNewLine & _
                "Ext: 0208 313 5542" & vbNewLine & vbNewLine & vbNewLine & _
                ""
                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
 'THIS IS WHERE IT SOMETIMES READS THE FILE NAMES AS FILECELL.VALUE BUT THE ABOVE IF STATEMENT READS AS FALSE (GOES TO ENDIF)
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell
                .display  'Use Display or Send 
            End With
 
            Set OutMail = Nothing
        End If
    Next cell
 
    Set OutApp = Nothing

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
As i say im sure its simple but its bugging the hell out of me.

For Reference:
Made a list in Sheets("Sheet1") with
In column A : Names of the people
In column B : E-mail addresses
In column C:E : Filenames like this C:\Data\Book2.xls (don't have to be Excel files)



Thanks
EV