Populate Message area with text from access database

I am trying to buid a macro(VBA) that reads an MS Access table(Query) and
pulls in the record set into the message area of an e-mail to be sent. This
is will save the analyst time by building the data part of the message
instead of cutting and pasting the info in from a Table or restult query.
Any help would be greatly appreciated.

Thanks!

Sorry for the lack of specifics...but as you can see I was not sure what I
needed......but I think I am all set. I was having trouble connecting to my
Access 2007 db.
I am new to 2007, have been using 2003. I used this and it seems to work
fine:
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\Weather.accdb;Persist Security Info=False;"
Set oDataBase = New ADODB.Connection
oDataBase.Open strConn

Then I was not sure what route to go when populating the message with data.
I was thinking a form but the Analyst needs to be able to free form their
e-mails so I just wanted to populate the message area. Was not sure on how
to populate the data in a table format so I went with the HTML tags. So I
query the data, create record sets and then read thru...for example:
With rstWOY
.MoveFirst

'Create table headings
txtWOY = "<tr><td>City</td><td>High</td><td>Low</td><td>Low
RH%</td><td>Normal High</td><td>Normal Low</td><td>Description of
Clouds/Significant WX</td></tr>"
' Loop through the Microsoft Access records.
Do While Not .EOF
txtWOY = txtWOY & "<tr><td>" & rstWOY!City & "</td><td>" &
rstWOY!High & "</td><td>" & rstWOY!Low _
& "</td><td>" & rstWOY!LowRH & "</td><td>" &
rstWOY!NormalHigh & "</td><td>" & rstWOY!NormalLow _
& "</td><td>" & rstWOY!DescriptionClouds & "</td></tr>"
.MoveNext
Loop
End With

Then I put the message together:

Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem

Set olApp = Outlook.Application
Set olMsg = olApp.CreateItem(olMailItem)

With olMsg
.To = "email address"
.CC = "e-mail address"
.Subject = "Subject" & Date & "...FINAL"
.HTMLBody = "<h2><u>Heading</u></h2><p>Yesterday's Forecast</p>" _
& "<table border='0' cellspacing='5'><Font color='red'>" _
& txtWFY & "</table></font><br><p>Weather Observed
Yesterday</p>" _
& "<table border='0' cellspacing='5'><Font color='red'>" &
txtWOY _
& "</table></font><br><p>Weather Forecast Today</p>"

.Display

End With

Set olMsg = Nothing
Set olApp = Nothing



If you know of a better way please let me know....but that is the path I
took and it seems to work well.

Thanks!



"Eric Legault [MVP - Outlook]" wrote:

> Given the lack of specifics I can't help you in depth, but this article is a
> good starting point:
>
> Connecting Outlook to Databases:
> http://www.outlookcode.com/article.aspx?ID=25
>
> Once you connect to the data, you can edit the active e-mail by setting a
> MailItem variable to the ActiveInspector.CurrentItem property. Then work
> with the e-mail's properties, like MailItem.Body or MailItem.HTMLBody to
> edit the message contents.
>
> --
> Eric Legault [MVP - Outlook]
> MCDBA, MCTS (Messaging & Collaboration, SharePoint Infrastructure, MOSS 2007
> & WSS 3.0 Application Development)
> President
> Collaborative Innovations
> -> Try Picture Attachments Wizard 2.0 For Microsoft Outlook <-
> -> Take your SharePoint content offline <-
> -> More info: http://www.collaborativeinnovations.ca <-
> Blog: http://blogs.officezealot.com/legault
>
> "LenJr" <LenJr.DeleteThis@discussions.microsoft.com> wrote in message
> news:853E0B9B-F02E-4031-A51D-00B0F17DE3BF@microsoft.com...
> > I am trying to buid a macro(VBA) that reads an MS Access table(Query) and
> > pulls in the record set into the message area of an e-mail to be sent.
> > This
> > is will save the analyst time by building the data part of the message
> > instead of cutting and pasting the info in from a Table or restult query.
> > Any help would be greatly appreciated.
> >
> > Thanks!
>