Creating a function to automated this command! (EMAIL)

Hey guys.....
Okay I need to some help with code please....I need to query my
database....look for the "DueDate" Field, if it is within the next two
months....then I need it to Run a Command I have already written which
is as follows :

Private Sub Command38_Click()
Dim strTo As String
Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String


strTo = Me!EmailAddress
strsubject = "subject"
varbody = "Attached please find your confirmation"


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub


How would I go about this?

From: - view profile
Date: Tues, Jun 6 2006 12:13 pm
Email:
Groups: microsoft.public.access.forms
Not yet ratedRating:
show options


Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Sorry guys...let me explain the situation a little-bit better....I also


have a Form (A continous form) that is essentially a Summary List of
all the records in my database (i.e It contains each records name, the
records Issue date, and its DueDate) for ALL the records in the
database....and from this the user can click on which record they want
and it will then open this particular record that exihibits much more
information, naturally! HOWEVER this is getting off the track!


Back on topic....do I need to create on "On Load" Event that will cycle



through all of the "DueDate" fields in this Summary List form...that
will then execute my email command if the "DueDate" falls within the
critera? (I already have the email aspect under control) I just need to



essentially automate the process.


If I do have to create an "On Load" event that will check these records



"DueDate", can I also pass some of the record information to the email
command (the code is posted in my previous question...above) ? if so
how do I do this?


WHat I have come up with so far....in the theory for my "On Load" event



is as follows:


Private Sub Form_Load()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryShipsInformation")


If DateDiff("m", RS(), Date) 2 Then DoCmd.Run Emailer ()


'do something - send an email, flag something...


End If


Obviously doesnt work....just theory at the moment....any
suggestions...much appreciated!

You'll need to loop through the form's underlying recordset and pass the
value of the EmailAddress field into a procedu

Dim rst As Object

Set rst = Me.Recordset.Clone

With rst
.MoveFirst
Do While Not .EOF
If .Fields("DueDate") = VBA.Date And _
.Fields("DueDate") = DateAdd("m", 2, VBA.Date) Then
SendMail .Fields("EmailAddress")
End If
.MoveNext
Loop
End With

The code to create the emails would be wrapped in a procedure, either in the
form's module or in a standard module:

Sub SendMail(strToAs String)

Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olMail As Outlook.MailItem

strsubject = "subject"
varbody = "Attached please find your confirmation"

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody

olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub

I'm not familiar with the Outlook object model, so you might be able to
refine this so it doesn’t open different instances of Outlook each time. I
do this with Word with the following code, but don't know enough about
Outlook to say whether the same can be done:

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

Ken Sheridan
Stafford, England

" wrote:

Hey guys.....
Okay I need to some help with code please....I need to query my
database....look for the "DueDate" Field, if it is within the next two
months....then I need it to Run a Command I have already written which
is as follows :

Private Sub Command38_Click()
Dim strTo As String
Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String


strTo = Me!EmailAddress
strsubject = "subject"
varbody = "Attached please find your confirmation"


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub


How would I go about this?

From: - view profile
Date: Tues, Jun 6 2006 12:13 pm
Email:
Groups: microsoft.public.access.forms
Not yet ratedRating:
show options


Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Sorry guys...let me explain the situation a little-bit better....I also


have a Form (A continous form) that is essentially a Summary List of
all the records in my database (i.e It contains each records name, the
records Issue date, and its DueDate) for ALL the records in the
database....and from this the user can click on which record they want
and it will then open this particular record that exihibits much more
information, naturally! HOWEVER this is getting off the track!


Back on topic....do I need to create on "On Load" Event that will cycle



through all of the "DueDate" fields in this Summary List form...that
will then execute my email command if the "DueDate" falls within the
critera? (I already have the email aspect under control) I just need to



essentially automate the process.


If I do have to create an "On Load" event that will check these records



"DueDate", can I also pass some of the record information to the email
command (the code is posted in my previous question...above) ? if so
how do I do this?


WHat I have come up with so far....in the theory for my "On Load" event



is as follows:


Private Sub Form_Load()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryShipsInformation")


If DateDiff("m", RS(), Date) 2 Then DoCmd.Run Emailer ()


'do something - send an email, flag something...


End If


Obviously doesnt work....just theory at the moment....any
suggestions...much appreciated!

Hey Ken,

Thankyou so much for your reply, this will be extremely valuable for
me, and will hopefully allow me to move one step closer to finishing
this project. I do still have a few questions however, so if you could
spare the time to assistment me, it would be very much appreciated!

Firstly, where abouts would I be placing this code to loop through the
forms underlying recordset? In the forms "On Load" Event?
My only concern, although I havent actually tried to implement this as
yet, is the fact that my main form is like a Summary List of ALL the
records within my database....so this form displays ALL of the
"DueDates" (as well as other summary information from each record) all
of this informaiton is therefore, obviously grabbed from the records
within the database...these fields in this SummaryList have their
control source properties set to the fields within the database...and
are linked on the summarylist via a "Record ID" to their parent record
within the Database! Therefore, does will this looping still work for
my current project?

Thankyou very much for your time, it is greatly appreciated!
The emailing aspect of this project is under control...I just have to
implement your code to cycle through the database and then intiate the
email process based on my selection criteria, "DueDate"!

Another probably really silly questions....but does the calculation
=DateAdd("m",2,VBA.Date) mean that any record "within" 2 months of the
"DueDate" will be selected, or just the literal sense of 2 months,
exactly?

Kind Regards,

Liam

Liam:

For this sort of thing the code to loop through the recordset would usually
be in the Click event procedure of a button on the form. The user can then
generate the emails when required by clicking the button. Putting it in the
form's Load event procedure would run it whenever the form is opened, which
might or might not be what you want. The procedure for generating the
emails can either go in the form's module (Insert|Procedure on the VBA menu
bar when you have the module open at any of the form's event procedures) or
in a standard module, though as you only want to use it with the form the
former is more appropriate.

So long as the form's RecordSource includes the DueDate and the EmailAddress
fields the fact that it contains all the DueDate values doesn't matter as the
code only calls the procedure to generate an email if the DueDate falls on or
after the current date and on or before a date two months from the current
date. The rows outside this date range will be ignored. If the number of
rows in the form's underlying recordset is very large this might not be very
efficient, however, and a better solution would be to put the code in a form
whose RecordSource is a query which returns only the rows within the date
range; you can use a similar expression in the query to restrict it to these
rows by putting the following as the criteria for the DueDate column in query
design view:

= Date() And DueDate = DateAdd("m",2,Date())

Ken Sheridan
Stafford, England


" wrote:

Hey Ken,

Thankyou so much for your reply, this will be extremely valuable for
me, and will hopefully allow me to move one step closer to finishing
this project. I do still have a few questions however, so if you could
spare the time to assistment me, it would be very much appreciated!

Firstly, where abouts would I be placing this code to loop through the
forms underlying recordset? In the forms "On Load" Event?
My only concern, although I havent actually tried to implement this as
yet, is the fact that my main form is like a Summary List of ALL the
records within my database....so this form displays ALL of the
"DueDates" (as well as other summary information from each record) all
of this informaiton is therefore, obviously grabbed from the records
within the database...these fields in this SummaryList have their
control source properties set to the fields within the database...and
are linked on the summarylist via a "Record ID" to their parent record
within the Database! Therefore, does will this looping still work for
my current project?

Thankyou very much for your time, it is greatly appreciated!
The emailing aspect of this project is under control...I just have to
implement your code to cycle through the database and then intiate the
email process based on my selection criteria, "DueDate"!

Another probably really silly questions....but does the calculation
=DateAdd("m",2,VBA.Date) mean that any record "within" 2 months of the
"DueDate" will be selected, or just the literal sense of 2 months,
exactly?

Kind Regards,

Liam

Hey Ken, thankyou for the reply....however, I am having difficulties
with your original code, in which I have placed as a command for a
buttons on click event :

Dim rst As Object
Set rst = Me.Recordset.Clone

With rst
.MoveFirst
Do While Not .EOF
If .Fields("DueDate") = VBA.Date And _
.Fields("DueDate") = DateAdd("m", 2, VBA.Date) Then
SendMail.Fields ("EmailAddress")
End If
.MoveNext
Loop
End With

The "SendMail.Fields" comes up with a Compile Error, expected function
or variable????
Also...there is not a different email address for each record...it is a
reminder to self...therefore there is only a need for one email
address???
Also that data criteria you have recommended.....it should only be for
a date that falls "within" two months of the DueDate, will the
calculation you have provided be accurate or fulfilling this
requirement?

Regards,
Liam

" wrote:

Hey Ken, thankyou for the reply....however, I am having difficulties
with your original code, in which I have placed as a command for a
buttons on click event :

Dim rst As Object
Set rst = Me.Recordset.Clone

With rst
.MoveFirst
Do While Not .EOF
If .Fields("DueDate") = VBA.Date And _
.Fields("DueDate") = DateAdd("m", 2, VBA.Date) Then
SendMail.Fields ("EmailAddress")
End If
.MoveNext
Loop
End With

The "SendMail.Fields" comes up with a Compile Error, expected function
or variable????
Also...there is not a different email address for each record...it is a
reminder to self...therefore there is only a need for one email
address???
Also that data criteria you have recommended.....it should only be for
a date that falls "within" two months of the DueDate, will the
calculation you have provided be accurate or fulfilling this
requirement?

Regards,
Liam

Liam:

Sorry about the blank reply!

If you look at my original code you'll see there is a space after SendMail,
which is missing in yours. This probably explains the error. The reference
to the field is the value passed into the procedure as its argument.
However, as there is just one email address you could just hard code your
address into your code:

SendMail "

or you can look it up from a table in the database where its stored, using
the Dlookup function. Storing values in a table and looking them up is
better than hard coding them as if you change the address you simply edit the
row in the table rather than having to amend the code. Say you have a one
row table MyDetails which includes a column Email you'd look it up like so:

SendMail DLookup("Email", "MyDetails")

As the emails are all to you, though, why not just email yourself a report
based on a query which returns the rows within the date range? You can do
this easily with the SendObject method (see help for details of how to use
the method). Or even just print the report unless there's a specific need to
have it mailed to you. The WHERE clause for the query would be:

WHERE DueDate = DATE() AND DueDate DATEADD("m", 2, DATE())+1

By looking for dates before 1 day after the final date this makes sure any
DueDate values on the final date which include a non-zero time of day are
picked up. In fact it would be prudent to amend the code I sent you to allow
for this possibility too:

If .Fields("DueDate") = VBA.Date And _
.Fields("DueDate") DateAdd("m", 2, VBA.Date)+1 Then

As regards the date range the code will pick out the values of DueDate which
fall within the criteria in your original post: "look for the "DueDate"
Field, if it is within the next two
months", i.e. from the current date when the code executes to 2 months from
that date, so if it were run today you'd get all dates from 8 June to 8
August 2006.

Ken Sheridan
Stafford, England

" wrote:

Hey Ken, thankyou for the reply....however, I am having difficulties
with your original code, in which I have placed as a command for a
buttons on click event :

Dim rst As Object
Set rst = Me.Recordset.Clone

With rst
.MoveFirst
Do While Not .EOF
If .Fields("DueDate") = VBA.Date And _
.Fields("DueDate") = DateAdd("m", 2, VBA.Date) Then
SendMail.Fields ("EmailAddress")
End If
.MoveNext
Loop
End With

The "SendMail.Fields" comes up with a Compile Error, expected function
or variable????
Also...there is not a different email address for each record...it is a
reminder to self...therefore there is only a need for one email
address???
Also that data criteria you have recommended.....it should only be for
a date that falls "within" two months of the DueDate, will the
calculation you have provided be accurate or fulfilling this
requirement?

Regards,
Liam

Hi Ken,



Its Liam Murphy, I am so sorry for the late reply .It has been a long
weekend here in Australia (The Queen's Birthday), so I haven't been
able to try and implement your suggestions until now! Your help and
assistance has been VERY much appreciated..and if you could spare the
time to look over this, it would be absolutely fantastic Ken.thank you
in advance..



I have finally gotten the code that you gave me to work ..I am able to
query/ loop through the database..based on the "Due Date" field..and
then prompt it to email me..this was the code, as I am sure you are
awa



The on click command to query the Records:



Private Sub SBMACheckAndEmail_Click()

Dim rst As Object





Set rst = Me.Recordset.Clone





With rst

.MoveFirst

Do While Not .EOF

If .Fields("Due Date") = VBA.Date And _

.Fields("Due Date") = DateAdd("m", 2, VBA.Date) Then

SendMail ")

End If

.MoveNext

Loop

End With





End Sub



And the email command:



Sub SendMail(strTo)



Dim strsubject As String

Dim varbody As Variant

Dim strattachment1 As String

Dim strattachment2 As String

Dim olApp As Outlook.Application

Dim olNs As Outlook.NameSpace

Dim olMail As Outlook.MailItem





strsubject = "ATTN:Shore-Based Maintainance Agreements"

varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "



Set olApp = CreateObject("Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")

olNs.Logon

Set olMail = olApp.CreateItem(olMailItem)





olMail.To = strTo

olMail.Subject = strsubject

olMail.Body = varbody





olMail.Send





Set olNs = Nothing

Set olMail = Nothing

Set olApp = Nothing





End Sub



Question:



Now that all of this is working.I have two problems.firstly.I would
like to fully automated this process..for example.I would like this
process.of querying the database.to somehow automatically take place on
a weekly basis (once a week), because this Database may potentially not
be opened on a weekly basis, and therefore need to ensure that it is
being check! I understand that you can create an Auto.exe file or
something along those lines.to open the database when the computer
loads..is it possible to have it so it only opens once a week.and also
how can I get it to initiate the "SBMACheckAndEmail_Click" event
procedure?



Also is it possible for the query to pull.if it finds a record that
fits within this Date Range..fields from that record.to be included in
the email?



Regards,





Liam

For example....I only wish to pull two fields from the Record to be
included into the email...those being....."The Vessels Name"...and it's
"IMO Number"....any suggestions..

Regards...

wrote:
Hi Ken,



Its Liam Murphy, I am so sorry for the late reply .It has been a long
weekend here in Australia (The Queen's Birthday), so I haven't been
able to try and implement your suggestions until now! Your help and
assistance has been VERY much appreciated..and if you could spare the
time to look over this, it would be absolutely fantastic Ken.thank you
in advance..



I have finally gotten the code that you gave me to work ..I am able to
query/ loop through the database..based on the "Due Date" field..and
then prompt it to email me..this was the code, as I am sure you are
awa



The on click command to query the Records:



Private Sub SBMACheckAndEmail_Click()

Dim rst As Object





Set rst = Me.Recordset.Clone





With rst

.MoveFirst

Do While Not .EOF

If .Fields("Due Date") = VBA.Date And _

.Fields("Due Date") = DateAdd("m", 2, VBA.Date) Then

SendMail ")

End If

.MoveNext

Loop

End With





End Sub



And the email command:



Sub SendMail(strTo)



Dim strsubject As String

Dim varbody As Variant

Dim strattachment1 As String

Dim strattachment2 As String

Dim olApp As Outlook.Application

Dim olNs As Outlook.NameSpace

Dim olMail As Outlook.MailItem





strsubject = "ATTN:Shore-Based Maintainance Agreements"

varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "



Set olApp = CreateObject("Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")

olNs.Logon

Set olMail = olApp.CreateItem(olMailItem)





olMail.To = strTo

olMail.Subject = strsubject

olMail.Body = varbody





olMail.Send





Set olNs = Nothing

Set olMail = Nothing

Set olApp = Nothing





End Sub



Question:



Now that all of this is working.I have two problems.firstly.I would
like to fully automated this process..for example.I would like this
process.of querying the database.to somehow automatically take place on
a weekly basis (once a week), because this Database may potentially not
be opened on a weekly basis, and therefore need to ensure that it is
being check! I understand that you can create an Auto.exe file or
something along those lines.to open the database when the computer
loads..is it possible to have it so it only opens once a week.and also
how can I get it to initiate the "SBMACheckAndEmail_Click" event
procedure?



Also is it possible for the query to pull.if it finds a record that
fits within this Date Range..fields from that record.to be included in
the email?



Regards,





Liam

Liam:

As regards the weekly automation you can return the week number of any date
by means of the DatePart function. If you store this in the database you can
determine whether the routine has been run in the current week by comparing
the current week number with the stored one. If they differ you run the
routine and update the table to make the stored week number the current one.

Lets say the table is called WeekNumberLog and has a column WeekNumber of
integer number data type. The table only need one row and one column of
course, so start it off by entering 0 in WeekNumber column manually.

You then need to add a function to either the form's module or a standard
module to return True of False and update the table if necessary:

Function WeeklyMailSent() As Boolean

Dim cmd As ADODB.Command
Dim strSQL As String
Dim intWeekNumber As Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' get current week number
intWeekNumber = DatePart("ww", Date)

strSQL = "UPDATE WeekNumberLog " & _
"SET WeekNumber = " & intWeekNumber

' does week number in table differ from current week number
If IsNull(DLookup("WeekNumber", "WeekNumberLog", _
"WeekNumber = " & intWeekNumber)) Then
' set return value of function to False and update table
WeeklyMailSent = False
cmd.CommandText = strSQL
cmd.Execute
Else
WeeklyMailSent = True
End If

End Function

You can then call the function at startup. A simple way to do this is to
put the code in the form's Load event procedure and open the form at startup
either by making it the database's opening form from the Tools|Start Up menu,
or by using an autoexec macro to open it. The amended code would then be:

Dim rst As Object

If Not WeeklyMailSent() Then
Set rst = Me.Recordset.Clone

With rst

' and so on

End With
End If

Alternatively you could do it completely outside the form by creating the
recordset object not as the form's recordset's clone, but on the basis of an
SQL statement to return the relevant rows. Then code would otherwise be the
same. The code would then go in a public procedure in a standard module, and
the Sendmail procedure and WeeklyMailSent function would go in the same
module. All you'd need to do then would be to run the public procedure,
either with an autoexec macro or in the Open event procedure of the
database's opening form, e.g. a switchboard.

As regards including the field's values in the emails you can pass them into
the SendMail procedure by amending its declaration to:

Sub SendMail(strTo As String, strVessel As String, lngIMONumber As Long)

assuming the IMO Number is a number data type, not text.

What you do with the strVessel and IMONumber arguments in the procedure
depends on how you want to incorporate them in the email. You'd call the
procedure like so in place of the present call:

SendMail ", .Fields("VesselName"),
..Fields("IMONumber")

Ken Sheridan
Stafford, England


" wrote:

Hi Ken,



Its Liam Murphy, I am so sorry for the late reply .It has been a long
weekend here in Australia (The Queen's Birthday), so I haven't been
able to try and implement your suggestions until now! Your help and
assistance has been VERY much appreciated..and if you could spare the
time to look over this, it would be absolutely fantastic Ken.thank you
in advance..



I have finally gotten the code that you gave me to work ..I am able to
query/ loop through the database..based on the "Due Date" field..and
then prompt it to email me..this was the code, as I am sure you are
awa



The on click command to query the Records:



Private Sub SBMACheckAndEmail_Click()

Dim rst As Object





Set rst = Me.Recordset.Clone





With rst

.MoveFirst

Do While Not .EOF

If .Fields("Due Date") = VBA.Date And _

.Fields("Due Date") = DateAdd("m", 2, VBA.Date) Then

SendMail ")

End If

.MoveNext

Loop

End With





End Sub



And the email command:



Sub SendMail(strTo)



Dim strsubject As String

Dim varbody As Variant

Dim strattachment1 As String

Dim strattachment2 As String

Dim olApp As Outlook.Application

Dim olNs As Outlook.NameSpace

Dim olMail As Outlook.MailItem





strsubject = "ATTN:Shore-Based Maintainance Agreements"

varbody = "Please check the Database A.S.A.P, as it appears that a
Record is up for renewal within a two-month period "



Set olApp = CreateObject("Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")

olNs.Logon

Set olMail = olApp.CreateItem(olMailItem)





olMail.To = strTo

olMail.Subject = strsubject

olMail.Body = varbody





olMail.Send





Set olNs = Nothing

Set olMail = Nothing

Set olApp = Nothing





End Sub



Question:



Now that all of this is working.I have two problems.firstly.I would
like to fully automated this process..for example.I would like this
process.of querying the database.to somehow automatically take place on
a weekly basis (once a week), because this Database may potentially not
be opened on a weekly basis, and therefore need to ensure that it is
being check! I understand that you can create an Auto.exe file or
something along those lines.to open the database when the computer
loads..is it possible to have it so it only opens once a week.and also
how can I get it to initiate the "SBMACheckAndEmail_Click" event
procedure?



Also is it possible for the query to pull.if it finds a record that
fits within this Date Range..fields from that record.to be included in
the email?



Regards,





Liam


For example....I only wish to pull two fields from the Record to be
included into the email...those being....."The Vessels Name"...and it's
"IMO Number"....any suggestions..

Regards...