Query to Initial Email?

[Liam.M]

hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I have set up a Query to show only records that meet a
certain criteria...therefore excluding all of the records that do not
meet this criteria (just for the record the criteria is any record
within my database that falls within two months of its "Due Date"
field). I need to find a way to cycle through all of these records
shown in the query and then automatically send an email to myself
notifying me that this particular record has almost expired...reached
its "Due Date".

Now I have already written the code to launch the "email" aspect of the
command...and this is working fine....I do, however, require some help
in automating the process....and was wondering if anyone could provide
me with the code to use, for example, within the form(s) "On Load"
event, that would initiate the "email" code I have already written
titled "SendMail"????? So essentially, if anyone could please spare the
time, would like to obtain some code that loops through all of the
records that query discovers to automate my "SendMail" command....it
would be very much appreciated!

Also does anyone know how to pass fields from a record into the body of
the email...i.e Outlook?
Kind Regards,

Liam

[pietlinden]

Li****@awamarine.com.au wrote:

hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I have set up a Query to show only records that meet a
certain criteria...therefore excluding all of the records that do not
meet this criteria (just for the record the criteria is any record
within my database that falls within two months of its "Due Date"
field). I need to find a way to cycle through all of these records
shown in the query and then automatically send an email to myself
notifying me that this particular record has almost expired...reached
its "Due Date".

Now I have already written the code to launch the "email" aspect of the
command...and this is working fine....I do, however, require some help
in automating the process....and was wondering if anyone could provide
me with the code to use, for example, within the form(s) "On Load"
event, that would initiate the "email" code I have already written
titled "SendMail"????? So essentially, if anyone could please spare the
time, would like to obtain some code that loops through all of the
records that query discovers to automate my "SendMail" command....it
would be very much appreciated!

Also does anyone know how to pass fields from a record into the body of
the email...i.e Outlook?
Kind Regards,

Liam

Liam,

OK, send a _single_ e-mail with the list of values from the query in
the body of the message....

you could create a function to create the message body (I'll call the
function fMsgBody), and then just change the message body assignment.

first, the fMsgBody function.

Function fMsgBody() As String
dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query

set rsDue=DBEngine(0)(0).OpenRecordset("qryDueMails")
do until rsDue.EOF
strList=rsDue.Fields("FirstName") & " " &
rsDue.Fields("LastName") & vbCrLf
rsDue.MoveNext
loop

fMsgBody="The following accounts are due:" & vbcrlf & strList

rsDue.Close
set rsDue=Nothing

End Function

then you'd just include this in your e-mail code.

Instead of the line that says
strMsg = Nz(Me!txtBody,"")

you could use:
strMsg = fMsgBody

(the function that builds the message body for you).

Then the rest of the code would be the same.

Clear as mud, right?

[Liam.M]

Thankyou so much for your reply....they are always extremely helpful!
Clear as mud...to me correct...hahaha...apologies BUT iam relatively
new too all of this...and unfortunately do lack the neccessary
experience...the informaiton you have just provided me makes sense
however implementing this into my project is another story....
The code:

Function fMsgBody() As String
dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query

set rsDue=DBEngine(0)(0).OpenRecordset("qryEmail")
do until rsDue.EOF
strList=rsDue.Fields("IMO Number") & " " & rsDue.Fields("Vessel Name") & " " & ("Date of Issue") & " " & ("Due Date") & vbCrLf
rsDue.MoveNext
loop

fMsgBody="The following accounts are due:" & vbcrlf & strList

rsDue.Close
set rsDue=Nothing

End Function

Where about am I surposed to place this code...and how does this
initiate my "SendMail" function? Does this just pass the variables to
the "SendMail" function I have....or is it meant to be implemented with
other code to loop through these records...in other words am I meant to
use other code to loop through the records and this code just being the
latter half of it to pass the variables (fields) to the "SendMail"
function I have?

Kind Regards,

Liam

[pietlinden]

Li****@awamarine.com.au wrote:

Thankyou so much for your reply....they are always extremely helpful!

Liam,

you might want to check out this link
http://groups.google.com/group/comp....dbc3a94dadba80

Lyle gives a good simple example of using CDOSys to send e-mails.

You could just set the .Body property of the message to the result of
the function, like this

Public Sub VerySimpleSendMailWithCDOSample()
' requires reference to cdosys.dll
' (Microsoft CDO for Windows 2000 library)

Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configuration
Set iMsg = New CDO.Message

With iCfg.Fields
..Item(cdoSendUsingMethod) = cdoSendUsingPort
..Item(cdoSMTPServerPort) = 25
..Item(cdoSMTPServer) = "smtp.mail.yahoo.ca"
..Item(cdoSMTPAuthenticate) = cdoBasic
..Item(cdoSendUserName) = "lylefair"
..Item(cdoSendPassword) = "Password"
..Item(cdoSendEmailAddress) = "Lyle Fairfield <[e-mail]>"
..Update
End With

With iMsg
..Configuration = iCfg
..Subject = "Test"
..To = "[e-mail]"

'---.TextBody = "Test"
..TextBody = fMsgBody() '<=== stuff the result of fMsgBody into the
..Textbody property of the email.
..Send
End With
End Sub

[Liam.M]

thankyou for the reply.....this is very interesting, and I appreciate
your time!
Could you please explain how I am meant to pass "fMsgBody()" code you
wrote for me in previous post to my Command "SendMail" or the
CDOSys....from my Query Email...and to all the records within it?
pi********@hotmail.com wrote:

Li****@awamarine.com.au wrote:

Thankyou so much for your reply....they are always extremely helpful!

Liam,

you might want to check out this link
http://groups.google.com/group/comp....dbc3a94dadba80

Lyle gives a good simple example of using CDOSys to send e-mails.

You could just set the .Body property of the message to the result of
the function, like this

Public Sub VerySimpleSendMailWithCDOSample()
' requires reference to cdosys.dll
' (Microsoft CDO for Windows 2000 library)

Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configuration
Set iMsg = New CDO.Message

With iCfg.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "smtp.mail.yahoo.ca"
.Item(cdoSMTPAuthenticate) = cdoBasic
.Item(cdoSendUserName) = "lylefair"
.Item(cdoSendPassword) = "Password"
.Item(cdoSendEmailAddress) = "Lyle Fairfield <[e-mail]>"
.Update
End With

With iMsg
.Configuration = iCfg
.Subject = "Test"
.To = "[e-mail]"

'---.TextBody = "Test"
.TextBody = fMsgBody() '<=== stuff the result of fMsgBody into the
.Textbody property of the email.
.Send
End With
End Sub

[Liam.M]

okay....I have created this so far:

Dim rst As Object
Set rst = Me.Recordset.Clone
With rst
.MoveFirst
Do While Not .EOF
SendMail ("ga************@hotmail.com")
rst.MoveNext
Loop

End With
End Sub

This Initiates my "SendMail" email code.....can you please help me add
the fields "IMO Number", "SBMA Number", "Date of Issue", "Due Date",
and "Vessel Name", from the record to pass into the "SendMail" email
code....and also the code for "SendMail", as the one I am currently
using is obviously not appropriate for my cause??????

Kind Regards

Liam
Li****@awamarine.com.au wrote:

thankyou for the reply.....this is very interesting, and I appreciate
your time!
Could you please explain how I am meant to pass "fMsgBody()" code you
wrote for me in previous post to my Command "SendMail" or the
CDOSys....from my Query Email...and to all the records within it?
pi********@hotmail.com wrote:

Li****@awamarine.com.au wrote:

Thankyou so much for your reply....they are always extremely helpful!

Liam,

you might want to check out this link
http://groups.google.com/group/comp....dbc3a94dadba80

Lyle gives a good simple example of using CDOSys to send e-mails.

You could just set the .Body property of the message to the result of
the function, like this

Public Sub VerySimpleSendMailWithCDOSample()
' requires reference to cdosys.dll
' (Microsoft CDO for Windows 2000 library)

Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configuration
Set iMsg = New CDO.Message

With iCfg.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "smtp.mail.yahoo.ca"
.Item(cdoSMTPAuthenticate) = cdoBasic
.Item(cdoSendUserName) = "lylefair"
.Item(cdoSendPassword) = "Password"
.Item(cdoSendEmailAddress) = "Lyle Fairfield <[e-mail]>"
.Update
End With

With iMsg
.Configuration = iCfg
.Subject = "Test"
.To = "[e-mail]"

'---.TextBody = "Test"
.TextBody = fMsgBody() '<=== stuff the result of fMsgBody into the
.Textbody property of the email.
.Send
End With
End Sub

[pietlinden]

Okay, how about the SQL for the query? Is this where the four fields
are coming from?

If so, your message body function is pretty simple... once you get this
working, you could make fMsgBody churn out a formatted table... but
that's later.

Function fMsgBody() As String
'"IMO Number", "SBMA Number", "Date of Issue", "Due Date",and "Vessel
Name"

dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query

set rsDue=DBEngine(0)(0).OpenRecordset("qryDueMails")
do until rsDue.EOF
strList=rsDue.Fields("IMO Number") & vbtab & _
rsDue.Fields("SBMA Number") & vbtab & _
rsDue.Fields("Date of Issue") & vbtab & _
rsDue.Fields("Due Date") & vbtab & _
rsDue.Fields("Vessel Name") & vbcrlf
rsDue.MoveNext
loop

fMsgBody="The following accounts are due:" & vbcrlf & strList

rsDue.Close
set rsDue=Nothing

End Function

Then just use fMsgBody instead of your normal message text. It'll
build the message for you. Then you just assign the result of this
function to the .TEXT or .BODY property of the Message (in Lyle's
code).

Of course, if you don't understand some VB, this is very likely going
to blow right over your head.

I hope this helps

Pieter

[ThompsonJessical]

I am doing something very similar in an access database and thought
this post was very helpful, but I am having some trouble.

I created the Function as a public function and then tried to call the
fMsgBody as the text part of my email and I recieved a compile error:
Expected array message. I can get the email portion to work if I don't
call the fMsgBody() function... so obviously my error is in the
fMsgBody portion.

I have :
Public Function fMsgBody() As String
Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query

Set rsDue = DBEngine(0)(0).OpenRecordset("[Query]![Mail IACUC
Registrations]")
Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop

fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList

rsDue.Close
Set rsDue = Nothing

End Function

'and the mail portion

Private Sub IACUC_CHANGE_Click()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim fMsgBody As String
Dim strList As String

Set fso = New FileSystemObject

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections
Set db = CurrentDb()
' This creates the e-mail

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = [Forms]![Personnel Form2]![Department Contact
Email]

'This gives it a subject
MyMail.Subject = "Current IACUC Approval of Change Form"

'This gives it the body
MyMail.Body = fMsgBody()
'MyMail.Body = "Dear Dr. " & [Forms]![Personnel
Form2]![Department Name] & vbCrLf & vbCrLf & "Attached is the Request
for IACUC Approval of Change to an Animal Care and Use Protocol
(amendment) form. Please fill one out for each of the currently
approved IACUC protocol(s) on which you have indicated you wish to add
" & [Forms]![Personnel Form2]![First Name] & [Forms]![Personnel
Form2]![Last Name] & ". Please edit the amendment form(s), complete
Section A and be sure to list relevant training and experience
information. Print a hard copy and return the original signed and
dated form(s) (including training and experience information) to me via
interoffice mail or in person. Please note: new personnel may NOT
participate on any IACUC protocol until approval from the IACUC is
received." & vbCrLf & vbCrLf & "Thank you for your attention to this
regulatory matter." & vbCrLf & vbCrLf & "Marsha Arnall" & vbCrLf &
"Research Regulations Specialist" & fMsgBody

'send an attachment

MyMail.Attachments.Add "X:\IACUC Administration\IACUC Forms
for Researchers\0904 Updated Amendment - Request for Change to Protocol
form.doc", olByValue, 1, "My Displayname"

So... can I use the acess query in the openrecordset? Or do I need to
write the query in vba (it is a pretty complicate sql statement) How
do I incorporate the sql into the vba?

Thanks,

Jessica

pi********@hotmail.com wrote:

Okay, how about the SQL for the query? Is this where the four fields
are coming from?

If so, your message body function is pretty simple... once you get this
working, you could make fMsgBody churn out a formatted table... but
that's later.

Function fMsgBody() As String
'"IMO Number", "SBMA Number", "Date of Issue", "Due Date",and "Vessel
Name"

dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query

set rsDue=DBEngine(0)(0).OpenRecordset("qryDueMails")
do until rsDue.EOF
strList=rsDue.Fields("IMO Number") & vbtab & _
rsDue.Fields("SBMA Number") & vbtab & _
rsDue.Fields("Date of Issue") & vbtab & _
rsDue.Fields("Due Date") & vbtab & _
rsDue.Fields("Vessel Name") & vbcrlf
rsDue.MoveNext
loop

fMsgBody="The following accounts are due:" & vbcrlf & strList

rsDue.Close
set rsDue=Nothing

End Function

Then just use fMsgBody instead of your normal message text. It'll
build the message for you. Then you just assign the result of this
function to the .TEXT or .BODY property of the Message (in Lyle's
code).

Of course, if you don't understand some VB, this is very likely going
to blow right over your head.

I hope this helps

Pieter

[pietlinden]

So what *exactly* are you trying to do? Never mind what Liam wrote.
Because the butchered code doesn't mean a whole lot. I know what the
original code did, but what are you trying to do? Just send a unique
e-mail to a list of addresses in a query?

Then you do something like

do until rsRecipients.EOF
with olkMsg
.Body="Hello " & rsRecipients.Fields("FirstName")...
.Recipient = rsRecipients.Fields("EMailAddress")
.Subject = "Some Subject"
.Send
end with
rsRecipients.MoveNext
loop

[ThompsonJessical]

No, I am sending one email to a specific person listed on the open form
that includes a list of items relating to them.

I am trying to create a button that generates an email telling the
person to update forms for a list of protocols. It then attaches the
protocol amendment form. The email button works great... I can attach
the correct file, address it to the person listed on the form, add a
message. The only part I am having trouble with is incorporating the
list of protocols that need to be updated. I want to add this part
into the body of the message. (It is similar to the previous persons
example of sending a message about updating the following accounts)

Public Function fMsgBody() As String

Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query

Set rsDue = DBEngine(0)(0).OpenRecordset("SELECT [Current IACUC
Protocols].[Protocol Number], [Current IACUC Protocols].[Protocol
Name], [New Personnel].[Member ID]" & _
"FROM ([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON [registrations list].[RegistrationForm ID]
= [Current IACUC Protocols].[Protocol Number]" & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID]))
OR ((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID])) OR ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID]))")

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function


pi********@hotmail.com wrote:

So what *exactly* are you trying to do? Never mind what Liam wrote.
Because the butchered code doesn't mean a whole lot. I know what the
original code did, but what are you trying to do? Just send a unique
e-mail to a list of addresses in a query?

Then you do something like

do until rsRecipients.EOF
with olkMsg
.Body="Hello " & rsRecipients.Fields("FirstName")...
.Recipient = rsRecipients.Fields("EMailAddress")
.Subject = "Some Subject"
.Send
end with
rsRecipients.MoveNext
loop

[pietlinden]

Th**************@yahoo.com wrote:

No, I am sending one email to a specific person listed on the open form
that includes a list of items relating to them.

I am trying to create a button that generates an email telling the
person to update forms for a list of protocols. It then attaches the
protocol amendment form. The email button works great... I can attach
the correct file, address it to the person listed on the form, add a
message. The only part I am having trouble with is incorporating the
list of protocols that need to be updated. I want to add this part
into the body of the message. (It is similar to the previous persons
example of sending a message about updating the following accounts)

Public Function fMsgBody() As String

Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query

Set rsDue = DBEngine(0)(0).OpenRecordset("SomeQuery")

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function

Okay, so what doesn't work? Does the recordset come up empty? Did you
try just creating the query in the querybuilder that returns the
records that you want and then just open the recordset off the query?
Try answering this question, maybe it'll help:

When I try to do [fill in the blank], instead of [what I expect], I get
[what I don't expect]. These are the steps I took to get myself here:[list].

My bet is that your query doesn't work the way you expect it to. If
you want to make sure it's working, do something like

dim rsDue As Recordset
set rsDue =
DBEngine(0)(0).OpenQuerydef("MyCannedQuery"),dbOpe nSnapshot)

if rsDue.Recordcount=0 then
msgbox "No records returned"
'--something's not right... exit and fix problem.
else
'process records
end if

You might want to look up OpenQueryDef in the help, but the way Access
help is going these days, pretty soon you'd be better off just looking
up a keyword in the NG. (I'm surprised that MS help doesn't just
search the NGs anyway!)

[ThompsonJessical]

First...

When I try to do execute the code to that would insert a list of the
query results into the body of an mail (the protocols associated with
the individual I am emailing) instead of seeing the list of protocols
in the email, I get a compile error: Expected Array and it highlights
the portion of the code that says MyMail.Body = fMsgBody() If I
instead fill in the MyMail.Body with text... it works just fine, so I
know the error is in the Function fMsgBody().

I have created a public function fMsgBody() to list all protocols
associated with an individual with the following code:

Public Function fMsgBody() As String

Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query
Set rsDue = DBEngine(0)(0).OpenRecordset("SELECT [Current IACUC
Protocols].[Protocol Number], [Current IACUC Protocols].[Protocol
Name], [New Personnel].[Member ID]" & _
"FROM ([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON [registrations list].[RegistrationForm ID]
= [Current IACUC Protocols].[Protocol Number]" & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID]))
OR ((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID])) OR ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID]))")

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function

I don't find OpenQueryDef in the access help file... and when I type
the code and put the period, it does not show up in the list of
available options. I have Querydefs or OpenRecordset... but no
OpenQueryDef.

The SQL for the query came from a query that I had previously written
in the Database window. It does work as a query in the database.

I appreciate your help.

Thanks,

Jessica Thompson

pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

No, I am sending one email to a specific person listed on the open form
that includes a list of items relating to them.

I am trying to create a button that generates an email telling the
person to update forms for a list of protocols. It then attaches the
protocol amendment form. The email button works great... I can attach
the correct file, address it to the person listed on the form, add a
message. The only part I am having trouble with is incorporating the
list of protocols that need to be updated. I want to add this part
into the body of the message. (It is similar to the previous persons
example of sending a message about updating the following accounts)

Public Function fMsgBody() As String

Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query

Set rsDue = DBEngine(0)(0).OpenRecordset("SomeQuery")

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function

Okay, so what doesn't work? Does the recordset come up empty? Did you
try just creating the query in the querybuilder that returns the
records that you want and then just open the recordset off the query?
Try answering this question, maybe it'll help:

When I try to do [fill in the blank], instead of [what I expect], I get
[what I don't expect]. These are the steps I took to get myself here:[list].

My bet is that your query doesn't work the way you expect it to. If
you want to make sure it's working, do something like

dim rsDue As Recordset
set rsDue =
DBEngine(0)(0).OpenQuerydef("MyCannedQuery"),dbOpe nSnapshot)

if rsDue.Recordcount=0 then
msgbox "No records returned"
'--something's not right... exit and fix problem.
else
'process records
end if

You might want to look up OpenQueryDef in the help, but the way Access
help is going these days, pretty soon you'd be better off just looking
up a keyword in the NG. (I'm surprised that MS help doesn't just
search the NGs anyway!)

[pietlinden]

if it doesn't compile, do you have the DAO library loaded?

[ThompsonJessical]

Yes. Microsof DAO 3.6 Object Library checked in the References.
pi********@hotmail.com wrote:

if it doesn't compile, do you have the DAO library loaded?

[pietlinden]

What happens if you create a query and try to open it given the same
criteria? If that doesn't compile, there's your problem.

If that works, you could just use a querydef instead of building this
huge query in code.

I guess my advice here is to simplify or isolate your problems. then
you know where the problem is coming from. What happens if you create
a simpler query?

[ThompsonJessical]

I created a simpler query and it works if I run it in the database
window (not sure how to make it run in the vba window by itself.... I
have MySQL query browser, but I am waiting for IT to give me the
apprpriate permissions to use it on the server where the DB is located,
so I can't run it in there now)

I am still getting the expected array error. I have defined fMsgBody
as a string, so why would it be expecting an array?
pi********@hotmail.com wrote:

What happens if you create a query and try to open it given the same
criteria? If that doesn't compile, there's your problem.

If that works, you could just use a querydef instead of building this
huge query in code.

I guess my advice here is to simplify or isolate your problems. then
you know where the problem is coming from. What happens if you create
a simpler query?

[pietlinden]

remove the parentheses from fMsgBody.

Either that, or it's being interpreted as an array somehow. Add an
Option Explicit statement to the top of your code and recompile. That
might help find the problem.

[ThompsonJessical]

Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
pi********@hotmail.com wrote:

remove the parentheses from fMsgBody.

Either that, or it's being interpreted as an array somehow. Add an
Option Explicit statement to the top of your code and recompile. That
might help find the problem.

[Lyle Fairfield]

Lauren Quantrell wrote:

I use the code below to generate a text file containing every stored
procedure and view in my SQL Server backend database, but I can't
figure out how to generate text of the User Defined Functions.

I see I can reference: SQLDMO.UserDefinedFunction
But can't find any way to reference dbs.<user defined functions>

Any help is greatly appreciated.
lq
Sub OutputDBQueries(myPath as String)

' In VB 6. Set a reference to Microsoft SQLDMO Object Library

Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim v As SQLDMO.View
Dim sptext As String

objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<Databasename>)

Open myPath For Output As #1

'>go through all stored procedures:
For Each sp In dbs.StoredProcedures

sptext = sp.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

'>go through all views:
For Each v In dbs.Views

sptext = v.Text

Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _

"************************************************* *****************************"
& _
vbCrLf & vbCrLf & vbCrLf
Next

MsgBox "The export is completed." & vbCrLf & vbCrLf & _
"Your export file has been created in: " & vbCrLf & _
myPath, vbInformation, "Export Completed"

End Sub

Air Code:

Dim c As ADODB.Connection
Dim s As String
Set c = New ADODB.Connection
c.Open "PROVIDER=SQLOLEDB.1;" _
& "INITIAL CATALOG=DB_51315;" _
& "DATA SOURCE=mssql04.discountasp.net;" _
& "USER ID=Whatever;" _
& "PASSWORD=YYSW"
s = "SELECT so.name, sc.text"
s = s & vbNewLine & "FROM SysComments sc"
s = s & vbNewLine & "JOIN SysObjects so"
s = s & vbNewLine & "ON sc.ID = so.ID"
s = s & vbNewLine & "WHERE so.xtype='FN'"
s = s & vbNewLine & "ORDER BY so.Name"
Debug.Print c.Execute(s).GetString(adClipString, , , vbNewLine)

[hbinc]

Hi Jessica,

Look at the code to fill strList:

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop

In fact, it only takes the value of the last element of rsDue.

Better try:
strList = strList & rsDue.Fields("Protocol Number") & vbCrLf
HBInc.

Th**************@yahoo.com wrote:

First...

When I try to do execute the code to that would insert a list of the
query results into the body of an mail (the protocols associated with
the individual I am emailing) instead of seeing the list of protocols
in the email, I get a compile error: Expected Array and it highlights
the portion of the code that says MyMail.Body = fMsgBody() If I
instead fill in the MyMail.Body with text... it works just fine, so I
know the error is in the Function fMsgBody().

I have created a public function fMsgBody() to list all protocols
associated with an individual with the following code:

Public Function fMsgBody() As String

Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query
Set rsDue = DBEngine(0)(0).OpenRecordset("SELECT [Current IACUC
Protocols].[Protocol Number], [Current IACUC Protocols].[Protocol
Name], [New Personnel].[Member ID]" & _
"FROM ([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON [registrations list].[RegistrationForm ID]
= [Current IACUC Protocols].[Protocol Number]" & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID]))
OR ((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID])) OR ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID])) OR ((([New Personnel].[Member
ID])=[Forms]![Personnel Form2]![Member ID])) OR ((([New
Personnel].[Member ID])=[Forms]![Personnel Form2]![Member ID])) OR
((([New Personnel].[Member ID])=[Forms]![Personnel Form2]![Member
ID]))")

Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing
End Function

I don't find OpenQueryDef in the access help file... and when I type
the code and put the period, it does not show up in the list of
available options. I have Querydefs or OpenRecordset... but no
OpenQueryDef.

The SQL for the query came from a query that I had previously written
in the Database window. It does work as a query in the database.

I appreciate your help.

Thanks,

Jessica Thompson

pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

No, I am sending one email to a specific person listed on the open form
that includes a list of items relating to them.
>
I am trying to create a button that generates an email telling the
person to update forms for a list of protocols. It then attaches the
protocol amendment form. The email button works great... I can attach
the correct file, address it to the person listed on the form, add a
message. The only part I am having trouble with is incorporating the
list of protocols that need to be updated. I want to add this part
into the body of the message. (It is similar to the previous persons
example of sending a message about updating the following accounts)
>
Public Function fMsgBody() As String
>
Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the
Query
>
Set rsDue = DBEngine(0)(0).OpenRecordset("SomeQuery")
>
Do Until rsDue.EOF
strList = rsDue.Fields("Protocol Number") & vbCrLf
rsDue.MoveNext
Loop
>
>
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
>
>
rsDue.Close
Set rsDue = Nothing
>
>
End Function

Okay, so what doesn't work? Does the recordset come up empty? Did you
try just creating the query in the querybuilder that returns the
records that you want and then just open the recordset off the query?
Try answering this question, maybe it'll help:

When I try to do [fill in the blank], instead of [what I expect], I get
[what I don't expect]. These are the steps I took to get myself here:[list].

My bet is that your query doesn't work the way you expect it to. If
you want to make sure it's working, do something like

dim rsDue As Recordset
set rsDue =
DBEngine(0)(0).OpenQuerydef("MyCannedQuery"),dbOpe nSnapshot)

if rsDue.Recordcount=0 then
msgbox "No records returned"
'--something's not right... exit and fix problem.
else
'process records
end if

You might want to look up OpenQueryDef in the help, but the way Access
help is going these days, pretty soon you'd be better off just looking
up a keyword in the NG. (I'm surprised that MS help doesn't just
search the NGs anyway!)

[pietlinden]

Th**************@yahoo.com wrote:

Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?

Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)

The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.

[pietlinden]

Function fMsgBody() As String

dim strSQL as String
dim rsDue as dao.recordset
dim strList as string '---place to dump all the names in the
query

'---build the query... (personally, I'd use a stored query)

strSQL = "SELECT [Current IACUC
Protocols].[Protocol Number], [Current IACUC Protocols].[Protocol
Name], [New Personnel].[Member ID] "

strSQL = strSQL & "FROM ([New Personnel] INNER JOIN [registrations
list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) "

strSQL = strSQL & "INNER JOIN [Current IACUC Protocols] ON
[registrations list].[RegistrationForm ID] = [Current IACUC
Protocols].[Protocol Number] " & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID]))"

'---open the recordset based on the query SQL
set rsDue=DBEngine(0)(0).OpenRecordset(strSQL)

'---concatenate the results into a single string
do until rsDue.EOF

'---MODIFY THIS SECTION TO INCLUDE YOUR FIELDS.
strList=strList & rsDue.Fields("IMO Number") & vbtab & _
rsDue.Fields("SBMA Number") & vbtab & _
rsDue.Fields("Date of Issue") & vbtab & _
rsDue.Fields("Due Date") & vbtab & _
rsDue.Fields("Vessel Name") & vbcrlf
rsDue.MoveNext
loop

fMsgBody="The following accounts are due:" & vbcrlf & strList

rsDue.Close
set rsDue=Nothing

End Function
There. Can't get much easier than that. Does that work?

[ThompsonJessical]

It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.

When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.

Is my loop code correct?

Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop

pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?

Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)

The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.

[hbinc]

Hi Jessica,

See a couple of messages before.
You can better use

strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf

HBInc.

Th**************@yahoo.com wrote:

It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.

When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.

Is my loop code correct?

Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop

pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?

Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)

The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.

[ThompsonJessical]

Sorry... I think we crossed postings for a minute. I re-wrote strSQL
as indicated above and the strList as supplied below. (I have pasted my
code) however, my email still comes up blank when I call fMsgBody in
the body of the email.
NOw, when I try to "test" the sql code in the database window, it does
not work... it prompts me for the parameter values of Protocol Number,
Protocol Name etc with the first strSQL statement. You wanted me to
separate the sql portion into 3 portions right?
Public Function fMsgBody() As String

Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset
Set db = CurrentDb()
Dim rsDue As DAO.Recordset
Dim strList As String '---place to dump all the names in the Query
Dim strSQL As String

strSQL = "SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] "
strSQL = strSQL & "FROM ([New Personnel] INNER JOIN [registrations
list] ON [New Personnel].[Member ID] = [registrations list].[Member
ID]) "
strSQL = strSQL & "INNER JOIN [Current IACUC Protocols] ON
[registrations list].[RegistrationForm ID] = [Current IACUC
Protocols].[Protocol Number] " & _
"WHERE ((([New Personnel].[Member ID])=[Forms]![Personnel
Form2]![Member ID]))"
'---open the recordset based on the query SQL
Set rsDue = DBEngine(0)(0).OpenRecordset(strSQL)
'---concatenate the results into a single string
Do Until rsDue.EOF

strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop
fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList
rsDue.Close
Set rsDue = Nothing

End Function
hbinc wrote:

Hi Jessica,

See a couple of messages before.
You can better use

strList = strList & rsDue.Fields([Protocol Number]) & vbCrLf

HBInc.

Th**************@yahoo.com wrote:

It works when I create a new query in the db window and paste the SQL
code into the sql editor (...ommitting the "" and () at beginning and
end.) I run the query and have 4 records.

When I "test it in vba" by hitting the button that creates the email
that calls the query looped string in the body of the message, it is
blank.

Is my loop code correct?

Do Until rsDue.EOF
strList = rsDue.Fields([Protocol Number]) & vbCrLf
rsDue.MoveNext
Loop

pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:
Okay.. if I take the () away from fMsgBody, I don't get the compile
error and the email is created, but there is no data. Which tells me
that my query is not working. How do I test my query within the VBA in
access window?
>
Why not isolate the query problems? Couple of ways to go about this -
one is to copy the SQL statement that your code builds, and then paste
it into a query (just click the view button on the top left in design
view, and switch to SQL view). Then try running it. The problem with
running the SQL statement in code is that you can't really see what
records it returns. You can get a recordcount, but that's it. (Well,
unless you loop through everything...)
>
The other way is to create a function that creates the SQL statement
for you. You could pass in your criteria or whatever... Doesn't
matter. And then you'd just copy that back into your blank query and
run it. (or change the SQL property of a junk/temp query) and run it.

[pietlinden]

If something isn't working, simplify or go take enough steps backwards
so you're at a spot where it does. Then change ONE thing at a time
from there.

What happens if you just create a normal query from this:

SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] FROM
([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON
[registrations list].[RegistrationForm ID] = [Current IACUC
Protocols].[Protocol Number] WHERE ((([New Personnel].[Member
ID])=[Forms]![Personnel
Form2]![Member ID]))
just make sure Form2 is open, and then copy and paste this into the SQL
view of a query and run it. What happens? If this works, it's how
you're manipulating the functions or whatever. Once you start there,
you know that it's the code causing the problem or it's the SQL.

Say you save the above query as "qryGetProtocols". Building the query
on the fly and opening a canned query (as long as the SQL statements
are the same) is logically equivalent. So break the thing up and sort
of start over with the pieces you know work. Then move forward from
there.
Once that works, you can work on collecting the information into a
single record or whatever. But if you're not moving from
stable/functional state to stable/functional state in your database
design, you won't make much progress.

Just my two cents.

[ThompsonJessical]

I have tested the Select Statements in the SQL query with Form2 open
and they work. I have tested the email without the fMsgBody and it
worked.

I added an if statement to enter the words none if the query returned
no values and the none did not appear in the body message.

To me it seems like I must have an error in the manner of calling the
function, I am calling the fuction as follows:
MyMail.Body = fMsgBody
I have written fMsgBody function as follows:

fMsgBody = "You have indicated this person will work on the following
protocols:" & vbCrLf & strList

I don't understand why nothing is showing up in the body of the email
with that code.

pi********@hotmail.com wrote:

If something isn't working, simplify or go take enough steps backwards
so you're at a spot where it does. Then change ONE thing at a time
from there.

What happens if you just create a normal query from this:

SELECT [Current IACUC Protocols].[Protocol Number], [Current
IACUC Protocols].[Protocol Name], [New Personnel].[Member ID] FROM
([New Personnel] INNER JOIN [registrations list] ON [New
Personnel].[Member ID] = [registrations list].[Member ID]) INNER JOIN
[Current IACUC Protocols] ON
[registrations list].[RegistrationForm ID] = [Current IACUC
Protocols].[Protocol Number] WHERE ((([New Personnel].[Member
ID])=[Forms]![Personnel
Form2]![Member ID]))
just make sure Form2 is open, and then copy and paste this into the SQL
view of a query and run it. What happens? If this works, it's how
you're manipulating the functions or whatever. Once you start there,
you know that it's the code causing the problem or it's the SQL.

Say you save the above query as "qryGetProtocols". Building the query
on the fly and opening a canned query (as long as the SQL statements
are the same) is logically equivalent. So break the thing up and sort
of start over with the pieces you know work. Then move forward from
there.
Once that works, you can work on collecting the information into a
single record or whatever. But if you're not moving from
stable/functional state to stable/functional state in your database
design, you won't make much progress.

Just my two cents.

[pietlinden]

Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?

[ThompsonJessical]

How exactly do you do that? I don't think I am doing it correctly
pi********@hotmail.com wrote:

Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?

[pietlinden]

Th**************@yahoo.com wrote:

How exactly do you do that? I don't think I am doing it correctly
pi********@hotmail.com wrote:

Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?

open a code window.
show the immediate window

type in
?fMsgBody

If it's working, the result should print below that.

[ThompsonJessical]

Okay.. then it must not be working. I typed it in and hit enter and
didn't get anything
pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

How exactly do you do that? I don't think I am doing it correctly
pi********@hotmail.com wrote:

Okay, now we know the problem is in fMsgBody. What happens when you
call fMsgBody in the Immediate window?

open a code window.
show the immediate window

type in
?fMsgBody

If it's working, the result should print below that.

[pietlinden]

Jessica,

this works in NorthWind...

Public Function fMsgBody() As String
Dim rst As Recordset
Dim rs As DAO.Recordset
Dim strList As String '---place to dump all the names in the Query
Dim strSQL As String

strSQL = "SELECT CategoryName FROM Categories;"
'---open the recordset based on the query SQL
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

'---concatenate the results into a single string
Do Until rs.EOF
strList = strList & vbCrLf & rs.Fields("CategoryName")
rs.MoveNext

Loop

rs.Close
Set rs = Nothing

fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList

End Function
------

This is a sample result...
?fmsgbody
You have indicated this person will work on the following protocols:

Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood
If you wanted to concatenate various fields in the result you could
loop through the recordset and do something like;

strLine = rs.Fields("FirstName") & " " & rs.Fields("LastName") & "
lives in " & rs.Fields("City")

test out the function asis and see if that works...

Hope that helps.
Pieter

[ThompsonJessical]

Thanks for sticking with me on this...

So... I tried it in my database (with a the appropriate sql statement)
and it didnt' work.

So then, I opened the Northwind database and copied your code into a
the general part of the Northwind- Form_Categories page. I viewed the
immediate window and typed ?fMsgBody and hit enter and didn't get
anything.

This is the very last thing to add to the database... so its just sooo
close.

Thanks,

Jessica Thompson
pi********@hotmail.com wrote:

Jessica,

this works in NorthWind...

Public Function fMsgBody() As String
Dim rst As Recordset
Dim rs As DAO.Recordset
Dim strList As String '---place to dump all the names in the Query
Dim strSQL As String

strSQL = "SELECT CategoryName FROM Categories;"
'---open the recordset based on the query SQL
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

'---concatenate the results into a single string
Do Until rs.EOF
strList = strList & vbCrLf & rs.Fields("CategoryName")
rs.MoveNext

Loop

rs.Close
Set rs = Nothing

fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList

End Function
------

This is a sample result...
?fmsgbody
You have indicated this person will work on the following protocols:

Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood
If you wanted to concatenate various fields in the result you could
loop through the recordset and do something like;

strLine = rs.Fields("FirstName") & " " & rs.Fields("LastName") & "
lives in " & rs.Fields("City")

test out the function asis and see if that works...

Hope that helps.
Pieter

[ThompsonJessical]

Okay..

So I wrote a button that on click has MsgBox (fMsgBody) and it worked.
(both in northwind and in my own database.

So what does that mean?

Th**************@yahoo.com wrote:

Thanks for sticking with me on this...

So... I tried it in my database (with a the appropriate sql statement)
and it didnt' work.

So then, I opened the Northwind database and copied your code into a
the general part of the Northwind- Form_Categories page. I viewed the
immediate window and typed ?fMsgBody and hit enter and didn't get
anything.

This is the very last thing to add to the database... so its just sooo
close.

Thanks,

Jessica Thompson
pi********@hotmail.com wrote:

Jessica,

this works in NorthWind...

Public Function fMsgBody() As String
Dim rst As Recordset
Dim rs As DAO.Recordset
Dim strList As String '---place to dump all the names in the Query
Dim strSQL As String

strSQL = "SELECT CategoryName FROM Categories;"
'---open the recordset based on the query SQL
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

'---concatenate the results into a single string
Do Until rs.EOF
strList = strList & vbCrLf & rs.Fields("CategoryName")
rs.MoveNext

Loop

rs.Close
Set rs = Nothing

fMsgBody = "You have indicated this person will work on the
following protocols:" & vbCrLf & strList

End Function
------

This is a sample result...
?fmsgbody
You have indicated this person will work on the following protocols:

Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood
If you wanted to concatenate various fields in the result you could
loop through the recordset and do something like;

strLine = rs.Fields("FirstName") & " " & rs.Fields("LastName") & "
lives in " & rs.Fields("City")

test out the function asis and see if that works...

Hope that helps.
Pieter

[pietlinden]

Th**************@yahoo.com wrote:

Okay..

So I wrote a button that on click has MsgBox (fMsgBody) and it worked.
(both in northwind and in my own database.

So what does that mean?

If the message body is being created correctly, then all you need to do
is to insert that part into your e-mail automation code. The code
would be something like (off the top of my head):

with olkMsg
.To="so*****@somewhere.com"
.Subject="Your subject"
.Body=fMsgBody
.Attachments.Add("C:\somefolder\somefile.doc")
.Send
End with

and it should be done.

[ThompsonJessical]

Sorry.. I was out of town for a few days.... back to the grind.

So I created a button with a messagebox and still get the msgbox with
the correct text by

Private Sub Command104_Click()
MsgBox (fMsgBody)
End Sub

But when I call fMsgBody in the email it does not appear. The message
is simply blank with the following code. I don't get it.

Set MyMail = MyOutlook.CreateItem(olMailItem)
With MyMail
.To = [Forms]![Personnel Form2]![Department Contact Email]
.Subject = "Current IACUC Approval of Change Form"
.Body = fMsgBody
.Attachments.Add
"\\biokc03\Dynamic\LAS\IACUC\IACUCForms\Amendm ent Form 0206.doc",
olByValue, 1, "My Displayname"
.Display
End With
pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

Okay..

So I wrote a button that on click has MsgBox (fMsgBody) and it worked.
(both in northwind and in my own database.

So what does that mean?

If the message body is being created correctly, then all you need to do
is to insert that part into your e-mail automation code. The code
would be something like (off the top of my head):

with olkMsg
.To="so*****@somewhere.com"
.Subject="Your subject"
.Body=fMsgBody
.Attachments.Add("C:\somefolder\somefile.doc")
.Send
End with

and it should be done.

[ThompsonJessical]

NEVERMIND I GOT IT!!

See what a little break in Vegas does for the brain cells?

It didn't work because I had defined fMsgBody as text. When I took
that out it worked.
Th**************@yahoo.com wrote:

Sorry.. I was out of town for a few days.... back to the grind.

So I created a button with a messagebox and still get the msgbox with
the correct text by

Private Sub Command104_Click()
MsgBox (fMsgBody)
End Sub

But when I call fMsgBody in the email it does not appear. The message
is simply blank with the following code. I don't get it.

Set MyMail = MyOutlook.CreateItem(olMailItem)
With MyMail
.To = [Forms]![Personnel Form2]![Department Contact Email]
.Subject = "Current IACUC Approval of Change Form"
.Body = fMsgBody
.Attachments.Add
"\\biokc03\Dynamic\LAS\IACUC\IACUCForms\Amendm ent Form 0206.doc",
olByValue, 1, "My Displayname"
.Display
End With
pi********@hotmail.com wrote:

Th**************@yahoo.com wrote:

Okay..
>
So I wrote a button that on click has MsgBox (fMsgBody) and it worked.
(both in northwind and in my own database.
>
So what does that mean?
>

If the message body is being created correctly, then all you need to do
is to insert that part into your e-mail automation code. The code
would be something like (off the top of my head):

with olkMsg
.To="so*****@somewhere.com"
.Subject="Your subject"
.Body=fMsgBody
.Attachments.Add("C:\somefolder\somefile.doc")
.Send
End with

and it should be done.