Help required for automated mailing list

[lalthan]

Hello everyone,
I have a macro which send out automated mails to each recipient as
shown below. The macro runs well for the recipient as long as the file
associated with their name is there. Now my problem is if there is no
file associated with the recipient's name from my recipient list, then
the macro stops running in the middle giving me the message that there
is no file associated with the recipient or that the file intended for
the recipient is missing. Now , I would request you guys to help me out
so that the macro runs for all the recipients list skipping whoever is
not there in the list or whose file associated with the recipient is
missing. Thanks

Public Sub AutoMail()
Dim oOutlookApp As New Outlook.Application
Dim strBody As String
'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)
Windows("Macro file for SMs weekly report.xls").Activate
Range("B273").Activate
While ActiveCell.Value <> ""
varSMId = ActiveCell.Value
varSMName = ActiveCell.Offset(0, 1).Value
varEMail = ActiveCell.Offset(0, 2).Value
varAsonDate = Format(ActiveCell.Offset(0, 3).Value,
"dd-mm-yy")
varBranch = ActiveCell.Offset(0, 4).Value

lcAttachment = ""
lcAttachment = "C:\All SMs with codes week 4 May 04\" & varSMId
& ".xls"
If Not lcAttachment = "" Then
Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
'Set the recipient for the new email
To = varEMail
'Set the recipient for a copy
'.CC = "(e-mail address removed)"
'Set the subject
Subject = "Sales Performance as on date " &
varAsonDate
'The content of the document is used as the body for
the email
Body = strBody
Attachments.Add lcAttachment
Send
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End If

ActiveCell.Offset(1, 0).Activate
Wend

End Sub

 

[Bob Flanagan]

I believe if you change

If Not lcAttachment = "" Then

to

If Dir(lcAttachment) <> "" Then

It will solve. This checks to see if the file exists.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel