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
-
11 11274
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
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.
Throw in a
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
Throw in a
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!
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
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?
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
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 = "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:\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
- 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 = "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:\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
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 = "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:\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
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 = "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:\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
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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...
|
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...
|
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...
|
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...
|
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, _
|
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...
|
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....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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,...
|
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...
|
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...
|
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...
|
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,...
| |