Populate Message area with text from access database

[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!

 

[LenJr]

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!

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

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!