Email using CDO

Discussion in 'Site Programming, Development and Design' started by tpaddock, Apr 2, 2011.

  1. Hello
    I am in the process of moving my website to Winhost however I am not able to get my email forms to work. Everything else is working fine including all connections to my MS SQL 2008 db. Could someone please point me in the right direction on how to set these email forms up correctly?
    I dont know if this will help but here is a couple of snippets of the code for one of my pages:
    Thanks in advance!!

    <%
    dim sql, rs, rs1, reportID, mail, body
    dim jobSite, jobSiteNum, reportDate, supervisor, weather, temperature, comment, problem
    dim emailTo, emailToArr, emailSubject, temp, header

    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open connString
    set rs = Server.CreateObject("ADODB.Recordset")
    set rs1 = Server.CreateObject("ADODB.Recordset")
    reportID = Request.Form("reportID")
    if reportID <> "" Then
    sql = "SELECT * FROM TblReport r, TblJobSite js, TblUser u " & _
    "WHERE r.JobSiteID = js.JobSiteID AND r.SupervisorID = u.UserID " & _
    "AND ReportID = " & reportID
    rs.Open sql, conn
    if NOT rs.EOF Then
    jobSite = rs.Fields("JobSiteName")
    jobSiteNum = rs.Fields("JobSiteNum")
    reportDate = rs.Fields("ReportDate")
    supervisor = rs.Fields("FirstName") & " " & rs.Fields("LastName")
    if not isnull(rs.Fields("WeatherID")) Then
    sql = "SELECT * FROM TblWeather WHERE WeatherID = " & rs.Fields("WeatherID")
    rs1.Open sql, conn
    if not rs1.EOF Then
    weather = rs1.Fields("WeatherName")
    end If
    rs1.Close
    end If
    temperature = rs.Fields("Temperature")
    comment = rs.Fields("Comment")
    problem = rs.Fields("Problem")
    end if
    rs.Close

    emailTo = Request.Form("txtEmailTo")
    emailToArr = Split(emailTo,";")
    emailSubject = Request.Form("txtEmailSubject")

    set mail = Server.CreateObject("CDO.Message")
    mail.From = getSetting("pEmailFrom")
    mail.To = emailTo
    mail.BCC = "user@mydomain.com"

    mail.Subject = emailSubject

    ----------

    'mail.Body = body
    'mail.AddEmbeddedImage Server.MapPath("/Images/Calendar Email Logo.jpg"), "Logo" ' Embed logo in email body
    'mail.IsHTML = true
    mail.HTMLBody = body

    'Attach pictures to email
    dim path
    path = Server.MapPath("Pictures")

    sql = "SELECT * FROM TblReportPicture WHERE ReportID = " & reportID
    rs.Open sql, conn
    if NOT rs.EOF then
    do until rs.EOF
    mail.AddAttachment path & "/" & rs.Fields("Filename")
    rs.MoveNext
    loop
    end if
    rs.Close

    On Error Resume Next
    mail.Send
    set mail = nothing

    if Err <> 0 then
    Response.Write "We are sorry but an error has occurred when sending email.<br><br>"
    Response.Write "Error description: " & Err.Description
    else
    'Add record to Report Email table
    rs.Open "TblReportEmail", conn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("EmailTo") = emailTo
    rs.Fields("Subject") = emailSubject
    rs.Fields("EmailBy") = Session.Contents("UserFullName")
    rs.Fields("ReportID") = reportID
    rs.Update
    rs.Close

    Response.Write "Report # " & reportID & " has been sent<br><br>"
    Response.Write "<a href='ReportMain.asp'>Back to Report Main</a>"
    %>
    <script type="text/javascript">
    <!--
    window.setTimeout("window.location='ReportMain.asp'",2500);
    -->
    </script>
    <%
    end if
    end if
    conn.Close
    set rs = nothing
    set rs1 = nothing
    set conn = nothing
    %>
     
    Last edited by a moderator: Oct 14, 2015
  2. Ray

    Ray

    Quickly looking at your code I did not see any SMTP authentication being passed through your code. Our server requires that you first log into it before it will send out the email.

    Try looking at this kb article for an example code on how to use CDO to send out email through our email server.

    http://support.Winhost.com/KB/a617/how-to-send-mail-using-cdo.aspx
     
    Last edited by a moderator: Oct 14, 2015
  3. Body does not execute

    Thanks for the response Ray!
    I am now able to send the email successfully but the email only includes the Subject line... what do I need to do to have the body run as well? Here is what I have so far:
    <%
    dim sql, rs, rs1, reportID, mail, body
    dim jobSite, jobSiteNum, reportDate, supervisor, weather, temperature, comment, problem
    dim emailTo, emailToArr, emailSubject, temp, header

    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open connString
    set rs = Server.CreateObject("ADODB.Recordset")
    set rs1 = Server.CreateObject("ADODB.Recordset")
    reportID = Request.Form("reportID")
    if reportID <> "" Then
    sql = "SELECT * FROM TblReport r, TblJobSite js, TblUser u " & _
    "WHERE r.JobSiteID = js.JobSiteID AND r.SupervisorID = u.UserID " & _
    "AND ReportID = " & reportID
    rs.Open sql, conn
    if NOT rs.EOF Then
    jobSite = rs.Fields("JobSiteName")
    jobSiteNum = rs.Fields("JobSiteNum")
    reportDate = rs.Fields("ReportDate")
    supervisor = rs.Fields("FirstName") & " " & rs.Fields("LastName")
    if not isnull(rs.Fields("WeatherID")) Then
    sql = "SELECT * FROM TblWeather WHERE WeatherID = " & rs.Fields("WeatherID")
    rs1.Open sql, conn
    if not rs1.EOF Then
    weather = rs1.Fields("WeatherName")
    end If
    rs1.Close
    end If
    temperature = rs.Fields("Temperature")
    comment = rs.Fields("Comment")
    problem = rs.Fields("Problem")
    end if

    rs.Close

    emailTo = Request.Form("txtEmailTo")
    emailToArr = Split(emailTo,";")

    'set mail = Server.CreateObject("Persits.MailSender")
    set objMessage = createobject("cdo.message")
    set objConfig = createobject("cdo.configuration")
    Set Flds = objConfig.Fields
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.mydomain.com"
    ' ' Passing SMTP authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'basic (clear-text) authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") ="postmaster@mydomain.com"
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") ="mypassword"
    Flds.update
    Set objMessage.Configuration = objConfig
    Set Flds = objConfig.Fields
    objMessage.To = emailTo
    objMessage.From = getSetting("pEmailFrom")
    objMessage.Subject = Request.Form("txtEmailSubject")
    objMessage.fields.update
    objMessage.HTMLBody = body

    body = "<html><head><title>Report</title>"
    body = body & "<style type='text/css'>body, td { font-family:Verdana; font-size:11px; } </style>"
    body = body & "</head><body>"
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    .....
    body = body & "</table>"

    body = body & "</body></html>"


    Thanks again for your help!
     
  4. Ray

    Ray

    What do you mean "the body run as well"? The body is not coming through? If you are passing the body as a variable, you may want to hard code it for now just to test it out.
     
  5. Body is not coming through

    I inserted the following and it did come through

    objMessage.HTMLBody = "This is a test sent from CDO using smtp authentication."

    Do you know how I get the body with VB script to come through?
     
  6. Ray

    Ray

    Where is the email body coming from? Is it coming from the database you seem to be connecting to or is it coming from a web form on your web site?
     
  7. Here is my code for the entire page...

    <table border="0" cellspacing="0" cellpadding="0" width="100%">
    <tr>
    <td class="pagetitle" style="font-weight: bold">DAILY REPORTS MANAGEMENT :: EMAIL REPORT</td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    </tr>
    <tr>
    <td align="center">
    <%
    dim sql, rs, rs1, reportID, mail, body
    dim jobSite, jobSiteNum, reportDate, supervisor, weather, temperature, comment, problem
    dim emailTo, emailToArr, emailSubject, temp, header

    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open connString
    set rs = Server.CreateObject("ADODB.Recordset")
    set rs1 = Server.CreateObject("ADODB.Recordset")
    reportID = Request.Form("reportID")
    if reportID <> "" Then
    sql = "SELECT * FROM TblReport r, TblJobSite js, TblUser u " & _
    "WHERE r.JobSiteID = js.JobSiteID AND r.SupervisorID = u.UserID " & _
    "AND ReportID = " & reportID
    rs.Open sql, conn
    if NOT rs.EOF Then
    jobSite = rs.Fields("JobSiteName")
    jobSiteNum = rs.Fields("JobSiteNum")
    reportDate = rs.Fields("ReportDate")
    supervisor = rs.Fields("FirstName") & " " & rs.Fields("LastName")
    if not isnull(rs.Fields("WeatherID")) Then
    sql = "SELECT * FROM TblWeather WHERE WeatherID = " & rs.Fields("WeatherID")
    rs1.Open sql, conn
    if not rs1.EOF Then
    weather = rs1.Fields("WeatherName")
    end If
    rs1.Close
    end If
    temperature = rs.Fields("Temperature")
    comment = rs.Fields("Comment")
    problem = rs.Fields("Problem")
    end if
    rs.Close
    emailTo = Request.Form("txtEmailTo")
    emailToArr = Split(emailTo,";")

    'set mail = Server.CreateObject("Persits.MailSender")
    set objMessage = createobject("cdo.message")
    set objConfig = createobject("cdo.configuration")
    Set Flds = objConfig.Fields
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.mydomain.com"
    ' ' Passing SMTP authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'basic (clear-text) authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") ="postmaster@mydomain.com"
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") ="mypassword"
    Flds.update
    Set objMessage.Configuration = objConfig
    Set Flds = objConfig.Fields
    objMessage.To = emailTo
    objMessage.From = getSetting("pEmailFrom")
    objMessage.Subject = Request.Form("txtEmailSubject")
    objMessage.fields.update
    objMessage.HTMLBody = body

    body = "<html><head><title>Report</title>"
    body = body & "<style type='text/css'>body, td { font-family:Verdana; font-size:11px; } </style>"
    body = body & "</head><body>"
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"

    'Report header
    header = getSetting("pCompanyAddress") & ", " & getSetting("pCompanyCity") & ", " & getSetting("pCompanyProvince") & " " & getSetting("pCompanyPostalCode") & _
    "<br>Phone: " & getSetting("pCompanyPhone") & "&nbsp;&nbsp;Fax: " & getSetting("pCompanyFax") & "<br><br>" & _
    "<h4 align='center'>Project Daily Report</h4>"
    body = body & "<tr><td colspan='2' align='center'><img src='../Calendar Email Logo.jpg' border='0' /><br>" & header & "</td></tr>"

    body = body & "<tr><td width='120'><b>Report #:</b></td><td><b>" & reportID & "</b></td></tr>"
    body = body & "<tr><td><b>Job Site:</b></td><td><b>" & jobSite & "</b></td></tr>"
    body = body & "<tr><td><b>Job Site #:</b></td><td><b>" & jobSiteNum & "</b></td></tr>"
    body = body & "<tr><td><b>Date:</b></td><td><b>" & FormatDateTime(reportDate,1) & "</b></td></tr>"
    body = body & "<tr><td><b>Site Supervisor:</b></td><td><b>" & supervisor & "</b></td></tr>"
    body = body & "<tr><td><b>Weather:</b></td><td>" & weather & "</td></tr>"
    body = body & "<tr><td><b>Temperature:</b></td><td>" & temperature & "</td></tr></table>"

    'Trades on site
    Dim i
    sql = "SELECT * FROM TblTrade t, TblReportTrade rt " & _
    "WHERE t.TradeID = rt.TradeID AND rt.ReportID = " & reportID & _
    " ORDER BY TradeName"
    rs.Open sql, conn
    if NOT rs.EOF Then
    i = 0
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    body = body & "<tr><td><b>Trades on site:</b></td><td>Number of Men</td><td>&nbsp;</td><td><b>Trades on site:</b></td><td>Number of Men</td></tr>"
    do until rs.EOF
    If(i Mod 2 = 0) Then
    body = body & "<tr><td>" & rs.Fields("TradeName") & "</td><td>" & rs.Fields("MenNumber") & "</td><td>&nbsp;</td>"
    Else
    body = body & "<td>" & rs.Fields("TradeName") & "</td><td>" & rs.Fields("MenNumber") & "</td></tr>"
    End If
    i = i + 1
    rs.MoveNext
    loop
    end If
    If (i Mod 2 = 1) Then
    body = body & "<td>&nbsp;</td><td>&nbsp;</td>></tr>"
    End If
    body = body & "</table>"
    rs.Close

    'Comments and Problems
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    body = body & "<tr><td valign='top'><b>Comments:</b></td><td>" & comment & "</td></tr>"
    body = body & "<tr><td valign='top'><b>Problems:</b></td><td>" & problem & "</td></tr>"
    body = body & "</table>"

    'Accidents
    sql = "SELECT * FROM TblReportAccident WHERE ReportID = " & reportID
    rs.Open sql, conn
    If Not rs.EOF Then
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    body = body & "<tr><td width='120'><b>Accident:</b></td><td colspan='3'>" & rs.Fields("AccidentOn") & "</td></tr>"
    body = body & "<tr><td>&nbsp;</td><td colspan='3'>" & rs.Fields("Description") & "</td></tr>"

    'Accident WCB
    sql = "SELECT * FROM TblReportAccident ra, TblWCBDoc w WHERE ra.ReportAccidentID = w.ReportAccidentID AND ra.ReportID = " & reportID
    rs1.Open sql, conn
    body = body & "<tr><td>&nbsp;</td><td><b>WCB File Name</b></td><td><b>Date Uploaded</b></td><td>&nbsp;</td></tr>"
    If Not rs1.EOF Then
    Do Until rs1.EOF
    body = body & "<tr><td>&nbsp;</td><td>" & rs1("FileName") & "</td><td>" & rs1("DateUploaded") & "</td><td><a href='http://../" & rs1("FileName") & "' target='_blank'>View</a></td></tr>"
    rs1.MoveNext
    Loop
    Else
    body = body & "<tr><td>&nbsp;</td><td>-</td><td>-</td><td>&nbsp;</td></tr>"
    End If
    rs1.Close

    sql = "SELECT * FROM TblReportAccident ra, TblWCBPicture w WHERE ra.ReportAccidentID = w.ReportAccidentID AND ra.ReportID = " & reportID
    rs1.Open sql, conn
    body = body & "<tr><td>&nbsp;</td><td><b>Picture File Name</b></td><td><b>Description</b></td><td>&nbsp;</td></tr>"
    If Not rs1.EOF Then
    Do Until rs1.EOF
    body = body & "<tr><td>&nbsp;</td><td>" & rs1("FileName") & "</td><td>" & rs1("Description") & "</td><td><a href="../" & rs1("FileName") & "' target='_blank'>View</a></td></tr>"
    rs1.MoveNext
    Loop
    Else
    body = body & "<tr><td>&nbsp;</td><td>-</td><td>-</td><td>&nbsp;</td></tr>"
    End If
    rs1.Close
    body = body & "</table>"

    End If
    rs.Close

    'Equipment
    Dim j
    sql = "SELECT * FROM TblEquipment e, TblReportEquipment re " & _
    "WHERE e.EquipmentID = re.EquipmentID AND re.ReportID = " & reportID & _
    " ORDER BY EquipmentName"
    rs.Open sql, conn
    if NOT rs.EOF Then
    j = 0
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    body = body & "<tr><td><b>Equipment:</b></td><td>Number</td><td>&nbsp;</td><td><b>Equipment:</b></td><td>Number</td></tr>"
    do until rs.EOF
    If(j Mod 2 = 0) Then
    body = body & "<tr><td>" & rs.Fields("EquipmentName") & "</td><td>" & rs.Fields("Number") & "</td><td>&nbsp;</td>"
    Else
    body = body & "<td>" & rs.Fields("EquipmentName") & "</td><td>" & rs.Fields("Number") & "</td></tr>"
    End If
    j = j + 1
    rs.MoveNext
    loop
    end if
    If (j Mod 2 = 1) Then
    body = body & "<td>&nbsp;</td><td>&nbsp;</td>></tr>"
    End If
    body = body & "</table>"
    rs.Close

    'Site Personnel
    sql = "SELECT * FROM TblUser u, TblReportUser ru " & _
    "WHERE u.UserID = ru.UserID AND ru.ReportID = " & reportID & _
    " ORDER BY FirstName, LastName"
    rs.Open sql, conn
    if NOT rs.EOF then
    body = body & "<table border='0' cellspacing='0' cellpadding='3'>"
    body = body & "<tr><td><b>Site Personnel:</b></td><td>Number of Hours</td></tr>"
    do until rs.EOF
    body = body & "<tr><td>" & rs.Fields("FirstName") & " " & rs.Fields("LastName") & "</td><td>" & rs.Fields("Hours") & "</td></tr>"
    rs.MoveNext
    Loop
    end if
    rs.Close
    body = body & "</body></html>"

    On Error Resume Next
    objMessage.Send
    set mail = nothing

    if Err <> 0 then

    'Add record to Report Email table
    rs.Open "TblReportEmail", conn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("EmailTo") = emailTo
    rs.Fields("Subject") = emailSubject
    rs.Fields("EmailBy") = Session.Contents("UserFullName")
    rs.Fields("ReportID") = reportID
    rs.Update
    rs.Close

    Response.Write "Report # " & reportID & " has been sent<br><br>"
    Response.Write "<a href='ReportMain.asp'>Back to Report Main</a>"
    %>
    <%
    end if
    end if
    conn.Close
    set rs = nothing
    set rs1 = nothing
    set conn = nothing
    %>
    </td>
    </tr>
    </table>
     
  8. DB

    It is coming from the db Im connecting to.
     
  9. Ray

    Ray

    It doesn't look like you got the form name to...

    objMessage.HTMLBody = body

    .correct.

    Try using...

    objMessage.HTMLBody = Request.Form("body")
     
  10. :(
    Sorry it still doesnt work?! I dont know what else to do. It was very easy to configure using localhost.
    Any other ideas?
    Thanks in advance
     
  11. Ray

    Ray

    It is really difficult debugging a full blown application. The best thing to do is to setup a small app that connects to your database and some sample table. Set it up with some web forms to send out email. This way you can break it down line by line much more easily. I know it maybe a pain in the butt to setup a smaller app simply for a test, but in the long run it is worth just to avoid the confusion and headaches.
     
  12. I tried the follow simple code and it doesnt work?? The body is not being sent in the email.
    There must be soming simple that I am doing wrong?!

    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>
    <%
    set objMessage = createobject("cdo.message")
    set objConfig = createobject("cdo.configuration")
    Set Flds = objConfig.Fields
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.mydomain.com"

    ' ' Passing SMTP authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'basic (clear-text) authentication
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") ="postmaster@mydomain.com"
    Flds.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") ="xxxxxx"
    Flds.update
    Set objMessage.Configuration = objConfig
    objMessage.To = "test@mydomain.com"
    objMessage.From = "admin@mydomain.com"
    objMessage.Subject = "CDO Test"
    objMessage.fields.update
    objMessage.HTMLBody = strHTML

    strHTML = strHTML & "Hey there,"
    strHTML = strHTML & "<br> <br>"
    strHTML = strHTML & "Blah, blah, blah"
    strHTML = strHTML & "<br> <br>"
    strHTML = strHTML & "Cheers,<br> <br>"
    strHTML = strHTML & ""

    objMessage.Send
    %>
    </html>
     
  13. Ray

    Ray

    Try setting it up with just a one line body first.

    strHTML = strHTML & "Hey there,"
     
  14. No Luck?
    Do you know of any reference material that I can look at?
    Thx
     
  15. Ray

    Ray

  16. No Luck.
    I might have to try another hosting company to see if I have any more luck? :(
     
  17. Hi Ray
    I have been talking with another person on another forum and he ask the question "Does the web server has access to the e-mail servers?"
    He followed up by saying
    "Does the proxy account has access to it. You should ask them if it does, and if so, what server settings to use."
    Could you let me know what to tell him?
    Thx
     
  18. Ray

    Ray

    Yes. The web server has access to the email server. Remember if you are able to manually type the body, instead of passing the variable through, and the email comes through means that the application is able to connect to the mail server and relay it out. The problem is the form page, passing the body from the form page to the actual CDO page to set out the email. That's where it seems to be breaking down.
     

Share This Page