troubleshooting Question

Code VBA in HTML to send email in Outlook

Avatar of Scott Palmer
Scott PalmerFlag for United States of America asked on
HTMLOutlookVBA
5 Comments1 Solution18 ViewsLast Modified:
In my Access program I have code to send an email that will include the results that come from a table.  I was able to get it to work, but only for one line.  There are 4 lines in the table (and the title line).
 
Assoc NameInitialRecred Total
Jim31215
Sandy31114
Ray41216
Kelly31215

To do this I am looping through to get all of the lines.  The problem Access does not like my syntax.  I have attached all the code and pasted it here:
EmailCode.txt
       Public Sub Temp1()
    Dim rs, sql
    Set rs = CreateObject("ADODB.Recordset")
 
    sql = "SELECT * FROM tblMKSummary"
         
    rs.Open sql, CurrentProject.Connection
    rs.MoveFirst
 
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    Set oEmail = oApp.CreateItem(olMailItem)
   
    With oEmail
        .Recipients.Add "scott.d.palmer@healthnet.com"
        .Subject = "Cred Distribution"
        .HTMLBody = "Hello, below is the Cred Distribution report for " & Date & "<p> " _
        & "<tr> " _
        & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Associate Name </td> " _
        & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Initial Credentialing </td> " _
        & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Recredentialing </td></tr>"      
        rs.MoveFirst
        Do
        & "<tr><td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("AssocName") & "</td> " _
        & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Initial") & "</td> " _
        & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Recred") & "</td> " _
        & "rs.MoveNext" _
        & "Loop Until rs.EOF" _
        & "</tr> " _
        & "</table></div></body></html> " _
        & "<p>Scott Palmer " _
        & "<br>Business Systems Analyst " _
        & "<br>Direct: 916-555-5555 " _
        & "<br>Scott.d.palmer@ healthnet.com " _
        & "<p>Company Name " _
        & "<br>1111 Yemen Street , Yemen, CA 95670 " _
        & "<p>NEW: Customer Relations Call Center: 800-555-0000 " _
        & "<br>Monday-Friday 8:00 a.m. to 5:00 p.m. PST"
        .Send
    End With
   
End Sub

If I don't include the "Do" it is fine, but once I add it the code then goes red.  There has to be a way to put the loop in there to get all of the lines of the report.

Thanks,
Scott




ASKER CERTIFIED SOLUTION
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros