Populate message area wtih data fron 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!

Populate message area wtih data fron Access database
 

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

Populate message area wtih data fron Access database
 

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 = "trtdCity/tdtdHigh/tdtdLow/tdtdLow
RH%/tdtdNormal High/tdtdNormal Low/tdtdDescription of
Clouds/Significant WX/td/tr"
' Loop through the Microsoft Access records.
Do While Not .EOF
txtWOY = txtWOY & "trtd" & rstWOY!City & "/tdtd" &
rstWOY!High & "/tdtd" & rstWOY!Low _
& "/tdtd" & rstWOY!LowRH & "/tdtd" &
rstWOY!NormalHigh & "/tdtd" & rstWOY!NormalLow _
& "/tdtd" & 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 = "h2uHeading/u/h2pYesterday's Forecast/p" _
& "table border='0' cellspacing='5'Font color='red'" _
& txtWFY & "/table/fontbrpWeather Observed
Yesterday/p" _
& "table border='0' cellspacing='5'Font color='red'" &
txtWOY _
& "/table/fontbrpWeather 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!



"Michael Bauer [MVP - Outlook]" wrote:



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

Populate message area wtih data fron Access database
 

I'd follow the old saying: Never touch a running system.

--
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 Mon, 28 Jul 2008 04:14:00 -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 = "trtdCity/tdtdHigh/tdtdLow/tdtdLow
RH%/tdtdNormal High/tdtdNormal Low/tdtdDescription of
Clouds/Significant WX/td/tr"
' Loop through the Microsoft Access records.
Do While Not .EOF
txtWOY = txtWOY & "trtd" & rstWOY!City & "/tdtd" &
rstWOY!High & "/tdtd" & rstWOY!Low _
& "/tdtd" & rstWOY!LowRH & "/tdtd" &
rstWOY!NormalHigh & "/tdtd" & rstWOY!NormalLow _
& "/tdtd" & 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 = "h2uHeading/u/h2pYesterday's Forecast/p" _
& "table border='0' cellspacing='5'Font color='red'" _
& txtWFY & "/table/fontbrpWeather Observed
Yesterday/p" _
& "table border='0' cellspacing='5'Font color='red'" &
txtWOY _
& "/table/fontbrpWeather 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!



"Michael Bauer [MVP - Outlook]" wrote:



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