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).
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
Not sure if this is perfect yet, but take a look / try and see if you get the idea and can work it into shape for your need.
Open in new window
»bp