files not attaching to email

[ajm]

folks,

some of my files are attaching and some are not. the macro below runs down a list and for every "Yes" in column F, create an email, and then add attachments. There could be 6 attachments at most per email. However, not every email will have all the attachments. The names of each of the attachments is specified in the variables path1- path6, where cells(i,"N").value contains the name of the recipient and cells N62-N66 contain the name of the particular reports. The idea being that for every recipient marked with a "Yes", create an email and attach whichever of the reports, named in the path1-6 variables, that can be found in the Temp folder.

The paths are correct, and the file names are correct. What else could be causing issues?

Sub testsendemail()

Dim i As Integer
Dim path1 As String
Dim path2 As String
Dim path3 As String
Dim path4 As String
Dim path5 As String
Dim path6 As String

For i = 69 To 349

   If Cells(i, "F").Value = "Yes" Then
   

            
            Dim OutApp As Object
            Dim OutMail As Object
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
                path1 = "C:\TEMP\" & Cells(i, "N").Value & Cells(63, "N").Value & ".pdf"
                path2 = "C:\TEMP\" & Cells(i, "N").Value & Cells(64, "N").Value & ".pdf"
                path3 = "C:\TEMP\" & Cells(i, "N").Value & Cells(65, "N").Value & ".pdf"
                path4 = "C:\TEMP\" & Cells(i, "N").Value & Cells(66, "N").Value & ".pdf"
                path5 = "C:\TEMP\" & Cells(i, "N").Value & Cells(62, "N").Value & ".pdf"
                path6 = "C:\TEMP\" & Cells(i, "O").Value & Cells(63, "N").Value & ".pdf"
             
            On Error Resume Next
            
            Signature = Environ("appdata") & "\microsoft\signatures\"
            If Dir(Signature, vbDirectory) <> vbNullString Then
                Signature = Signature & Dir$(Signature & "*.htm")
            Else:
                Signature = ""
            End If
            Signature = CreateObject("Scripting.FileSystemObject").GetFile(Signature).OpenAsTextStream(1, -2).ReadAll
                   
            
            With OutMail
                .sentonbehalfofname = "Reports"
                .To = Cells(i, "E").Value
                .cc = Cells(i, "G").Value
                .Subject = "Statements for " & Cells(62, "B").Value
                .htmlbody = "Good Afternoon" & "<br />" & "<br />" & "Please find attached your statements for " & Cells(62, "B").Value & "." & "<br />" & "<br />" & _
                    "If you require any further information regarding these documents, please do not hesitate to contact me directly." & "<br />" & "<br />" & "Regards" & "<br />" & "<br />" & _
                    Signature
                    
                   
                    
                .Attachments.Add path1
                .Attachments.Add path2
                .Attachments.Add path3
                .Attachments.Add path4
                .Attachments.Add path5
                .Attachments.Add path6
                .Application.Visible = True
                
                .Save
                             
            End With
            On Error GoTo 0
            
            Set OutMail = Nothing
            Set OutApp = Nothing
            
            With Application
            .ScreenUpdating = True
            .EnableEvents = True
        
            End With
            
                  
    End If
            
    
Next i
  
End Sub

 

[daverunt]

Manually step through the code using F8.
When you step through each line in the list of Attachments hover over path1, 2 etc and see if it is valid.
Possibly a missing backslash in the path\filename.
Possibly the filename doesn't exactly match the content of the cell.

 

[ajm]

cheers dave. I am going to go back through it this afternoon. the interesting thing is that its not always the same files that are attached, nor the same ones that are not attached. its perplexing but bound to be something simple.

 

[daverunt]

Might be an idea to set pathX="" for each path at the end of each loop before next i, to clear the variable value.

 

[ajm]

finally got a chance to work on this again today. got the source files to look at and discovered mysterious extra spaces in the names of the saved files compared to the variable names assigned to path 1-6 at run time. Thanks for your help.

 

[daverunt]

Glad you got it working. Thanks for the feedback.