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 Name | Initial | Recred | Total |
Jim | 3 | 12 | 15 |
Sandy | 3 | 11 | 14 |
Ray | 4 | 12 | 16 |
Kelly | 3 | 12 | 15 |
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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.