Creating a function to automated this command! (EMAIL)

[Liam.M]

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: (e-mail address removed) - view profile
Date: Tues, Jun 6 2006 12:13 pm
Email: (e-mail address removed)
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!

 

[Ken Sheridan]

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

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

 

[Liam.M]

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

 

[Ken Sheridan]

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

 

[Liam.M]

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

 

[Ken Sheridan]

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

 

[Ken Sheridan]

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 "(e-mail address removed)"

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

 

[Liam.M]

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
aware:



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 ("(e-mail address removed)")

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

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

 

[Ken Sheridan]

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 "(e-mail address removed)", .Fields("VesselName"),
..Fields("IMONumber")

Ken Sheridan
Stafford, England

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
aware:



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 ("(e-mail address removed)")

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

 

[Liam.M]

hey Ken.....thankyou so much for your reply....very useful...however,
before I recieved your reply, I opted, under the guidance of another
forum user who has been extremely helpful...to filter out all of the
records that I am not interested in through a Query....thus just
showing the records that I need to remind myself of. My problem at the
moment...is I need to be able to "loop" (if this is the correct
terminology) through all of the records shown by/ within this Query,
and grab certain fields from each record and place it in the body of an
email and send it to myself....any suggestions and code that would
allow me t do this? your help and feedback would be greatly
appreciated....

Tks/Brgds

Liam

p.s: so far someone has given me the following...yet I am struggling to
interpret it...

*For the loop and to pass the fields to the SendMail command?
Dim rst As DAO.Recordset
Dim strList As String

Set rst = DBEngine(0)(0).OpenRecordset("qryEmail")
Do Until rst.EOF
strList = rst.Fields("SMBA Number") & " " & rst.Fields("Vessel
Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Date of
Issue") & vbCrLf
SendMail ("(e-mail address removed)")
rst.MoveNext
Loop


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


rst.Close
Set rst = Nothing


End Function
End Sub

*the SendMail 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 = fMsgBody
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 = fMsgBody


olMail.Send


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


End Sub
Ken Sheridan wrote:

 

[Ken Sheridan]

Liam:

You'd pass the string expression into the SendMail procedure by amending it
so it takes a second argument, a single argument this time rather than one
for each field as in my last post. So the procedure would be along the
following lines:

Sub SendMail(strTo As String, strList As String)

Dim strsubject As String
Dim strBody As String
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"
strBody = "The following accounts are due:" & vbCrLf & strList
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 = strBody


olMail.Send


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


End Sub

You'd now call it with:

SendMail ("(e-mail address removed)", strList)

You can remove all references to the fMsgBody variable from the calling
procedure.

Incidentally I notice the following expression includes some zero length
strings:

strList = rst.Fields("SMBA Number") & " " & rst.Fields("Vessel Name") & "" &
rst.Fields("IMO Number") & "" & rst.Fields("Date of Issue") & vbCrLf

Should these be spaces?

strList = _
rst.Fields("Vessel Name") & " " & _
rst.Fields("IMO Number") & " " & _
rst.Fields("Date of Issue") & vbCrLf

Breaking the line up with the _ continuation character makes for better
readability of the code.

Ken Sheridan
Stafford, England

 

[Liam.M]

Hey Ken,

Thankyou so much for your reply.....I am having great difficulty
getting this process completed, and it is quite frustrating, as
unfortunately I do lack the knowledge and experience, yet I am so close
to finishing. Within this thread....you provided me @ the start with a
"working" solution, that I had implemented...it was fantastic.... the
code was :
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

However, now this "If. Fields" Date criteria, is no longer required, as
I have moved this into my Query (SQL Statement), so only the records
shown meet that date criteria!

I have made the adjustment to the "Sub SendMail" Code, to the one
provided by yourself....but how do I intiate this action to pass all of
the records to this "Sub SendMail", i.e the code above but now adjusted
to perform what I have asked! (getting all the records shown by Query
and their fields "IMO Number, Vessel Name, Date of Issue, Due Date")
and sending them to "SendMail". If you could please spare the time to
provide me with the code, like before, it would be sooooooooooooooooo,
sooooooooooooo, so very, very much appreciated.

Kind Regards,

Liam.

 

[Ken Sheridan]

Liam:

You just need to pass the list of values which you concatenated into the
strList variable into the procedure as the second argument:

Dim rst As Object
Dim strList As String

Set rst = Me.Recordset.Clone

With rst
.MoveFirst
Do While Not .EOF
strList = _
.Fields("Vessel Name") & " " & _
.Fields("IMO Number") & " " & _
.Fields("Date of Issue") & vbCrLf
SendMail .Fields ("(e-mail address removed) "), strList
.MoveNext
Loop
End With

Set rst = Nothing

Make sure you have a space after SendMail.

If you use the form's recordset's clone like this rather than basing the
recordset on the query the form's RecordSource could either be the qryEmail
query or you could keep the original RecordSource and rather skip over the
irrelevant records as before or filter it like so:

Me.Filter = "DueDate <= #" & _
Format(VBA.Date,"mm/dd/yyyy") & _
"# And DueDate <= #" & _
Format(DateAdd("m", 2, VBA.Date),"mm/dd/yyyy") & "#"
Me.FilterOn = True

You'd put that before the Set rst = Me.Recordset.Clone line.

Then after generating the emails turn the filter off with:

Me.FilterOn = False

which you'd put after the Set rst = Nothing line.

If you want to base the recordset directly on the query rather than via the
form's recordset's clone then it would go like this:

Dim rst As ADODB.Recordset
Dim strList As String

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:="qryEMail", _
CursorType:= adOpenForwardOnly

Do While Not .EOF
strList = _
.Fields("Vessel Name") & " " & _
.Fields("IMO Number") & " " & _
.Fields("Date of Issue") & vbCrLf
SendMail .Fields ("(e-mail address removed) "), strList
.MoveNext
Loop
End With

Set rst = Nothing

Ken Sheridan
Stafford, England

 

[Liam.M]

Hey Ken,
You provide by far THE most professional help in this forum, every
single time you have provided me with a solution that not only
works...and an easy explaination of how to implement the solution, in
laymen's terms, and not trying to make yourself sound like the best
thing since sliced bread, as some other tech's on here, often do!
So once again thankyou so much for your help and assitance.
I have implement the code:

Private Sub Whatever_Click()

Dim rst As Object
Dim strList As String


Set rst = Me.Recordset.Clone


With rst
.MoveFirst
Do While Not .EOF
strList = _
.Fields("Vessel Name") & " " & _
.Fields("IMO Number") & " " & _
.Fields("Date of Issue") & strList & vbCrLf
SendMail ("(e-mail address removed) "), strList
.MoveNext
Loop
End With

Set rst = Nothing

End Sub

Into an "On Click" event proceedure, which initiates my "SendMail"
function...all is working fine...I do however, have another couple of
questions that I would very much appreciate assistance with. The
SendMail function...based on the above code sends me those fields but
instead of "each record" individually...what it is doing is say for
example, when it sends record three...it also sends record one and
two's fields with it....how can i stop this so it either...just sends
one email with all of the records...or two emails each record
individually?

Also...do u know the easiest manner in which to format the email
structure better?


Kind Regards,

Liam.

 

[Ken Sheridan]

Liam:

I should have spotted this before! The code is concatenating the current
row's values to the existing value of the strList variable rather than
building it from scratch. This is a common technique when you want to
incrementally built a value list as you loop through a set of records; I use
it for instance in a function which builds a list of recipients to whom a
letter is copied when a mail merge is generated from within Access. Its not
what's needed here though, so change it to:

With rst
.MoveFirst
Do While Not .EOF
strList = _
.Fields("Vessel Name") & " " & _
.Fields("IMO Number") & " " & _
.Fields("Date of Issue") & vbCrLf
SendMail ("(e-mail address removed) "), strList
.MoveNext
Loop
End With

If you don't want the carriage return/line feed at the end simply remove the
& vbCrLf.

I'll have to pass on formatting the email. I'm afraid. My knowledge of
Outlook is about equal to my ability to walk on water. It might be worth
posting in the Outlook discussion group on that aspect.

Ken Sheridan
Stafford, England

 

[Liam.M]

Hey Ken,

Sorry for the late reply here, I have been quite sick...but this
database keeps driving me on! Once again THANKYOU sooooo much for all
your assistance...your help on these threads has undoubtably been the
best and most professional (and I am not just saying this for the fun
of it....but credit given where credit's due).

I have managed to implement the code provided by yourself successfully,
and I have managed to "mark-up" the structure of the email (formatting)
into the V.B code itself, which was relatively easy!

Now all I need to do is fully "automate" this whole process and this
project is finished..yay!

I have downloaded a scheduling utility, and intend on purhasing the
express-click yes pro software to get through the security warnings
imposed my mircosoft outlook.

My only problem is this:

I intend on scheduling my database to load automatically every two
weeks and this whole emailing reminders etcetera to take place.
However, here is the problem...currently ALL of the code is located in
"On-click" Event Proceedures...and therefore I need to automate the
code (without placing it into a forms "on load" event. I understand
that I can run Macro's directly from the scheduling utility using an /x
command line...HOWEVER...I do not want to re-write all of the code you
have helped me with...as it will then have to become a Public
Function....completely altering the code, true?

Any suggestions?

 

[Ken Sheridan]

Liam:

The only thing which makes it necessary to have the code in a bound form's
module is the use of the form's recordset's clone. If you base the
recordset directly on your query you can do it in a function in a standard
module which you can call with a macro. I included code for establishing a
recordset based on a query in my post of 23 June.

I'm away for a couple of weeks from tomorrow, so this ill be my last log-in
until then. Good luck.

Ken Sheridan
Stafford, England