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

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!

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!