Query to Initial Email?

[Liam.M@awamarine.com.au]

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...ther efore 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...reach ed
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@hotmail.com]

Re: Query to Initial Email?


Liam.M@awamarin e.com.au wrote:[color=blue]
> 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...ther efore 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...reach ed
> 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[/color]

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).OpenRecor dset("qryDueMai ls")
do until rsDue.EOF
strList=rsDue.F ields("FirstNam e") & " " &
rsDue.Fields("L astName") & 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@awamarine.com.au]

Re: Query to Initial Email?

Thankyou so much for your reply....they are always extremely helpful!
Clear as mud...to me correct...hahah a...apologies BUT iam relatively
new too all of this...and unfortunately do lack the neccessary
experience...th e informaiton you have just provided me makes sense
however implementing this into my project is another story....
The code:
[color=blue]
> 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).OpenRecor dset("qryEmail" )
> do until rsDue.EOF
> strList=rsDue.F ields("IMO Number") & " " & rsDue.Fields("V essel 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[/color]

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@hotmail.com]

Re: Query to Initial Email?


Liam.M@awamarin e.com.au wrote:[color=blue]
> Thankyou so much for your reply....they are always extremely helpful![/color]

Liam,

you might want to check out this link

http://groups.google.com/group/comp.databases.ms-access/msg/01dbc3a94dadba80

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 VerySimpleSendM ailWithCDOSampl e()
' requires reference to cdosys.dll
' (Microsoft CDO for Windows 2000 library)

Dim iCfg As CDO.Configurati on
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configurati on
Set iMsg = New CDO.Message

With iCfg.Fields
..Item(cdoSendU singMethod) = cdoSendUsingPor t
..Item(cdoSMTPS erverPort) = 25
..Item(cdoSMTPS erver) = "smtp.mail.yaho o.ca"
..Item(cdoSMTPA uthenticate) = cdoBasic
..Item(cdoSendU serName) = "lylefair"
..Item(cdoSendP assword) = "Password"
..Item(cdoSendE mailAddress) = "Lyle Fairfield <lylef...@yahoo .ca>"
..Update
End With

With iMsg
..Configuration = iCfg
..Subject = "Test"
..To = "lylefairfi...@ aim.com"

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

[Liam.M@awamarine.com.au]

Re: Query to Initial Email?

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?


pietlinden@hotm ail.com wrote:[color=blue]
> Liam.M@awamarin e.com.au wrote:[color=green]
> > Thankyou so much for your reply....they are always extremely helpful![/color]
>
> 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 VerySimpleSendM ailWithCDOSampl e()
> ' requires reference to cdosys.dll
> ' (Microsoft CDO for Windows 2000 library)
>
> Dim iCfg As CDO.Configurati on
> Dim iMsg As CDO.Message
>
> Set iCfg = New CDO.Configurati on
> Set iMsg = New CDO.Message
>
> With iCfg.Fields
> .Item(cdoSendUs ingMethod) = cdoSendUsingPor t
> .Item(cdoSMTPSe rverPort) = 25
> .Item(cdoSMTPSe rver) = "smtp.mail.yaho o.ca"
> .Item(cdoSMTPAu thenticate) = cdoBasic
> .Item(cdoSendUs erName) = "lylefair"
> .Item(cdoSendPa ssword) = "Password"
> .Item(cdoSendEm ailAddress) = "Lyle Fairfield <lylef...@yahoo .ca>"
> .Update
> End With
>
> With iMsg
> .Configuration = iCfg
> .Subject = "Test"
> .To = "lylefairfi...@ aim.com"
>
> '---.TextBody = "Test"
> .TextBody = fMsgBody() '<=== stuff the result of fMsgBody into the
> .Textbody property of the email.
> .Send
> End With
> End Sub[/color]

[Liam.M@awamarine.com.au]

Re: Query to Initial Email?

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

Dim rst As Object
Set rst = Me.Recordset.Cl one
With rst
.MoveFirst
Do While Not .EOF
SendMail ("gatecrasher_0 5@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
Liam.M@awamarin e.com.au wrote:[color=blue]
> 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?
>
>
> pietlinden@hotm ail.com wrote:[color=green]
> > Liam.M@awamarin e.com.au wrote:[color=darkred]
> > > Thankyou so much for your reply....they are always extremely helpful![/color]
> >
> > 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 VerySimpleSendM ailWithCDOSampl e()
> > ' requires reference to cdosys.dll
> > ' (Microsoft CDO for Windows 2000 library)
> >
> > Dim iCfg As CDO.Configurati on
> > Dim iMsg As CDO.Message
> >
> > Set iCfg = New CDO.Configurati on
> > Set iMsg = New CDO.Message
> >
> > With iCfg.Fields
> > .Item(cdoSendUs ingMethod) = cdoSendUsingPor t
> > .Item(cdoSMTPSe rverPort) = 25
> > .Item(cdoSMTPSe rver) = "smtp.mail.yaho o.ca"
> > .Item(cdoSMTPAu thenticate) = cdoBasic
> > .Item(cdoSendUs erName) = "lylefair"
> > .Item(cdoSendPa ssword) = "Password"
> > .Item(cdoSendEm ailAddress) = "Lyle Fairfield <lylef...@yahoo .ca>"
> > .Update
> > End With
> >
> > With iMsg
> > .Configuration = iCfg
> > .Subject = "Test"
> > .To = "lylefairfi...@ aim.com"
> >
> > '---.TextBody = "Test"
> > .TextBody = fMsgBody() '<=== stuff the result of fMsgBody into the
> > .Textbody property of the email.
> > .Send
> > End With
> > End Sub[/color][/color]

[pietlinden@hotmail.com]

Re: Query to Initial Email?

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).OpenRecor dset("qryDueMai ls")
do until rsDue.EOF
strList=rsDue.F ields("IMO Number") & vbtab & _
rsDue.Fields("S BMA Number") & vbtab & _
rsDue.Fields("D ate of Issue") & vbtab & _
rsDue.Fields("D ue Date") & vbtab & _
rsDue.Fields("V essel 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@yahoo.com]

Re: Query to Initial Email?

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("P rotocol 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_Cl ick()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Applica tion
Dim MyMail As Outlook.MailIte m
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObjec t
Dim MyBody As TextStream
Dim MyBodyText As String
Dim fMsgBody As String
Dim strList As String

Set fso = New FileSystemObjec t

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

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

Set MyMail = MyOutlook.Creat eItem(olMailIte m)

' 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.Attachme nts.Add "X:\IACUC Administration\ IACUC Forms
for Researchers\090 4 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

pietlinden@hotm ail.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).OpenRecor dset("qryDueMai ls")
do until rsDue.EOF
strList=rsDue.F ields("IMO Number") & vbtab & _
rsDue.Fields("S BMA Number") & vbtab & _
rsDue.Fields("D ate of Issue") & vbtab & _
rsDue.Fields("D ue Date") & vbtab & _
rsDue.Fields("V essel 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@hotmail.com]

Re: Query to Initial Email?

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.EO F
with olkMsg
.Body="Hello " & rsRecipients.Fi elds("FirstName ")...
.Recipient = rsRecipients.Fi elds("EMailAddr ess")
.Subject = "Some Subject"
.Send
end with
rsRecipients.Mo veNext
loop

[ThompsonJessical@yahoo.com]

Re: Query to Initial Email?

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].[RegistrationFor m 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("P rotocol 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




pietlinden@hotm ail.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.EO F
with olkMsg
.Body="Hello " & rsRecipients.Fi elds("FirstName ")...
.Recipient = rsRecipients.Fi elds("EMailAddr ess")
.Subject = "Some Subject"
.Send
end with
rsRecipients.Mo veNext
loop

[pietlinden@hotmail.com]

Re: Query to Initial Email?


ThompsonJessica l@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("P rotocol 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("M yCannedQuery"), dbOpenSnapshot)

if rsDue.Recordcou nt=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@yahoo.com]

Re: Query to Initial Email?



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].[RegistrationFor m 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("P rotocol 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



pietlinden@hotm ail.com wrote:

ThompsonJessica l@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("P rotocol 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("M yCannedQuery"), dbOpenSnapshot)
>
if rsDue.Recordcou nt=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@hotmail.com]

Re: Query to Initial Email?

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

[ThompsonJessical@yahoo.com]

Re: Query to Initial Email?

Yes. Microsof DAO 3.6 Object Library checked in the References.


pietlinden@hotm ail.com wrote:

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

[pietlinden@hotmail.com]

Re: Query to Initial Email?

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?