By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,201 Members | 957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,201 IT Pros & Developers. It's quick & easy.

Looping, extracting and emailing?

P: n/a
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.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
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 SBMACheckAndEmail_Click()
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 ("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

Jun 22 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
> 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 SBMACheckAndEmail_Click()
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 ("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...

Jun 22 '06 #2

P: n/a
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....and
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.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

To be called by: SendMail ("gatecrasher...@hotmail.com", strList)

And the values to be placed in the email:

strList = _
rst.Fields("Vessel Name") & " " & _
rst.Fields("IMO Number") & " " & _
rst.Fields("Due Date")& " " &_
rst.Fields("Date 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.

Jun 22 '06 #3

P: n/a
> 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
HUH? where does the value of strList come from? No call like
strList=fMessageBody()
???

What follows will only return ONE record, at most. You need to loop
through ALL of them.
strList = _
rst.Fields("Vessel Name") & " " & _
rst.Fields("IMO Number") & " " & _
rst.Fields("Due Date")& " " &_
rst.Fields("Date 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

Jun 23 '06 #4

P: n/a
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("SMBA Number") & " " & rst.Fields("Vessel

Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Date 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.

Jun 23 '06 #5

P: n/a
Li****@awamarine.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....and
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.

Jun 23 '06 #6

P: n/a
hahahaha thanks lyle...I am Business (Marketing) graduate...so yes I
can talk the talk...Regards Liam
Lyle Fairfield wrote:
Li****@awamarine.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....and
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.


Jun 23 '06 #7

P: n/a
okay...created function....shown:
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("IMO Number") & vbTab & _
rsDue.Fields("SBMA Number") & vbTab & _
rsDue.Fields("Date of Issue") & vbTab & _
rsDue.Fields("Due Date") & vbTab & _
rsDue.Fields("Vessel 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.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 =
fMessageBody()
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.SendMail
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function

yet still doesnt work?

Suggestion?

pi********@hotmail.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("SMBA Number") & " " & rst.Fields("Vessel

Name") & "" & rst.Fields("IMO Number") & "" & rst.Fields("Date 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.


Jun 23 '06 #8

P: n/a
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("SBMA Number") & vbTab & _
rst.Fields("Date of Issue") & vbTab & _
rst.Fields("Due Date") & vbTab & _
rst.Fields("Vessel 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.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 =
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.SendMail ("ga************@hotmail.com")
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function

Jun 23 '06 #9

P: n/a
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("SBMA Number") & vbTab & _
rst.Fields("Date of Issue") & vbTab & _
rst.Fields("Due Date") & vbTab & _
rst.Fields("Vessel 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.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 =
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.SendMail ("ga************@hotmail.com")
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Function

Jun 23 '06 #10

P: n/a
"Still doesn't work" doesn't mean sh... anything.

You're definitely into marketing. You need to learn to talk to tech
folks.

What you said amounts to this:

You go to the doctor. You say "it hurts", but you neglect to say
where. So you get no help. WHERE is a key clue.

How about we play madlibs...

I *expected* to get this <example>
but I got <that> instead.

I started with <starting point>
and I wanted to get to <ending point>.

Connect the dots and you might get some useful help. Otherwise, you're
pissing in the wind.

Okay, since you have no idea how to code, I'd just write a macro that
does something like

SendObject and e-mails the report based on the query you designed. The
help files should help you through it. That and check out NWind.

Jun 23 '06 #11

P: n/a
Liam,

not sure if this is going to confuse or clarify things... here's pretty
much everything in one routine. Be warned... this is UNTESTED code.
I'm cobbling and reading some help files to put this together. The
basic gist is this... get all the junk done inside a single routine.
(It's not that coimplicated, anyway, if you take it one step at a
time.)

Open up your list of due records in code (that's what I'm doing opening
the Querydef).
Open up Outlook and
Send an e-mail to <someaddress> for each record. (loop through
records).
Close Outlook.
Close objects and clean up references (all the stuff you used "Set"
with).

How about...

1. get the records in your recordset, and send them individually to
yourself.
Sub SendAllNotifications()

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strsubject 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
'--open a recordset based on the query so we can loop through the mails
to be sent.
Set rs = DBEngine(0)(0).QueryDefs("qryDueEmails").OpenRecor dset

'--Log into Outlook namespace/account

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

'--send ALL the e-mails to same address with same subject.

do until rs.EOF

Set olMail = olApp.CreateItem(olMailItem)

with olMail
.To = "yo***********@yourdomain.com"
.Subject ="ATTN:Shore-Based Maintainance Agreements"

'---build the msg body from the fields in the query
.Body = "These are due..." & rs.Fields("FieldName1") & " on " &
rs.Fields("FieldName2")

'--add attachments
.Attachments.Add("C:\SomeDir\SomeFile.txt")

'---now send the message
.Send
end with

'---and go to the next record
rs.MoveNext
loop

rs.close
set rs=nothing

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Sub
And the final part... schedule this event.

You have a few choices on this:
1. use the Scheduler at http://www.mvps.org/access/modules/mdl0042.htm
2. run the SendEMails routine (or whatever you call it) in the Startup
event of your database...
3. run it in the timer event of some form that you open and keep
hidden...

Really depends on how often these things are due... Prolly the easiest
is in the startup events of your db.

make sense yet? <fingers crossed!!!>

Pieter

Jun 25 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.