Do not send email if no attachment

[slpixie]

Hi, I have an Access 2003 DB for contracts. I have a query, for each person
responsible for a contract, that lists all upcoming contract due dates with
specific time frames. Some times a person's query might result in no records.
There is a report generated based on each of the queries. I have code to
send an email but I do not want to send the email if there is nothing to
attach. That part of the code needs help as it doesn't work. Thanks in
advance.

Function MailParameters()

Dim outApp As Outlook.Application, outMsg As MailItem
Dim Cancel As Boolean

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg
.Importance = olImportanceHigh
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.BCC = "(e-mail address removed)"
.Subject = "Outlook Test Code Seven"
.Body = "Testing code for Access to Outlook"
On Error Resume Next
.Attachments.Add _
"G:\Accounting\CDImportNumbers\CDImportNumbers.xls"
If Attachment.Count = 0 Then
Cancel = True
Else: .Send
End If

End With

Set outApp = Nothing
Set outMsg = Nothing

End Function

 

[pietlinden]

Hi, I have an Access 2003 DB for contracts. I have a query, for each person
responsible for a contract, that lists all upcoming contract due dates with
specific time frames. Some times a person's query might result in no records.
There is a report generated based on each of the queries. I have code to
send an email but I do not want to send the email if there is nothing to
attach. That part of the code needs help as it doesn't work. Thanks in
advance.

Function MailParameters()

Dim outApp As Outlook.Application, outMsg As MailItem
Dim Cancel As Boolean

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg
.Importance = olImportanceHigh
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.BCC = "(e-mail address removed)"
.Subject = "Outlook Test Code Seven"
.Body = "Testing code for Access to Outlook"
On Error Resume Next
.Attachments.Add _
"G:\Accounting\CDImportNumbers\CDImportNumbers.xls"
If Attachment.Count = 0 Then
Cancel = True
Else: .Send
End If

End With

Set outApp = Nothing
Set outMsg = Nothing

End Function

why not just filter out the recipients with no attachments in the
Access?

 

[pietlinden]

whoa... marble-like sharpness on my part.... check the following line
at the beginning of the process...

strAttachment ="G:\Accounting\CDImportNumbers\CDImportNumbers.xls"

If Dir(strAttachment)="" then 'file does not exist
rs.MoveNext 'so just go to the next
record
Else
put your automation code here.
End If

 

[slpixie via AccessMonster.com]

The routine runs each person's query and then automatically generates a
report whether or not there is data in the query results. The routine is set
so if the report contains no data, it is not saved. I will be having a
function per person so need code that says if for this mail receipient there
is no report file saved do not send an email.

Hi, I have an Access 2003 DB for contracts. I have a query, for each person
responsible for a contract, that lists all upcoming contract due dates with

[quoted text clipped - 35 lines]
why not just filter out the recipients with no attachments in the
Access?

 

[slpixie via AccessMonster.com]

Thanks. It works if there isn't an attachment but now won't run if there is
one. Here is the code again. Did I place things in the right places? I
need to dash to the doctor now but will check back first thing tomorrow.

Function MailParameters()

Dim outApp As Outlook.Application, outMsg As MailItem
Dim Cancel As Boolean

strAttachment = "G:\Accounting\CDImportNumbers\CDImportNumbers.xls"

If Dir(strAttachment) = "" Then 'file does not exist
rs.MoveNext 'so just go to the next record
Else
'put your automation code here.

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg
.Importance = olImportanceHigh
.To = "(e-mail address removed)"
'.CC = "(e-mail address removed)"
'.BCC = "(e-mail address removed)"
.Subject = "Outlook Test Code Nine"
.Body = "Testing code for Access to Outlook"
On Error Resume Next
.Attachments.Add _
"G:\Accounting\CDImportNumbers\CDImportNumbers1.xls"
If Attachment.Count = 0 Then
Cancel = True
Else: .Send
End If

End With

End If

Set outApp = Nothing
Set outMsg = Nothing

End Function