2

I am trying to loop through a database query and send emails to the email addresses I get from the query. Here is my piece of code.

do until rs.EOF
    Set myMail = CreateObject("CDO.Message")
    myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
    myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="relay-hosting.secureserver.net"
    myMail.Configuration.Fields.Update
    myMail.Subject= subject
    myMail.From="something@Something.Something"
    myMail.To = rs("Email")
    myMail.HTMLBody = strMessage & "Some Message"
    myMail.Send
    Set myMail = Nothing
    rs.MoveNext
loop

I have searched and tried different solution without any luck. Following line of the code seems to have an issue, but i could not find what

myMail.To = rs("Email")

Before giving any answer or suggestion please keep in mind,

  • This code works fine when I use any static email address in "TO" attribute of CDO Message object
  • The query is giving accurate results
  • I receive 500 Internal server error when i use dynamic email (email from the query) in the "TO" attribute of CDO Message object. I don't have the access to the IIS to enable detailed error messages.
Atif
  • 280
  • 1
  • 11
  • Without the actual error message it is nearly impossible to tell. When you say "accurate results" what does that mean? What is the return from the query and what is the hard coded value you used? – Sean Lange Feb 11 '16 at 20:38
  • 1
    Check the results of your query very carefully. At least one of the `rs("Email")` values is not a valid email address. There's no other explanation. – Keith Feb 11 '16 at 20:58
  • @SeanLange From "Accurate Results" i mean query result is giving me the desired email address. I hard coded my email address to receive emails, and i received them. But when i use dynamic value, it gives error. Please let me know if you are still not clear what i mean – Atif Feb 11 '16 at 21:00
  • 1
    As @Keith said, at least one of your values is not a valid email address. Since you stuck using classic asp and can't make IIS provide you anything useful you may have to resort to serious old school logging. Have your code log every email address, the last on it logs will be the bad one. You could also add some error handling so your code doesn't throw a 500 error. "on error ..." – Sean Lange Feb 11 '16 at 21:04
  • 2
    Enable IIS and ASP on your own machine and try to reproduce the error ... enable detailed error messages. without the error message is nearly impossible to tell whats happening – Rafael Feb 11 '16 at 22:44

2 Answers2

2

Well, as others observed in the comments, without the actual error message it`s hard to tell. There are many possible sources:

  • the recordset is closed at this point for some reason
  • there is no "Email" field in the recordset
  • E-Mail address invalid
  • ...

The good thing is, you can use classic ASP error handling to find the error like this:

on error resume next

do until rs.EOF
    Set myMail = CreateObject("CDO.Message")
    myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
    myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="relay-hosting.secureserver.net"
    myMail.Configuration.Fields.Update
    myMail.Subject= subject
    myMail.From="something@Something.Something"
    myMail.To = rs("Email")
    myMail.HTMLBody = strMessage & "Some Message"
    myMail.Send

    if err then
        Response.write "<code>" & err.Source & " - " & err.Description & "</code>"
    end if

    Set myMail = Nothing
    rs.MoveNext
loop

on error goto 0

There are of course better ways to handle these errors, even in classic ASP (search SO or see this solution). This is only to help you find the error when you don't have access to the IIS settings.

Community
  • 1
  • 1
gpinkas
  • 2,291
  • 2
  • 33
  • 49
  • There is email field in the recordset, Emails are in valid format (i have seen them all by writing them to the page), cannot think of any reason that can close the recordset. Tried error handling but the error code is 0. Used web.config file to overwrite error display settings and now i see the error of request timeout. But the same code executes in a few seconds when i use static email address in the "TO" attribute to send email. – Atif Feb 12 '16 at 10:58
  • maybe `rs("Email")` is NULL somewhere? Add CStr() to convert it. – gpinkas Feb 12 '16 at 11:51
  • I have double checked and every row in the recordset has a valid email address. I had tried CStr() without any luck. Anyways thanks for every one's time here. I'll have to keep digging to solve this issue. – Atif Feb 12 '16 at 11:57
  • @gpinkas or `rs("Email") & ""` would be enough. Personally not a fan of setting `ADODB.Recordset` variables directly to objects, usually go into local variables first where they can be check manipulated before being passed on to object properties etc. – user692942 Feb 12 '16 at 11:57
  • 1
    @Atif: Well, good luck debugging! Maybe there is an issue with multiple mails? see here: http://stackoverflow.com/questions/10482053/cdo-message-send-causes-script-execution-timeout – gpinkas Feb 12 '16 at 12:01
  • @Atif Look for an invalid character (space, carriage return, control character...) in one of the email addresses. – Keith Feb 12 '16 at 13:50
0

I once had a similar problem and found that it was caused by bad email addresses where their server responded with an error. That error broke the loop. It is impossible to keep unused email addresses out of a database, so to solve the problem I simply added On Error Resume Next

ObjSendMail.To = "Me <my@example.com>"
ObjSendMail.Subject = strSubject
ObjSendMail.From = strFromName & " <no-reply@example.com>"
ObjSendMail.ReplyTo = strFromName & " <" & strFromEmail & ">"
ObjSendMail.BodyPart.Charset = "UTF-8"
ObjSendMail.TextBody = strEmailBody  
ObjSendMail.TextBodyPart.Charset = "UTF-8"
On Error Resume Next
ObjSendMail.Send
Set ObjSendMail = Nothing

Then it won't matter if the routine trips on any more failed emails because it will ignore them and move onto the next email.

WilliamK
  • 821
  • 1
  • 13
  • 32