asked on

Embedding Table in Email Message Using VBA Code

I have created VBA code in Access that takes the contents of a recordset and places the data in the body of an email message. My code looks like this:

With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("person1@somewhere.com")
            objOutlookRecip.Type = olTo
            Set objOutlookRecip = .Recipients.Add("person2@somewhere.com")
            objOutlookRecip.Type = olCC
            .Subject = "Something "
            
            Dim rstData As ADODB.Recordset
            Set rstData = New ADODB.Recordset
            rstData.ActiveConnection = CurrentProject.Connection
            
            rstData.Open "SELECT * FROM qryAny"
            
            Dim strData As String
            
            Do Until rstData.EOF
            
                strData = strData & rstData("FullName") & vbNewLine
                rstData.MoveNext
            Loop

            .Body = strData

            For Each objOutlookRecip In .Recipients
                objOutlookRecip.Resolve
            Next
     
            .Save
           .send
End With

Open in new window

That code all works great. The problem is that I want to output additional fields (not just FullName), and I want the data to appear in a table so that the data is formatted nicely. I have tried separating the fields with tabs, but I have no way of knowing the length of the data in each field, so the data does not line up correctly.

Is this possible? Any help would be much appreciated!

Jack Leach

If you can get away with using an HTML email (which is really what you need for any sort of decent formatting), then you'll need to modify your code to build the body using appropriate html <table> tags. Here's the markup for an HTML table:

<table>
  <tr>
    <td>content row1col1</td>
    <td>content row1col2
  </tr>
  <tr>
    <td>content row2col1</td>
    <td>content row2col2</td>
  </tr>
</table>

Open in new window

Thus, you build a loop that reads through the records, usually adding fields to each <td> element for each row. Something like this:

Dim html As String

html = <table>

While Not rs.eof
  html = html & "<tr><td>" & rs("Field1") & "</td><td>" & rs("field2") & "</td></tr>
  rs.movenext
Wend

html = html & "</table>"

.Body = html

Open in new window

Along those lines anyway, you get the idea. Hopefully that gets you in the right direction?

Cheers,
-jack

TechMommy

ASKER

Hi Jack,

It looks like that will work. I have to wait for the client to implement and test it. I'll award your points as soon as I verify that it solves my problem. That may be tomorrow.

Thank you very much for your help.

Alison

TechMommy

ASKER

Hi Jack,

I implemented your code as follows:

strPatients = "<HTML><table>"

           
            Do Until rstDailyPatient.EOF
            
                strPatients = strPatients & "<tr><td>" & rstDailyPatient("Name_First") & " " & rstDailyPatient("Name_Last") & "</td><td>" & rstDailyPatient("City") & ", " & _
                    rstDailyPatient("State")  &  "</td></tr> &  _
                    vbNewLine
                
            Loop
            
            strPatients = strPatients & "</table></HTML>"

       With objOutlookMsg
            .BodyFormat = olFormatHTML
            .HTMLBody = "HTML Content"
            .Body = strPatients
       End With

Open in new window

All the text AND tags appear in the message as text. I tried this with and without the HTML tags.

<tr><td>name1</td><td>somewhere, CA</td></tr>
<tr><td>Name 2</td><td>somewhere else, CA</td></tr>

Any ideas?

Thank you,

Alison

TechMommy

ASKER

Hi Again Jack,

I was just looking at my code...Is it the vbNewLine that is messing me up?

Alison

TechMommy

ASKER

FYI, I tried it without the vbNewLine and got the same results.

ASKER CERTIFIED SOLUTION

Jack Leach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial