Need an if statement

Joined
Jun 13, 2023
Messages
5
Reaction score
0
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:

excel 2023.jpg
 
Joined
Sep 4, 2022
Messages
129
Reaction score
16
Code:
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.
 
Joined
Jun 13, 2023
Messages
5
Reaction score
0
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
 
Joined
Jun 13, 2023
Messages
5
Reaction score
0
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
 
Joined
Sep 4, 2022
Messages
129
Reaction score
16
a IF statement can enclose a bigger code pattern.
Once you have the 'Source' var define, you can achieve the checking of Source var.


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)

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
 
Joined
Jun 13, 2023
Messages
5
Reaction score
0
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.
 
Joined
Sep 4, 2022
Messages
129
Reaction score
16
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,794
Messages
2,569,641
Members
45,355
Latest member
SJLChristi

Latest Threads

Top