?
Solved

Embedding Table in Email Message Using VBA Code

Posted on 2014-01-30
9
Medium Priority
?
8,986 Views
Last Modified: 2014-02-04
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!
0
Comment
Question by:TechMommy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39822573
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
0
 
LVL 11

Author Comment

by:TechMommy
ID: 39822720
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
0
 
LVL 11

Author Comment

by:TechMommy
ID: 39831587
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 11

Author Comment

by:TechMommy
ID: 39831595
Hi Again Jack,

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

Alison
0
 
LVL 11

Author Comment

by:TechMommy
ID: 39831619
FYI, I tried it without the vbNewLine and got the same results.
0
 
LVL 4

Accepted Solution

by:
Jack Leach earned 2000 total points
ID: 39831892
HTML doesn't concern itself with extra whitespace... you can put this all on a single line or you could put each element on it's own line and it would act (more or less) the same.

The markup you posted is correct - it creates a table, albeit one with no styles, so it may not quite resemble a table.

Your HTMLBody property of the mailitem is set to a literal string "HTML Content".  Try changing that to the output of the loop: eg, .HTMLBody = strPatients.

hth
0
 
LVL 11

Author Comment

by:TechMommy
ID: 39834273
That solved it (.HTMLBody=strPatients). Thank you very much for your help. Alison
0
 
LVL 11

Author Closing Comment

by:TechMommy
ID: 39834275
Jack did a great job walking me through possible solutions until I reached the right combination of factors. He had lots of helpful ideas. Thanks!
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39834448
Glad to help.  Good luck with your project.

-jack
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question