473,545 Members | 2,693 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Send mail by CDO and attachments

41 New Member
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
11 11291
Scott Price
1,384 Recognized Expert Top Contributor
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
pixie
41 New Member
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
1,384 Recognized Expert Top Contributor
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
pixie
41 New Member
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
1,384 Recognized Expert Top Contributor
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
pixie
41 New Member
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
1,384 Recognized Expert Top Contributor
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
pixie
41 New Member
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
1,384 Recognized Expert Top Contributor
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>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
Sep 12 '07 #10

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

Similar topics

5
1507
by: mimi | last post by:
Hi, I've been searching all the posts and articles but couldn't find any example on how to send attachments with pop3 using C#. Does anyone have one or know how? Thanks
3
3703
by: Luiz Vianna | last post by:
Guys, I have a Windows 2003 running Exchange 2003. On this machine I'm trying to run an ASP.NET webform to send an e-mail. The codebehing my ASPX is like this: Dim NewMail As New CDO.Message NewMail.From = From NewMail.To = Msgto
1
1785
by: Sophie | last post by:
Hi, I'm not very good in english so excuse me if I have difficulty to explain my problem. I want to send a mail with VB.net with this code : Option Strict On Public Class Form1 Inherits System.Windows.Forms.Form
2
10670
by: Landley | last post by:
Hello, Is there a way of creating an email, attaching a file and sending using the client's default mail client? I am looking for a none email client specific solution that does not involve specifying parameters such as mail servers etc. Cheers,
2
3881
by: Husam | last post by:
Hi EveryBody: I made windows application project as e-mail sender. This project consist of 13 textbox and one label and one button. I add {system.web.dll} as refrance to this project to help me using some mail functions after I got hint from Mr. Paul Remblance to do so. The code is like the follwoing: Imports System.Web.Mail
2
2491
by: Robert Dufour | last post by:
I tried using (vs2003 Vb.Net) Public Sub SendEmail( _ ByVal Recipient As String, _ ByVal Sender As String, _ ByVal Subject As String, _ ByVal Message As String, _
2
2633
by: oyster | last post by:
I find that the existing email moudle is some hard for me to understand, especially the part of how to set the CC, BCC and attach the files. Is there any more easy one like this p-code? import easyemail smtpserver=easyemail.server('something') smtpserver.login('usr@gmail.com', pwd) newletter=smtpsever.letter(smtpserver) newletter.sendto=...
1
2406
by: Chitu03 | last post by:
Hi I am already send a mail using Php with some attachement into it. My Problem is the attachement file is in my Database(mysql). I don't know how can i get from database and then add to my mail. <?php function send_mail($emailaddress, $fromaddress, $emailsubject, $body, $ccaddress, $attachments=false) { $eol="\r\n"; ...
1
6823
by: maxxxxel | last post by:
Hi Can anyone help me with some asp code , I changed the code to use CDO.message instead of the old cdont.sys to send mail from a ASP webpage which works fine. Our problem is that when we send mail externally to a internet email site like Gmail the PDF is sent but is corrupted because CDOSYS ends up using binary encoding rather than Base64...
1
6541
by: sxwend | last post by:
I am trying to use the following post results (http://www.thescripts.com/forum/thread189759.html) and add another requirement. I need to send the results to just the email addresses that the query specifies for each record... Essentially this is a make shift Ordering Tool and I want to be able to notify the receiver of the order and its specifics....
0
7490
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7682
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7935
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7780
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6009
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5069
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3465
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1911
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1037
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.