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 = "[e-mail]"
    .CC = "[e-mail]"
    .From = "[e-mail]"
    .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

If Attach = 0 Or IsNull(Attach) Then...

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

If Attach = 0 Or IsNull(Attach) Then...

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:

If Not IsNull(Attach) Then
  .Send
Else
End If

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:

If Not IsNull(Attach) Then
  .Send
Else
End If

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.

Dim iConf
Dim Flds
Dim strHTML
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>"
 
 
With iMsg
    Set .Configuration = iConf
    .To = "[e-mail]"
    .CC = "[e-mail]"
    .From = "[e-mail]"
    .Subject = "Please read:  test CDO message with Attachment11"
    .HTMLBody = strHTML
 
 
   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 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

[Scott Price]

Dim iConf
Dim Flds
Dim strHTML
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>"
 
 
With iMsg
    Set .Configuration = iConf
    .To = "[e-mail]"
    .CC = "[e-mail]"
    .From = "[e-mail]"
    .Subject = "Please read:  test CDO message with Attachment11"
    .HTMLBody = strHTML
 
 
   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 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

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>References 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+attachment 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.

Sub FinalCDOeMail()
 
 
 
 
Dim iConf
Dim Flds
Dim strHTML
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
    'ToDo: Enter name or IP address of remote SMTP server.
    .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\rptMiller.txt")) = 0 Then
 
With iMsg
    Set .Configuration = iConf
    .To = "[e-mail]"
    .CC = "[e-mail]"
    .From = "[e-mail]"
    .Subject = "Contract Reports"
    .HTMLBody = strHTML
 
 
   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
 
 
  .Send
 
End With
End If
 
 
 
 
 
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
 
MsgBox "Mail Sent!"
 
 
 
 
 
End Sub

[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.

Sub FinalCDOeMail()
 
 
 
 
Dim iConf
Dim Flds
Dim strHTML
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
    'ToDo: Enter name or IP address of remote SMTP server.
    .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\rptMiller.txt")) = 0 Then
 
With iMsg
    Set .Configuration = iConf
    .To = "[e-mail]"
    .CC = "[e-mail]"
    .From = "[e-mail]"
    .Subject = "Contract Reports"
    .HTMLBody = strHTML
 
 
   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
 
 
  .Send
 
End With
End If
 
 
 
 
 
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
 
MsgBox "Mail Sent!"
 
 
 
 
 
End Sub

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