Can I test for an xls file exist from a separate xls file

[andycapp28]

This is continuing on from some code I wrote where an excel spreadsheet had Column E holding email addresses. This column E was read and for each cell containing an address it would select Column A containing name of excel file to be put in an email.
I need to refine this as I need to ensure the excel file in Column A exists and only if it does send an email.

Finally just to ask if .From is valid to use object

My existing code is as follows;

Sub Email_ESS()
'
' Email all supported Evs held in excel file and _
attach his/her file from Monthly Query results
'

Dim r As Range, c As Range
Dim rLookfor As Range
Dim rLookin As Range

With Application
Application.ScreenUpdating = False
Application.EnableEvents = False
End With

With Worksheets
' Set rLookin = Range(Cells(2, "E"), Cells(2, "E").End(xlDown)) TOOK USE of this out as couldn't get to compile
End With

'For Each r In rLookin TOOK THIS OUT, COULDn't use as not get to compile

For Each r In Range(Cells(2, "E"), Cells(2, "E").End(xlDown))
' Set r = rLookin.Find(r.Value) NOT USING (see above)

If Not r Is Nothing Then

ESubject = "ESS_Report"
sendto = r.Value
NewFileName = r.Offset(0, -4)

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)

With Itm
.Subject = ESubject
.to = sendto
.Attachments.Add "G:\Marketing\Management and admin\Monthly Management Reports\ESS & EIT Monthly Reporting\NewFileName"
.Send
End With

Set App = Nothing
Set Itm = Nothing

End If
Next

End Sub

 

[SkipVought]

Hi,

Some thoughts.

When your turn these off, then you must turn them on when you're done. It's good housekeeping!

    Application.ScreenUpdating = False
    Application.EnableEvents = False

This With...End With OBJECT makes no sense.

    With Worksheets
    ' Set rLookin = Range(Cells(2, "E"), Cells(2, "E").End(xlDown)) TOOK USE of this out as couldn't get to compile
    End With

This should refer to a Worksheet Object not the Worksheets collection.

    With Worksheets("[b]Your Sheet Name HERE[/b]")
     Set rLookin = .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown)) '''TOOK USE of this out as couldn't get to compile
    End With

This makes no sense, as r exists until the end of the collection

        If Not r Is Nothing Then

I think that you mean if the VALUE in object r is not [no string]

        If r.Value <> "" Then

Now to your question

    On Error Resume Next
    .Attachments.Add "G:\Marketing\Management and admin\Monthly Management Reports\ESS & EIT Monthly Reporting\NewFileName"
    If Err.Number = 0 Then
        .Send
    Else
        Err.Clear
    End If
    On Error GoTo 0

Also, I'd suggest using the server name rather than a DOS Drive.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Unless your file will always be named NewFileName (with no extension), consider this:

On Error Resume Next
.Attachments.Add "G:\Marketing\Management and admin\Monthly Management Reports\ESS & EIT Monthly Reporting\"[blue] & NewFileName[/blue]
    If Err.Number = 0 Then
        .Send
    Else
        Err.Clear
    End If
    On Error GoTo 0

Have fun.

---- Andy

 

[andycapp28]

Thanks Skip and Andy

I will take this on board and give it a go tomorrow.

AC