Need an if statement

[gussy81]

Hi,

I have a spreadsheet with 5 clients. email address and path to pdf doc. i have it working to the point it sends email to all 5 columns on the spreadsheet and attaches the pdf from the column that specifies the path to the pdf file. the issue i am currently looking for is when there is no pdf doc for a given client the code stops. i need the if statement to say if there is no pdf for a client on the list skip them and move onto the next client.

this is my vb code:

Sub Single_attachment()
Dim appOutlook As Object
Dim Email As Object
Dim Source, mailto As String
Dim intR As Integer

Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)

intR = 2
For intR = 2 To 6
mailto = Cells(intR, 2)
Source = "C:\Users\fergus.kelly\Desktop\test eom invoices email\" & Cells(intR, 3)

Set Email = appOutlook.CreateItem(olMailItem)
With Email
.To = mailto
.Attachments.Add Source
.Subject = "Important Sheets"
.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
.send
End With

Next intR
End Sub

the spreadsheet looks like this:

 

[FResher]

Set Email = appOutlook.CreateItem(olMailItem)
With Email
.To = mailto
if dir(Source) <> "" then
.Attachments.Add Source
end if
.Subject = "Important Sheets"
.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
.send
End With

dir( file_name ) will check if the file is in the written location too.

 

[gussy81]

hi FResher - this is good as it processes with the missing pdf file - but can we also say don't compose an email for that client

 

[gussy81]

I did this - is there a better more efficient way?

Set Email = appOutlook.CreateItem(olMailItem)
With Email
.To = mailto
If Dir(Source) <> "" Then
.Attachments.Add Source
End If
.Subject = "Important Sheets"
.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
If Dir(Source) <> "" Then
.display
End If
End With

 

[FResher]

a IF statement can enclose a bigger code pattern.
Once you have the 'Source' var define, you can achieve the checking of Source var.

Sub Single_attachment()
Dim appOutlook As Object
Dim Email As Object
Dim Source, mailto As String
Dim intR As Integer

Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)

intR = 2
For intR = 2 To 6
mailto = Cells(intR, 2)
Source = "C:\Users\fergus.kelly\Desktop\test eom invoices email\" & Cells(intR, 3)

if Dir(Source) = "" then 

Set Email = appOutlook.CreateItem(olMailItem)
With Email
.To = mailto
.Attachments.Add Source
.Subject = "Important Sheets"
.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
.send
End With

end if

Next intR
End Sub

 

[gussy81]

ok thank you.

i ran the code update and changed your code from this:

if Dir(Source) = "" then

to this:

If Dir(Source) <> "" Then

as I was getting an error. is this ok to run with?

 

[FResher]

ok thank you.

i ran the code update and changed your code from this:

to this:


as I was getting an error. is this ok to run with?

yes, I forget this.
thank you

 

[gussy81]

could i ask you for your opinion on the for loop i have please:

For intR = 2 To 6

is there a clever way to re-write this to accomondate any amount as apposed to hard coding 6.

 

[FResher]

could i ask you for your opinion on the for loop i have please:



is there a clever way to re-write this to accomondate any amount as apposed to hard coding 6.

sure, it's about 'checking datas' and 'rows'

the main idea is to ensure the datas are ok,
a row must have the 4 columns ( 4 cells ) filled, and all relevant datas in.

leading to :
if the 'datas' are here and relevant, the row is ok, so you can send the mail
then you go to the next row.