|
| |
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
 |
CDOSYS and multiple emails list - 2/24/2005 22:37:11
Can anybody take a look at the code below and tell me what is wrong? The code is basically two working asp scripts that I am trying to merge together. The code I have highlighted in red is the data retrieval portion where a connection is opened to a SQL database and a query is run to retrieve email addresses from a table. The code that I have highlighted in blue is a working CDOSYS script I use to send emails to predefined to and from addresses. Code that is in bold is my attempt to merge them together so the CDOSYS script will take the list generated from the connection (which was verified to work via a response.write) and then send the email to each person in the list.
Thanks
cliff
CODE:
<%
Dim cn, RS, SQL, Conn
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("Provider=SQLOLEDB;Server=CRPDALMSQ06;Database=ukdashSQL1;UID=IMU;pwd=hmfic2004;")
SQL = "Select * FROM emailtest"
set Conn=server.createobject("adodb.connection")
Conn.open cn
Set RS = Conn.Execute (SQL)
Do until RS.EOF
Email = RS(" Email" )
Const cdoSendUsingMethod = _
"http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSendUsingPort = 2
Const cdoSMTPServer = _
"http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = _
"http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cdoSMTPConnectionTimeout = _
"http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate = _
"http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cdoBasic = 1
Const cdoSendUserName = _
"http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cdoSendPassword = _
"http://schemas.microsoft.com/cdo/configuration/sendpassword"
Dim objConfig ' As CDO.Configuration
Dim objMessage ' As CDO.Message
Dim Fields ' As ADODB.Fields
' Get a handle on the config object and it's fields
Set objConfig = Server.CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
' Set config fields we care about
With Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "smtp3dns.mysite.com"
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPConnectionTimeout) = 10
.Item(cdoSMTPAuthenticate) = cdoBasic
.Update
End With
Set objMessage = Server.CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig
With objMessage
.To = Email
.From = "me@mysite.com"
.Subject = "EMF Approval Requested"
.textbody = "Please click the Link below to review/approve a EMF submitted for Project:"
.Send
End With
End If
RS.MoveNext
loop
Set Fields = Nothing
Set objMessage = Nothing
Set objConfig = Nothing
Set RS = nothing
Conn.close
%>
< Message edited by cliffdeen -- 2/24/2005 22:43:17 >
|
|
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 8:40:57
All that happens is when trying to access the page via web - I get a Page cannot be displayed error. Separating the two into single pages - no error. So, I figure I have something out of order or Dim's conflicting or something like that. At a loss as to what to try next. everything I have tried, just seems to take me further from the solution. Any and all help would be greatly appreciated.
cliff
< Message edited by cliffdeen -- 2/25/2005 11:51:19 >
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 11:50:30
quote:
...
With objMessage
.To = Email
.From = "me@mysite.com"
.Subject = "EMF Approval Requested"
.textbody = "Please click the Link below to review/approve a EMF submitted for Project:"
.Send
End With
End If
RS.MoveNext
loop
Set Fields = Nothing
Set objMessage = Nothing
Set objConfig = Nothing
Set RS = nothing
Conn.close
%>
Just a quick guess and probably not the preferred method, but it's an easy way to test my theory. My theory is you keep trying to open the mail object yet you never close it during the loop; only AFTER the loop. Try changing the last to:
...
With objMessage
.To = Email
.From = "me@mysite.com"
.Subject = "EMF Approval Requested"
.textbody = "Please click the Link below to review/approve a EMF submitted for Project:"
.Send
End With
End If
Set Fields = Nothing
Set objMessage = Nothing
RS.MoveNext
loop
Set objConfig = Nothing
Set RS = nothing
Conn.close
%>
Hey, it's a very quick test.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 11:57:11
I tried changing the last lines as you detailed. Still a no go - HTTP 500 Page cannot be displayed error.
Any other ideas?
quote:
With objMessage
.To = Email
.From = "me@mysite.com"
.Subject = "EMF Approval Requested"
.textbody = "Please click the Link below to review/approve a EMF submitted for Project:"
.Send
End With
End If
Set Fields = Nothing
Set objMessage = Nothing
RS.MoveNext
loop
Set objConfig = Nothing
Set RS = nothing
Conn.close
%>
| |
|
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 12:55:56
Ok - I've made some progress - I now get it to send a email to the FIRST person from the query, but then it stops. I still get the page error (that doesn't bother me so much, as long as the email part works). Here is the code that is sending it to the first person on the list. So it obviously is not looping to pick up the rest of the names. Right now I only have 3 in the test query. HELP!
<%
Dim cn, RS, SQL, Conn
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("Provider=SQLOLEDB;Server=CRPDALMSQ06;Database=ukdashSQL1;UID=IMU;pwd=hmfic2004;")
'This next line is your SQL for the data you want to use
SQL = "Select * FROM emailtest"
set Conn=server.createobject("adodb.connection")
Conn.open cn
Set RS = Conn.Execute (SQL)
Dim email
For Each EMAIL In RS.Fields
email=(rs("email"))
Const cdoSendUsingMethod = _
"http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSendUsingPort = 2
Const cdoSMTPServer = _
"http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = _
"http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cdoSMTPConnectionTimeout = _
"http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate = _
"http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cdoBasic = 1
Const cdoSendUserName = _
"http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cdoSendPassword = _
"http://schemas.microsoft.com/cdo/configuration/sendpassword"
Dim objConfig ' As CDO.Configuration
Dim objMessage ' As CDO.Message
Dim Fields ' As ADODB.Fields
' Get a handle on the config object and it's fields
Set objConfig = Server.CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
' Set config fields we care about
With Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "smtp3dns.myemailserver.com"
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPConnectionTimeout) = 10
.Item(cdoSMTPAuthenticate) = cdoBasic
.Update
End With
Set objMessage = Server.CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig
With objMessage
.To = email
.From = "me@myemailserver.com"
.cc = "me@myemailserver.com"
.Subject = "LET ME KNOW IF YOU GET THIS!"
.textbody = "Hey this works:"
.Send
End With
Next
Set Fields = Nothing
Set objMessage = Nothing
Set objConfig = Nothing
Set RS = nothing
Conn.close
%>
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 13:25:59
quote:
For Each EMAIL In RS.Fields
email=(rs("email"))
1. One last "quick check" for code changes?
email=trim(rs("email"))
2. You said you only have 3 addresses in the DB right now. What happens if they're all the same address? IOW are they all valid, legitimate addresses?
What about putting "On Error Resume Next" at the top of the code? Maybe just something about the second record is bad?
3. Also, can you turn freindly messages off so we can see the 'real' error?
Just a few more things I'd check.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 13:41:09
Ok - First I tried the trim statement and no change - just one email to me.
I verified the email addresses and they are correct.
I went ahead and put the ON Error at the top of the code and got an interesting result - It now sends three emails to the same person (me - I am first on the list). In addition the page not displayed error went away and I now get the text line that I inserted outside the script.
So - it is looping but never grabs the next email address - dim statement wrong or do I need a array (scared of those - undestand concept - practice level =poor). ?
Finally, how do I turn off the friendly message function?
cliff
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 15:03:49
Try this code:
<%
Dim cn, RS, SQL, Conn, email, Z
Dim objConfig ' As CDO.Configuration
Dim objMessage ' As CDO.Message
Dim Fields ' As ADODB.Fields
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSendUsingPort = 2
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cdoBasic = 1
Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("Provider=SQLOLEDB;Server=CRPDALMSQ06;Database=ukdashSQL1;UID=IMU;pwd=hmfic2004;")
'This next line is your SQL for the data you want to use
SQL = "Select * FROM emailtest"
set Conn=server.createobject("adodb.connection")
Conn.open cn
Set RS = Conn.Execute (SQL)
DO WHILE not RS.EOF
email=(rs("x_email"))
' Get a handle on the config object and it's fields
Set objConfig = Server.CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
' Set config fields we care about
With Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "smtp3dns.myemailserver.com"
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPConnectionTimeout) = 10
.Item(cdoSMTPAuthenticate) = cdoBasic
.Update
End With
Set objMessage = Server.CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig
With objMessage
.To = email
.From = "me@myemailserver.com"
.cc = "me@myemailserver.com"
.Subject = "LET ME KNOW IF YOU GET THIS!"
.textbody = "Hey this works:"
.Send
End With
Set Fields = Nothing
Set objMessage = Nothing
Set objConfig = Nothing
RS.MoveNext
Loop
Set RS = nothing
Conn.close
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 15:08:32
quote:
For Each EMAIL In RS.Fields
Just to explain my code. I think the biggest problem was right there. You were looping thru the field names NOT the field values.
I changed it to a WHILE NOT EOF.
I also moved some of your DIM's around and stuff so they were'nt redefined, etc.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
cliffdeen
Posts: 155
Joined: 4/12/2004
From: Mckinney, TX
Status: offline
|
RE: CDOSYS and multiple emails list - 2/25/2005 18:29:14
Roger - once again YOU THE MAN! Worked like a champ and after seeing your change - it all makes sense. Also, as you can see from some of the code - I had already "borrowed" some of your other work you have posted - specifically the export to excel that works so well. Only thing I had to change was the rs("x-email") to just email. I figure you went to a bunch of trouble to copy my code and test it via the spooky db.
Thanks Again!
Code revisited for others to view:
quote:
<%
Dim cn, RS, SQL, Conn, email, Z
Dim objConfig ' As CDO.Configuration
Dim objMessage ' As CDO.Message
Dim Fields ' As ADODB.Fields
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSendUsingPort = 2
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cdoBasic = 1
Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("Provider=SQLOLEDB;Server=CRPDALMSQ06;Database=ukdashSQL1;UID=IMU;pwd=hmfic2004;")
'This next line is your SQL for the data you want to use
SQL = "Select * FROM emailtest"
set Conn=server.createobject("adodb.connection")
Conn.open cn
Set RS = Conn.Execute (SQL)
DO WHILE not RS.EOF
email=(rs("x_email"))
' Get a handle on the config object and it's fields
Set objConfig = Server.CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
' Set config fields we care about
With Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "smtp3dns.myemailserver.com"
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPConnectionTimeout) = 10
.Item(cdoSMTPAuthenticate) = cdoBasic
.Update
End With
Set objMessage = Server.CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig
With objMessage
.To = email
.From = "me@myemailserver.com"
.cc = "me@myemailserver.com"
.Subject = "LET ME KNOW IF YOU GET THIS!"
.textbody = "Hey this works:"
.Send
End With
Set Fields = Nothing
Set objMessage = Nothing
Set objConfig = Nothing
RS.MoveNext
Loop
Set RS = nothing
Conn.close
%>
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
