CDO.Message Dont Send If No Attachment

S

SLP

Hi, I have some code that sends an email with Attachment by CDO. I'm using
it in Access as an automated solution to generating required reports for each
program director that has reports due. Problem is, if an attachment does not
exist, I dont want an email sent. I can't seem to figure that part of the
code out. Any help would be appreciated. Here is part of the code. Thanks
in advance

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.From = "(e-mail address removed)"
.Subject = "Please read: test CDOSYS message with Attachment3"
.HTMLBody = strHTML

On Error Resume Next
Set Object = Expression


If Not IsMissing("G:\Accounting\Development\ExcelToAccess2.xls") Then
Set Attach =
..AddAttachment("G:\Accounting\Development\ExcelToAccess2.xls")
End If


If Attach = 0 Then
Cancel = True
Else: .Send
End If
 
S

SteveM

Use the Dir() function to check for the existence of the file specified by
path.

If you don't want to send a mail without attachment, you should probably
surrround your code with the test and only execute if Dir() does not return a
zero length string "". Alternatively, you can just conditionally execute .Send

Steve
 
S

SLP

Thanks for the response. I'm kinda new to this so I'm not sure what the
Dir() function is...I'll see if I can find something here that will give me a
clue once I get into the office...unless you would be willing to give me a
little more info. Thanks again.
 
R

Ralph

One way to do it:

If Not Len(Dir("G:\Accounting\Development\ExcelToAccess2.xls"))= 0 Then
With iMsg
'rest of the code here..
End With
End if
 
S

SLP

Thanks. I added that and now it won't send whether or not there is an
attachment. Any suggestions would be appreciated.
 
D

Douglas J. Steele

It would probably be a good idea for you to post your current code, just in
case you misunderstood what Ralph was suggesting.
 
S

SLP

Thats a good idea. I probably did misunderstand as I am still new at this.
Here is the code. I really appreciate all the help.

Sub CDOTestFive()


Dim iMsg
Dim iConf
Dim Flds
Dim strHTML
'Dim iBp As CDO.IBodyPart
'Dim iMsg As New CDO.Message
Dim Attach

Const cdoSendUsingPort = 2

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set Flds = iConf.Fields


With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"propexbe.propeople.org"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With


strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Please let me know if you got an attachment and
if you could open it. Thanks.</p></b></br>"
strHTML = strHTML & "<b><p>Also please let me know if the text of the
message is bold. Thanks. </b></p>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

If Not Len(Dir("G:\Accounting\Development\ExcelToAccess2.xls")) = 0 Then

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.From = "(e-mail address removed)"
.Subject = "Please read: test CDOSYS message with Attachment13"
.HTMLBody = strHTML



On Error Resume Next
Set Object = Expression

If Not IsMissing("G:\Accounting\Development\ExcelToAccess2.xls") Then
Set Attach =
..AddAttachment("G:\Accounting\Development\ExcelToAccess2.xls")
End If


If Attach = 0 Then
Cancel = True
Else: .Send
End If


End With
End If

Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

MsgBox "Mail Sent!"

End Sub
 
D

Douglas J. Steele

Since the Len(Dir( )) statement is checking whether or not the file exists,
you don't need the IsMissing anymore.

Sub CDOTestFive()
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Object
Dim strHTML As String
Dim Attach As Object

Const cdoSendUsingPort = 2

' May as well check as for the attachment as soon as possible.
' No point doing the work if you're just going to be throwing it away!

If Len(Dir("G:\Accounting\Development\ExcelToAccess2.xls")) > 0 Then
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = _
cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"propexbe.propeople.org"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= _
10
.Update
End With

strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Please let me know if you got an attachment
and " & _
"if you could open it. Thanks.</p></b></br>"
strHTML = strHTML & "<b><p>Also please let me know if the text of the "
& _
"message is bold. Thanks. </b></p>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.From = "(e-mail address removed)"
.Subject = "Please read: test CDOSYS message with Attachment13"
.HTMLBody = strHTML

Set Attach =
..AddAttachment("G:\Accounting\Development\ExcelToAccess2.xls")
.Send
MsgBox "Mail Sent!"
End With
End If

Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

End Sub

Actually, I'm not a CDO expert, but I'm not sure you need to declare Attach:
the following might be sufficient

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.From = "(e-mail address removed)"
.Subject = "Please read: test CDOSYS message with Attachment13"
.HTMLBody = strHTML
.AddAttachment("G:\Accounting\Development\ExcelToAccess2.xls")
.Send
MsgBox "Mail Sent!"
End With
 
S

SLP

Hi,

Before you responded, I deleted the If Attach = 0 Then portion of the code
and just added .Send and it worked like a charm. I will see what happens if
I deleted the IsMissing portion.

I want to thank everyone for helping this newbie out! You guys are great.
 
R

Ralph

You might also try adding the CDO reference and the ActiveX Data references.

Sub CDOTestFive()
'set the following References from the Menu bar under Tools then References
'Microsoft CDO for Windows 2000 Library
'Microsoft ActiveX Data Objects 2.5 Library

Dim iMsg As CDO.Message
Dim iConf As CDO.Configuration
Dim Flds As ADODB.Fields
Dim strHTML As String

'If Not Len(Dir("G:\Accounting\Development\ExcelToAccess2.xls")) = 0 Then
If Not Len(Dir("c:\temp\tst.xls")) = 0 Then
Set iMsg = New CDO.Message
Set iConf = iMsg.Configuration
Set Flds = iConf.Fields


With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"propexbe.propeople.org"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With


strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Please let me know if you got an attachment and
if you could open it. Thanks.</p></b></br>"
strHTML = strHTML & "<b><p>Also please let me know if the text of the
message is bold. Thanks. </b></p>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.From = "(e-mail address removed)"
.Subject = "Please read: test CDOSYS message with Attachment13"
.HTMLBody = strHTML
.Send

End With

MsgBox "Mail Sent!"

End If

Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

End Sub
 
S

SLP

Thanks again everyone. When I removed the IsMissing portion of the code, it
didn't send when there was an attachment so I need Ralph's Len (Dir()) and
the IsMissing.

Ralph -- I have both of those refences already set up. I'll try your code
and see what happens.
 

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

Similar Threads

Do not send email if no attachment 4
Did not receive email via CDO.message 1
CDO Message 1
SendEmail with Embedded Graphics 0
Send Workbook as an Attachment Via Gmail - Code Review 1
Send Mail using Lotus Notes 1
Trying to send an E-mail on Your Behalf - Question 10
Problem in mail attachment when using CDO 3

Top