Populate message area wtih data fron Access database

  • Thread starter Thread starter LenJr
  • Start date Start date
L

LenJr

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!
 
Please show what you have so far and tell us what the problem is.

--
Best regards
Michael Bauer - MVP Outlook

: VBOffice Reporter for Data Analysis & Reporting
: Outlook Categories? Category Manager Is Your Tool
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Thu, 24 Jul 2008 04:37:01 -0700 schrieb LenJr:
 
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!
 
Back
Top