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...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]

Li****@awamarin e.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...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

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]

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:

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

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****@awamarin e.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 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]

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********@hotm ail.com wrote:

Li****@awamarin e.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 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

[Liam.M]

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 ("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****@awamarin e.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********@hotm ail.com wrote:

Li****@awamarin e.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 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

[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).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]

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

pi********@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]

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]

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


pi********@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