473,698 Members | 2,185 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 11327
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
1513
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
3709
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
1793
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
10692
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
3888
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
2496
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
2646
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= newletter.sendcc=
1
2413
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"; $mime_boundary=md5(time()); # Common Headers $headers .= 'From:...
1
6840
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 encoding when creating the attachment. More information here: Anthonys Code My knowledge of ASP...
1
6561
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. q_Order_Detail_4email consists of ,,, and Example: Hello@mail.com,956,Modem,1000,xyz123...
0
8668
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 usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9152
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8885
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8855
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4358
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4612
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3037
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
2
2320
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.