Creating a function to automated this command! (EMAIL)

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 ")
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:

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 ", 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

" wrote:

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 ")
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

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 wrote:
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 ", 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

" wrote:

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 ")
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

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 "), 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 "), strList
.MoveNext
Loop
End With

Set rst = Nothing

Ken Sheridan
Stafford, England

" wrote:

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.

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 "), 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.

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 "), 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


" wrote:

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 "), 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.

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 wrote:
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 "), 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


" wrote:

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 "), 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.

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

" wrote:

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?