Send mail by CDO and attachments

[pixie]

Hi, Access 2003. I have a DB that contains deadlines data. I have the DB set up to create a report for each person that has deadline within a certain time frame. I need to automate the DB so an email is sent when a report exists but an email is not sent if there is no report for people. I have CDO code that works great except for the do not send part. The following is that part of the code. Any help would be appreciated. Thanks!

With iMsg
    Set .Configuration = iConf
    .To = "bjo@propeople.org"
    .CC = "bjo@propeople.org"
    .From = "bjo@propeople.org"
    .Subject = "Please read:  test CDOSYS message with Attachment3"
    .HTMLBody = strHTML
  
   '.Err.Clear
   On Error Resume Next
   Set Object = Expression
   
   
   
   If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
         Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
      End If
      
      
     If Attach = 0 Then
      Cancel = True
      Else: .Send
      End If

[Scott Price]

I guess I'm not understanding where the problem lies. You say you have the database set up to only generate a report when there exists a deadline, then why do you want to not fire the code when there is no report? How are you calling the code to begin with?

Regards,
Scott

[pixie]

I guess I'm not understanding where the problem lies. You say you have the database set up to only generate a report when there exists a deadline, then why do you want to not fire the code when there is no report? How are you calling the code to begin with?

Regards,
Scott

The DB runs a query for each of the program folks that could or could not have deadlines. If they have a deadline, a report is created. If they don't a report is not generated. My code is currently saying create and email and send the attachment to that person then it calls code for the next person and so on. If one of the people (I call about 15 subs) does not have a report to attach, I do not want an email going out to them. Hope that clarifies things. I can do it when not using CDO but want to use CDO so I don't get the Outlook warnings.
Hope you can help out. I don't get why that one part of the code that should stop the sending doesn't work.

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

Maybe you can see something I'm missing.

[Scott Price]

Throw in a

Debug.Print Attach

in about line 18, just for grins and chuckles. I'm interested to see what value you will get if there is no report to attach.

You haven't included the section of code where you declare the variables used here, but I'm suspecting that you should be checking for a null value as well as 0 for your variable Attach. Something like [CODE=vb]If Attach = 0 Or IsNull(Attach) Then...[/CODE]

Regards,
Scott

[pixie]

Throw in a

Debug.Print Attach

in about line 18, just for grins and chuckles. I'm interested to see what value you will get if there is no report to attach.

You haven't included the section of code where you declare the variables used here, but I'm suspecting that you should be checking for a null value as well as 0 for your variable Attach. Something like [CODE=vb]If Attach = 0 Or IsNull(Attach) Then...[/CODE]

Regards,
Scott

Hi,

I added the Debug.Print and didn't get any response. I also tried

 If Attach = 0 Or IsNull(Attach) Then
      Cancel = True
      Else: .Send
      End If

and it doesn't work weither or not there is an attachment.

Here are my variables:

Dim iConf
Dim Flds
Dim strHTML
Dim Attach

I appreciate your help!

[Scott Price]

The lack of apparent response means that you are passing a null value into the Attach variable. (You're immediate window should show at the bottom of the vba editor window... If passed a null value, Debug.Print has nothing to print to the immediate window, and so all you will likely see is the cursor moving one line...) Since it's a null value, you should be able to delete the Attach = 0 part, leaving only the IsNull(Attach) part.

Why not try re-writing this to something like the following:
[CODE=vb]
If Not IsNull(Attach) Then
.Send
Else
End If[/CODE]

Regards,
Scott

[pixie]

The lack of apparent response means that you are passing a null value into the Attach variable. (You're immediate window should show at the bottom of the vba editor window... If passed a null value, Debug.Print has nothing to print to the immediate window, and so all you will likely see is the cursor moving one line...) Since it's a null value, you should be able to delete the Attach = 0 part, leaving only the IsNull(Attach) part.

Why not try re-writing this to something like the following:
[CODE=vb]
If Not IsNull(Attach) Then
.Send
Else
End If[/CODE]

Regards,
Scott

Thanks again, Scott, but it sends whether or not there is an attachment. Any other ideas?

[Scott Price]

Thanks again, Scott, but it sends whether or not there is an attachment. Any other ideas?

Please post the whole Subroutine, starting from top ending at bottom. When you paste it into this reply window, wrap it in code tags by selecting all the code text, clicking the # button, and then manually edit the first code tag to look just like this: [CODE=vb]

Regards,
Scott

[pixie]

Sub CDOTestFive()


Here it is Scott. Once again, I really appreciate your help.

[CODE=vb]
Dim iConf
Dim Flds
Dim strHTML
Dim Attach

Const cdoSendUsingPor t = 2

Set iMsg = CreateObject("C DO.Message")
Set iConf = CreateObject("C DO.Configuratio n")

Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = cdoSendUsingPor t

.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "propexbe.prope ople.org"
.Item("http://schemas.microso ft.com/cdo/configuration/smtpconnectiont imeout") = 10
.Update
End With

strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Plea se 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 = "jkt@propeople. org"
.CC = "wld@propeople. org"
.From = "bjo@propeople. org"
.Subject = "Please read: test CDO message with Attachment11"
.HTMLBody = strHTML


On Error Resume Next
Set Object = Expression


If Not IsMissing("G:\A ccounting\Devel opment\rptMille r.txt") Then
Set Attach = .AddAttachment( "G:\Accounting\ Development\rpt Miller.txt")
End If


If Not IsNull (Attach) Then
.Send
End If

End With
End If


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

MsgBox "Mail Sent!"

End Sub[/CODE]

[Scott Price]

[CODE=vb]Dim iConf
Dim Flds
Dim strHTML
Dim Attach

Const cdoSendUsingPor t = 2

Set iMsg = CreateObject("C DO.Message")
Set iConf = CreateObject("C DO.Configuratio n")

Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = cdoSendUsingPor t

.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "propexbe.prope ople.org"
.Item("http://schemas.microso ft.com/cdo/configuration/smtpconnectiont imeout") = 10
.Update
End With

strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Plea se 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 = "jkt@propeople. org"
.CC = "wld@propeople. org"
.From = "bjo@propeople. org"
.Subject = "Please read: test CDO message with Attachment11"
.HTMLBody = strHTML


On Error Resume Next
Set Object = Expression


If Not IsMissing("G:\A ccounting\Devel opment\rptMille r.txt") Then
Set Attach = .AddAttachment( "G:\Accounting\ Development\rpt Miller.txt")
End If


If Not IsNull (Attach) Then
.Send
End If

End With
End If


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

MsgBox "Mail Sent!"

End Sub[/CODE]

A couple of things that occur to me also here.

1: You should probably rename the Attach variable to something like MyAttach, since I think Attach is a reserved word.

2: just making sure that your references are set correctly: if you go into Tools>Reference s which CDO library do you have checked? Should be Microsoft CDO for Windows 2000 Library. (I'm almost certain that you'll have this reference, since I don't think the earlier versions of CDO supported HTML messages, but please check anyway... You never know :-).

3: How are you determining who gets the attachment and who does not? It doesn't appear to me in this section of code. Somewhere you will have to pass the email addresses of the people who are to receive the email+attachmen t to this function...

4. Will it mess up the flow of your function if you put the .Send command just after your Set Attach = .AddAttachment "..." line?

Regards,
Scott

[pixie]

Hi, Scott --

Thanks for all your help. I've gotten it to work. I'll attach the code.

In regard to #3, a query runs for every person that could possibly get a report. If the report is blank it doesn't save. In my code, I have one sub for each person that could get a report and the first sub calls the second sub which calls the third and so on.

I really appreciate all your help! Thanks a lot.

[CODE=vb]Sub FinalCDOeMail()




Dim iConf
Dim Flds
Dim strHTML
Dim Attach



Const cdoSendUsingPor t = 2

Set iMsg = CreateObject("C DO.Message")
Set iConf = CreateObject("C DO.Configuratio n")

Set Flds = iConf.Fields



With Flds
.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = cdoSendUsingPor t
'ToDo: Enter name or IP address of remote SMTP server.
.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "propexbe.prope ople.org"
.Item("http://schemas.microso ft.com/cdo/configuration/smtpconnectiont imeout") = 10
.Update
End With


strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Plea se 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:\Acc ounting\Develop ment\rptMiller. txt")) = 0 Then

With iMsg
Set .Configuration = iConf
.To = "jkt@propeople. org"
.CC = "wld@propeople. org"
.From = "bjo@propeople. org"
.Subject = "Contract Reports"
.HTMLBody = strHTML


On Error Resume Next
Set Object = Expression


If Not IsMissing("G:\A ccounting\Devel opment\rptMille r.txt") Then
Set Attach = .AddAttachment( "G:\Accounting\ Development\rpt Miller.txt")
End If


.Send

End With
End If





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

MsgBox "Mail Sent!"





End Sub[/CODE]

[Scott Price]

Hi, Scott --

Thanks for all your help. I've gotten it to work. I'll attach the code.

In regard to #3, a query runs for every person that could possibly get a report. If the report is blank it doesn't save. In my code, I have one sub for each person that could get a report and the first sub calls the second sub which calls the third and so on.

I really appreciate all your help! Thanks a lot.

[CODE=vb]Sub FinalCDOeMail()




Dim iConf
Dim Flds
Dim strHTML
Dim Attach



Const cdoSendUsingPor t = 2

Set iMsg = CreateObject("C DO.Message")
Set iConf = CreateObject("C DO.Configuratio n")

Set Flds = iConf.Fields



With Flds
.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = cdoSendUsingPor t
'ToDo: Enter name or IP address of remote SMTP server.
.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "propexbe.prope ople.org"
.Item("http://schemas.microso ft.com/cdo/configuration/smtpconnectiont imeout") = 10
.Update
End With


strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b><p>Plea se 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:\Acc ounting\Develop ment\rptMiller. txt")) = 0 Then

With iMsg
Set .Configuration = iConf
.To = "jkt@propeople. org"
.CC = "wld@propeople. org"
.From = "bjo@propeople. org"
.Subject = "Contract Reports"
.HTMLBody = strHTML


On Error Resume Next
Set Object = Expression


If Not IsMissing("G:\A ccounting\Devel opment\rptMille r.txt") Then
Set Attach = .AddAttachment( "G:\Accounting\ Development\rpt Miller.txt")
End If


.Send

End With
End If





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

MsgBox "Mail Sent!"





End Sub[/CODE]

Great! Glad you got it to work! The funny thing is that your solution is exactly what I thought it should be in the first place, and was going to follow up with the next post... You beat me to it :-) Good job!

Regards,
Scott