Avatar of Scott Palmer
Scott PalmerFlag for United States of America

asked on 

Code VBA in HTML to send email in Outlook

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




HTMLOutlookVBA

Avatar of undefined
Last Comment
Scott Palmer
Avatar of Bill Prew
Bill Prew

Try something more like this.  You need to handle the statements differently than the assembly of the HTML data for the email.  I typically like to build up the HTML body content as a string first, and then assign it before sending.  It makes it easier to debug, and I like to concatenate on to it as I go rather than have the long run on continue lines.

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.

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)

    Dim sHTML
    sHTML = ""
   
    With oEmail
        .Recipients.Add "scott.d.palmer@healthnet.com"
        .Subject = "Cred Distribution"
        sHTML = "Hello, below is the Cred Distribution report for " & Date & "<p> "
        sHTML = sHTML & "<tr> "
        sHTML = sHTML & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Associate Name </td> "
        sHTML = sHTML & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Initial Credentialing </td> "
        sHTML = sHTML & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'> Recredentialing </td></tr>"
       
        rs.MoveFirst
        Do
            sHTML = sHTML & "<tr><td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("AssocName") & "</td> "
            sHTML = sHTML & "<td style='padding: 5px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Initial") & "</td> "
            sHTML = sHTML & "<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

        sHTML = sHTML & "</tr> "
        sHTML = sHTML & "</table></div></body></html> "
        sHTML = sHTML & "<p>Scott Palmer "
        sHTML = sHTML & "<br>Business Systems Analyst "
        sHTML = sHTML & "<br>Direct: 916-595-8215 "
        sHTML = sHTML & "<br>Scott.d.palmer@ healthnet.com "
        sHTML = sHTML & "<p>Company Name "
        sHTML = sHTML & "<br>1111 Yemen Street , Yemen, CA 95670 "
        sHTML = sHTML & "<p>NEW: Customer Relations Call Center: 800-565-0588 "
        sHTML = sHTML & "<br>Monday-Friday 8:00 a.m. to 5:00 p.m. PST"

        .HTMLBody = sHTML
        .Send
    End With
   
End Sub

Open in new window


»bp
Try something like... 


txtHTMLHeader = "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

 txtHTMLBody = txtHTMLBody & "<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

 txtHTMLFooter = "</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"
        .HTMLBody = txtHTMLHeader & txtHTMLBody & txtHTMLFooter
        .Send
    End With
ASKER CERTIFIED SOLUTION
Avatar of Daniel Pineault
Daniel Pineault

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
I' would go with Bill: String concatenation, as it make errors easier to spot.
Avatar of Scott Palmer
Scott Palmer
Flag of United States of America image

ASKER

Thanks Daniel.

Scott
Outlook
Outlook

Microsoft Outlook is a personal information manager from Microsoft, available as a part of the Microsoft Office suite. Although often used mainly as an email application, it also includes a calendar, task manager, contact manager, note-taker, journal, and web browser.

105K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo