By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Send mail by CDO and attachments

P: 41
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!

Expand|Select|Wrap|Line Numbers
  1. With iMsg
  2.     Set .Configuration = iConf
  3.     .To = "bjo@propeople.org"
  4.     .CC = "bjo@propeople.org"
  5.     .From = "bjo@propeople.org"
  6.     .Subject = "Please read:  test CDOSYS message with Attachment3"
  7.     .HTMLBody = strHTML
  8.  
  9.    '.Err.Clear
  10.    On Error Resume Next
  11.    Set Object = Expression
  12.  
  13.  
  14.  
  15.    If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
  16.          Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
  17.       End If
  18.  
  19.  
  20.      If Attach = 0 Then
  21.       Cancel = True
  22.       Else: .Send
  23.       End If
  24.  
Sep 11 '07 #1
Share this Question
Share on Google+
11 Replies


Scott Price
Expert 100+
P: 1,384
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
Sep 11 '07 #2

P: 41
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.

Expand|Select|Wrap|Line Numbers
  1. If Attach = 0 Then
  2.       Cancel = True
  3.       Else: .Send
  4.       End If 
Maybe you can see something I'm missing.
Sep 11 '07 #3

Scott Price
Expert 100+
P: 1,384
Throw in a
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. If Attach = 0 Or IsNull(Attach) Then...
Regards,
Scott
Sep 12 '07 #4

P: 41
Throw in a
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. If Attach = 0 Or IsNull(Attach) Then...
Regards,
Scott
Hi,

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

Expand|Select|Wrap|Line Numbers
  1.  If Attach = 0 Or IsNull(Attach) Then
  2.       Cancel = True
  3.       Else: .Send
  4.       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!
Sep 12 '07 #5

Scott Price
Expert 100+
P: 1,384
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Attach) Then
  2.   .Send
  3. Else
  4. End If
Regards,
Scott
Sep 12 '07 #6

P: 41
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Attach) Then
  2.   .Send
  3. Else
  4. End If
Regards,
Scott
Thanks again, Scott, but it sends whether or not there is an attachment. Any other ideas?
Sep 12 '07 #7

Scott Price
Expert 100+
P: 1,384
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
Sep 12 '07 #8

P: 41
Sub CDOTestFive()


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

Expand|Select|Wrap|Line Numbers
  1. Dim iConf
  2. Dim Flds
  3. Dim strHTML
  4. Dim Attach
  5.  
  6. Const cdoSendUsingPort = 2
  7.  
  8. Set iMsg = CreateObject("CDO.Message")
  9. Set iConf = CreateObject("CDO.Configuration")
  10.  
  11. Set Flds = iConf.Fields
  12.  
  13. With Flds
  14.     .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  15.  
  16.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "propexbe.propeople.org"
  17.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  18.     .Update
  19. End With
  20.  
  21. strHTML = "<HTML>"
  22. strHTML = strHTML & "<HEAD>"
  23. strHTML = strHTML & "<BODY>"
  24. strHTML = strHTML & "<b><p>Please let me know if you got an attachment and if you could open it. Thanks.</p></b></br>"
  25. strHTML = strHTML & "<b><p>Also please let me know if the text of the message is bold.  Thanks.  </b></p>"
  26. strHTML = strHTML & "</BODY>"
  27. strHTML = strHTML & "</HTML>"
  28.  
  29.  
  30. With iMsg
  31.     Set .Configuration = iConf
  32.     .To = "jkt@propeople.org"
  33.     .CC = "wld@propeople.org"
  34.     .From = "bjo@propeople.org"
  35.     .Subject = "Please read:  test CDO message with Attachment11"
  36.     .HTMLBody = strHTML
  37.  
  38.  
  39.    On Error Resume Next
  40.    Set Object = Expression
  41.  
  42.  
  43.  If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
  44.          Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
  45.       End If
  46.  
  47.  
  48.     If Not IsNull (Attach) Then
  49.       .Send
  50.       End If
  51.  
  52. End With
  53. End If
  54.  
  55.  
  56. Set iMsg = Nothing
  57. Set iConf = Nothing
  58. Set Flds = Nothing
  59.  
  60. MsgBox "Mail Sent!"
  61.  
  62.    End Sub
Sep 12 '07 #9

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. Dim iConf
  2. Dim Flds
  3. Dim strHTML
  4. Dim Attach
  5.  
  6. Const cdoSendUsingPort = 2
  7.  
  8. Set iMsg = CreateObject("CDO.Message")
  9. Set iConf = CreateObject("CDO.Configuration")
  10.  
  11. Set Flds = iConf.Fields
  12.  
  13. With Flds
  14.     .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  15.  
  16.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "propexbe.propeople.org"
  17.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  18.     .Update
  19. End With
  20.  
  21. strHTML = "<HTML>"
  22. strHTML = strHTML & "<HEAD>"
  23. strHTML = strHTML & "<BODY>"
  24. strHTML = strHTML & "<b><p>Please let me know if you got an attachment and if you could open it. Thanks.</p></b></br>"
  25. strHTML = strHTML & "<b><p>Also please let me know if the text of the message is bold.  Thanks.  </b></p>"
  26. strHTML = strHTML & "</BODY>"
  27. strHTML = strHTML & "</HTML>"
  28.  
  29.  
  30. With iMsg
  31.     Set .Configuration = iConf
  32.     .To = "jkt@propeople.org"
  33.     .CC = "wld@propeople.org"
  34.     .From = "bjo@propeople.org"
  35.     .Subject = "Please read:  test CDO message with Attachment11"
  36.     .HTMLBody = strHTML
  37.  
  38.  
  39.    On Error Resume Next
  40.    Set Object = Expression
  41.  
  42.  
  43.  If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
  44.          Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
  45.       End If
  46.  
  47.  
  48.     If Not IsNull (Attach) Then
  49.       .Send
  50.       End If
  51.  
  52. End With
  53. End If
  54.  
  55.  
  56. Set iMsg = Nothing
  57. Set iConf = Nothing
  58. Set Flds = Nothing
  59.  
  60. MsgBox "Mail Sent!"
  61.  
  62.    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
Sep 12 '07 #10

P: 41
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.

Expand|Select|Wrap|Line Numbers
  1. Sub FinalCDOeMail()
  2.  
  3.  
  4.  
  5.  
  6. Dim iConf
  7. Dim Flds
  8. Dim strHTML
  9. Dim Attach
  10.  
  11.  
  12.  
  13. Const cdoSendUsingPort = 2
  14.  
  15. Set iMsg = CreateObject("CDO.Message")
  16. Set iConf = CreateObject("CDO.Configuration")
  17.  
  18. Set Flds = iConf.Fields
  19.  
  20.  
  21.  
  22. With Flds
  23.     .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  24.     'ToDo: Enter name or IP address of remote SMTP server.
  25.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "propexbe.propeople.org"
  26.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  27.     .Update
  28. End With
  29.  
  30.  
  31. strHTML = "<HTML>"
  32. strHTML = strHTML & "<HEAD>"
  33. strHTML = strHTML & "<BODY>"
  34. strHTML = strHTML & "<b><p>Please let me know if you got an attachment and if you could open it. Thanks.</p></b></br>"
  35. strHTML = strHTML & "<b><p>Also please let me know if the text of the message is bold.  Thanks.  </b></p>"
  36. strHTML = strHTML & "</BODY>"
  37. strHTML = strHTML & "</HTML>"
  38.  
  39.  
  40.  
  41. If Not Len(Dir("G:\Accounting\Development\rptMiller.txt")) = 0 Then
  42.  
  43. With iMsg
  44.     Set .Configuration = iConf
  45.     .To = "jkt@propeople.org"
  46.     .CC = "wld@propeople.org"
  47.     .From = "bjo@propeople.org"
  48.     .Subject = "Contract Reports"
  49.     .HTMLBody = strHTML
  50.  
  51.  
  52.    On Error Resume Next
  53.    Set Object = Expression
  54.  
  55.  
  56. If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
  57.         Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
  58.       End If
  59.  
  60.  
  61.   .Send
  62.  
  63. End With
  64. End If
  65.  
  66.  
  67.  
  68.  
  69.  
  70. Set iMsg = Nothing
  71. Set iConf = Nothing
  72. Set Flds = Nothing
  73.  
  74. MsgBox "Mail Sent!"
  75.  
  76.  
  77.  
  78.  
  79.  
  80. End Sub
Sep 12 '07 #11

Scott Price
Expert 100+
P: 1,384
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.

Expand|Select|Wrap|Line Numbers
  1. Sub FinalCDOeMail()
  2.  
  3.  
  4.  
  5.  
  6. Dim iConf
  7. Dim Flds
  8. Dim strHTML
  9. Dim Attach
  10.  
  11.  
  12.  
  13. Const cdoSendUsingPort = 2
  14.  
  15. Set iMsg = CreateObject("CDO.Message")
  16. Set iConf = CreateObject("CDO.Configuration")
  17.  
  18. Set Flds = iConf.Fields
  19.  
  20.  
  21.  
  22. With Flds
  23.     .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  24.     'ToDo: Enter name or IP address of remote SMTP server.
  25.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "propexbe.propeople.org"
  26.     .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  27.     .Update
  28. End With
  29.  
  30.  
  31. strHTML = "<HTML>"
  32. strHTML = strHTML & "<HEAD>"
  33. strHTML = strHTML & "<BODY>"
  34. strHTML = strHTML & "<b><p>Please let me know if you got an attachment and if you could open it. Thanks.</p></b></br>"
  35. strHTML = strHTML & "<b><p>Also please let me know if the text of the message is bold.  Thanks.  </b></p>"
  36. strHTML = strHTML & "</BODY>"
  37. strHTML = strHTML & "</HTML>"
  38.  
  39.  
  40.  
  41. If Not Len(Dir("G:\Accounting\Development\rptMiller.txt")) = 0 Then
  42.  
  43. With iMsg
  44.     Set .Configuration = iConf
  45.     .To = "jkt@propeople.org"
  46.     .CC = "wld@propeople.org"
  47.     .From = "bjo@propeople.org"
  48.     .Subject = "Contract Reports"
  49.     .HTMLBody = strHTML
  50.  
  51.  
  52.    On Error Resume Next
  53.    Set Object = Expression
  54.  
  55.  
  56. If Not IsMissing("G:\Accounting\Development\rptMiller.txt") Then
  57.         Set Attach = .AddAttachment("G:\Accounting\Development\rptMiller.txt")
  58.       End If
  59.  
  60.  
  61.   .Send
  62.  
  63. End With
  64. End If
  65.  
  66.  
  67.  
  68.  
  69.  
  70. Set iMsg = Nothing
  71. Set iConf = Nothing
  72. Set Flds = Nothing
  73.  
  74. MsgBox "Mail Sent!"
  75.  
  76.  
  77.  
  78.  
  79.  
  80. 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
Sep 12 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.