Looping, extracting and emailing?

[Liam.M]

Hey guys,
If anyone could spare sometime to help me out, it would be very much
appreciated.... .what I am trying to do is automate a "Command" that
sends me an Email. I have created a Query that grabs all the records I
am after...now all I need to do is get some code that will allow me
loop through each of these records shown by the query and pass certain
fields from these records into the Body of my email command...which
then emails itself to one person, and that person being myself.

The Email command I have at the moment, is as follows:

Sub SendMail(strTo)

Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
varbody = fMsgBody
Set olApp = CreateObject("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(olMailItem)
olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = fMsgBody
olMail.Send
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Sub
I just need some code to automate the above with the fields from the
records passed into the Body of the email....so far I have this......

Private Sub SBMACheckAndEma il_Click()
Dim rst As DAO.Recordset
Dim strList As String

Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("SMB A Number") & " " & rst.Fields("Ves sel
Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Dat e of
Issue") & vbCrLf
SendMail ("ga*********** *@hotmail.com")
rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing
End Function
End Sub

If someone could point me in the right direction it would be very much
appreciated

Kind Regards

[pietlinden]

> I just need some code to automate the above with the fields from the

records passed into the Body of the email....so far I have this......

Private Sub SBMACheckAndEma il_Click()
Dim rst As DAO.Recordset
Dim strList As String

Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("SMB A Number") & " " & rst.Fields("Ves sel
Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Dat e of
Issue") & vbCrLf
SendMail ("ga*********** *@hotmail.com")
rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing
End Function
End Sub

If someone could point me in the right direction it would be very much
appreciated

Kind Regards

WOW. You're confused. I thought you wanted to send the entire query
result to yourself in a single e-mail. What you're doing now is
sending an e-mail per record. If not, you have to put the .SendMail
call OUTSIDE your loop. You might want to get a beginner book on VBA.
Perhaps that will sort you out a little. From the look of your code,
you don't really understand what's going on at all. And as it says in
the 10 commandments...

Thou shalt not copy and paste other people's code without at least
attempting to understand what it does.

You might be better off thinking this through on paper and writing down
exactly what you want to achieve. Then you can figure out the basic
steps and then solve those problems one at a time. I'm not saying this
to be critical, but because your code shows that you seem not to
understand what's going on in the code. And without that, you won't
learn a thing. Sorry to be rough, but that's the way I see it.

Here's a starter list:
1. create the message body by collapsing the query result into either
an HTML table or something I can stuff into the Message Body.
2. prepend the field names in the query (if necessary)
3. write some code to send the e-mail.

You don't need a form at all to do this. You can open the recordset in
a code module and process from there. Then call it when the DB opens
or something...

[Liam.M]

hahahaha so harsh, but so true! I definately agree with you...I am
getting rather confused, but please also understand this fact, I am
more than competent in areas such as Web Design and HTML/ CSS, but when
it comes to V.B I definately lack the experience and knowledge....an d
hence why I seek the assistance of professionals such as yourself, who
always have been extremely helpful. The assistance that I have been
provided by yourself and others through these forum's has been
priceless, and it is EXTREMELY well appreciated! I do however, have a
rough idea about certain elements of the code and what it is doing, and
have therefore been able to essentially "wing" alot/ all of my database
construction, as I'm essentially unable to write code myself...yet I
can adopt/interpret/manipulate most of the code (so far..and up until
now)....for my purpose and intent!?

I can assure you though, however, that once I have finally figured out
this last little piece of the puzzle...my database shall be complete,
and it will definately be my last database attempt...I WILL be retiring
indefinately hahaha! I have learnt many valuable lessons and it has
been challenging to say the least BUT there will be NO MORE :-p
Soooooooooo if you could please just bare with me and help get me
through this last step, then its game over, and what a games its been!

Now you are correct...I dont neccessarily want to have each record
emailed individually... but perhaps the whole table...how would I go
about this....
Under the suggestion and help of Mr.Ken Sheridan...my email code has
been modified to this:
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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
strBody = "The following accounts are due:" & vbCrLf & strList
Set olApp = CreateObject("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(olMailItem)
olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = strBody
olMail.Send
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub

To be called by: SendMail ("gatecrasher.. [e-mail]", strList)

And the values to be placed in the email:

strList = _
rst.Fields("Ves sel Name") & " " & _
rst.Fields("IMO Number") & " " & _
rst.Fields("Due Date")& " " &_
rst.Fields("Dat e of Issue") & vbCrLf
The Question is how am I meant to incorporate all of this for my intent
and purpose...as previously explained to you?

Tks/ Brgds

Liam.

[pietlinden]

> 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.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
strBody = "The following accounts are due:" & vbCrLf & strList
HUH? where does the value of strList come from? No call like
strList=fMessag eBody()
???

What follows will only return ONE record, at most. You need to loop
through ALL of them.
strList = _
rst.Fields("Ves sel Name") & " " & _
rst.Fields("IMO Number") & " " & _
rst.Fields("Due Date")& " " &_
rst.Fields("Dat e of Issue") & vbCrLf

I already showed you how to do this. You create a FUNCTION to return
the list of values. Then you assign that result to the .Body property
of your e-mail.

As David said, I already answered your question. The fact that the
answer blew right over your head is not my fault. My original answer,
which I stand by, was:

1. create a function to return the list of records as a single string.
2. create a function to send the e-mail (so I sent you to Danny's
website)
3. create a routine/scheduler to send the e-mails.

The fact that you don't know how to use the NG's effectively is your
own fault. Maybe you should learn to search them. Surely the answer
is there somewhere. you just have to put the pieces together. If you
really want to make this happen without any learning or knowledge on
your part, please insert money.

Thanks,

Pieter

[pietlinden]

FUNCTION fMsgBody()
Dim rst As DAO.Recordset
Dim strList As String
Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("SMB A Number") & " " & rst.Fields("Ves sel

Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Dat e of
Issue") & vbCrLf

rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing
End Function

you call this function INSIDE your e-mailing routine. or just set the
..BODY property of the e-mail to the result of the function, e.g.,

olkMsg.Body = fMsgBody()

If this confuses you that much, you should probably pay someone to sort
it out for ya. At this rate, it'll never get done. You seem to be
moving backwards instead of forwards.

[Lyle Fairfield]

Li****@awamarin e.com.au wrote:

hahahaha so harsh, but so true! I definately agree with you...I am
getting rather confused, but please also understand this fact, I am
more than competent in areas such as Web Design and HTML/ CSS, but when
it comes to V.B I definately lack the experience and knowledge....an d
hence why I seek the assistance of professionals such as yourself, who
always have been extremely helpful. The assistance that I have been
provided by yourself and others through these forum's has been
priceless, and it is EXTREMELY well appreciated! I do however, have a
rough idea about certain elements of the code and what it is doing, and
have therefore been able to essentially "wing" alot/ all of my database
construction, as I'm essentially unable to write code myself...yet I
can adopt/interpret/manipulate most of the code (so far..and up until
now)....for my purpose and intent!?

Have you considered offering to endorse Access in Microsoft
commercials? I think you are the perfect example of the user for whom
MS has created Access 2007.

[Liam.M]

hahahaha thanks lyle...I am Business (Marketing) graduate...so yes I
can talk the talk...Regards Liam
Lyle Fairfield wrote:

Li****@awamarin e.com.au wrote:

hahahaha so harsh, but so true! I definately agree with you...I am
getting rather confused, but please also understand this fact, I am
more than competent in areas such as Web Design and HTML/ CSS, but when
it comes to V.B I definately lack the experience and knowledge....an d
hence why I seek the assistance of professionals such as yourself, who
always have been extremely helpful. The assistance that I have been
provided by yourself and others through these forum's has been
priceless, and it is EXTREMELY well appreciated! I do however, have a
rough idea about certain elements of the code and what it is doing, and
have therefore been able to essentially "wing" alot/ all of my database
construction, as I'm essentially unable to write code myself...yet I
can adopt/interpret/manipulate most of the code (so far..and up until
now)....for my purpose and intent!?

Have you considered offering to endorse Access in Microsoft
commercials? I think you are the perfect example of the user for whom
MS has created Access 2007.

[Liam.M]

okay...created function....sho wn:
Private Function fMsgBody()

Dim rst As DAO.Recordset
Dim strList As String

Set rsDue = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rsDue.EOF
SendMail ("ga*********** *@hotmail.com")
strList = rsDue.Fields("I MO 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
Dim strsubject As String
Dim strBody As String
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
strBody = "The following accounts are due:" & vbCrLf & strList =
fMessageBody()
Set olApp = CreateObject("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(olMailItem)
olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = strBody

olMail.SendMail
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function

yet still doesnt work?

Suggestion?

pi********@hotm ail.com wrote:

FUNCTION fMsgBody()
Dim rst As DAO.Recordset
Dim strList As String
Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("SMB A Number") & " " & rst.Fields("Ves sel

Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Dat e of
Issue") & vbCrLf

rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing
End Function

you call this function INSIDE your e-mailing routine. or just set the
.BODY property of the e-mail to the result of the function, e.g.,

olkMsg.Body = fMsgBody()

If this confuses you that much, you should probably pay someone to sort
it out for ya. At this rate, it'll never get done. You seem to be
moving backwards instead of forwards.

[Liam.M]

pasted wrong section:

Private Function fMsgBody()

Dim rst As DAO.Recordset
Dim strList As String

Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("IMO Number") & vbTab & _
rst.Fields("SBM A Number") & vbTab & _
rst.Fields("Dat e of Issue") & vbTab & _
rst.Fields("Due Date") & vbTab & _
rst.Fields("Ves sel Name") & vbCrLf
rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing

End Function
Dim strsubject As String
Dim strBody As String
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
strBody = "The following accounts are due:" & vbCrLf & strList =
fMsgBody()
Set olApp = CreateObject("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(olMailItem)
olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = fMsgBody()

olMail.SendMail ("ga*********** *@hotmail.com")
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function

[Liam.M]

pasted wrong section:

Private Function fMsgBody()

Dim rst As DAO.Recordset
Dim strList As String

Set rst = DBEngine(0)(0). OpenRecordset(" qryEmail")
Do Until rst.EOF
strList = rst.Fields("IMO Number") & vbTab & _
rst.Fields("SBM A Number") & vbTab & _
rst.Fields("Dat e of Issue") & vbTab & _
rst.Fields("Due Date") & vbTab & _
rst.Fields("Ves sel Name") & vbCrLf
rst.MoveNext
Loop
fMsgBody = "The following accounts are due:" & vbCrLf & strList
rst.Close
Set rst = Nothing

End Function
Dim strsubject As String
Dim strBody As String
Dim strattachment1 As String
Dim strattachment2 As String
Dim olApp As Outlook.Applica tion
Dim olNs As Outlook.NameSpa ce
Dim olMail As Outlook.MailIte m
strsubject = "ATTN:Shore-Based Maintainance Agreements"
strBody = "The following accounts are due:" & vbCrLf & strList =
fMsgBody()
Set olApp = CreateObject("O utlook.Applicat ion")
Set olNs = olApp.GetNamesp ace("MAPI")
olNs.Logon
Set olMail = olApp.CreateIte m(olMailItem)
olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = fMsgBody()

olMail.SendMail ("ga*********** *@hotmail.com")
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function