MS Access 2003 sending emails

[horsetransport]

Hello,
Below is what I "Know how to do" but it doesn't accomplish what I want

I have table called sndmail fields that matter useremail and mailsent
I need to get the sendmessage to loop through to EOF and only send
emails to those with mailsent field "NO"
I know how to get access to send emails using:
Option Explicit
Sub sbSendMessage(O ptional AttachmentPath)
Dim objOutlook As Outlook.Applica tion
Dim objOutlookMsg As Outlook.MailIte m
Dim objOutlookRecip As Outlook.Recipie nt
Dim objOutlookAttac h As Outlook.Attachm ent
On Error GoTo ErrorMsgs
' Create the Outlook session.
Set objOutlook = CreateObject("O utlook.Applicat ion")
' Create the message.
Set objOutlookMsg = objOutlook.Crea teItem(olMailIt em)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add ("positivep...@ aim.com")
objOutlookRecip .Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip =
..Recipients.Ad d("train...@pos itivepets.net")
objOutlookRecip .Type = olBCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test." & vbCrLf & vbCrLf
.Importance = olImportanceHig h 'High importance
' Add attachments to the message.
If Not IsMissing(Attac hmentPath) Then
Set objOutlookAttac h = .Attachments.Ad d(AttachmentPat h)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip .Resolve Then
objOutlookMsg.D isplay
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttac h = Nothing
ErrorMsgs:
If Err.Number = "287" Then
'MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail" & _
"addresses to send your message. For more information, & _
"see the document at http://www.microsoft.c om/office" & _
"/previous/outlook/downloads/security.asp. " "
Else
'MsgBox Err.Number, Err.Description '
End If
End Sub
I also found this that is supposed to perform Exactly what I want but
it bombs with 'improper use of my and other errors PLEASE help:
Private Sub emailList_Click ()
' Loop through a email list generate messages one at a time
Dim dbs As Database, whereStr As String
Dim rstMail As Recordset, UserEmail As Variant
Dim postIt As Integer, UserName As Variant
Dim UserCompany As Variant, UserCountry As Variant
Dim UserComments As Variant, AccessVersion As Variant
Dim EmailDownload As Variant
If Not IsNull(Me!Trial Email) Then
' Just do a single trial email
whereStr = " where userEmail = '" & Me!TrialEmail & "'"
Me!TrialEmail = Null
Else
' Email the list one at a time
whereStr = " where not emailSent "
End If
' Open the database object and select users names that havent been sent

yet
Set dbs = CurrentDb
Set rstMail = dbs.OpenRecords et("select * from softwareUsers " &
whereStr)
If rstMail.RecordC ount = 0 Then GoTo exitCmdUserDeta ils
rstMail.MoveFir st
Do Until rstMail.EOF ' Begin loop
postIt = MsgBox(UserName & " " & rstMail!UserEma il & _
" ... " & rstMail!UserNam e & " ... " & _
rstMail!UserCom pany, vbYesNoCancel, _
"Email The Following"
If postIt = vbYes Then
' Output the message as email. Build a complete email message
' from the user detail and the message on the output form
' Place the user comments at the bottom so that you can refer
' to them for that personal message !
DoCmd.SendObjec t acSendNoObject, , acFormatTXT, _
rstMail!UserEma il, , , Me![SubjectReq], _
Me![GreetingReq] & " " & rstMail!UserNam e & _
Chr(10) & Chr(10) & Me![Instructions] & _
Chr(10) & Chr(10) & rstMail!UserCom ments
' Update the email sent box
rstMail.Edit
rstMail("EmailS ent") = True
rstMail.Update
Else
exitCmdUserDeta ils:
rstMail.Close
Exit Sub
End Sub
Thankyou,
Susan

[pietlinden]

<SNIP>
I have table called sndmail fields that matter useremail and mailsent

I need to get the sendmessage to loop through to EOF and only send
emails to those with mailsent field "NO"
</SNIP>

"SELECT <Fieldlist> from MyTable WHERE MailSent=False; "
- open that recordset, loop through it, send e-mails to each. It's
already filtered.

the problem with CurrentDB is that it's in the DAO object library, and
if you're using some versions of Access (2000, maybe 2002), only ADO is
registered by default. So you need to register it. OPen a code
module, Tools-References-find DAO 3.x... and click it. Then if you
need to have both ADO and DAO registered (checked), then you need ti
disambiguate your references. For example...

dim rsDAO as DAO.Recordset
dim rsADO as ADO.Recordset

HTH

[pietlinden]

Just reading this message is getting me confused. So I'll look at the
first example (automating Outlook) instead of the second (using
SendObject).

Here's my simplified example...
dim rsMsgInfo as DAO.Recordset
dim objOutlookMsg as object

Set rsMsgInfo = dgengine(0(0).O penRecordset("S ELECT...FROM... WHERE...",
dbOpenDynamic)
Set objOutlookMsg = objOutlook.Crea teItem(olMailIt em)

do until rsMsgInfo.EOF
with objOutlookMsg
.To = rsMsgInfo.Field s("EmailAddress ")
.Body = "Dear " & rsMsgInfo.Field s("FirstName"). ..
.Subject = "SOME SUBJECT:
.Send
rsMsgInfo.MoveN ext
Loop

rsMsgInfo.Close
set rsMsgInfo=Nothi ng

Hopefully that's more helpful than the last oh so very helpful post...
Pieter

[CDMAPoster]

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

Just reading this message is getting me confused. So I'll look at the
first example (automating Outlook) instead of the second (using
SendObject).

Here's my simplified example...
dim rsMsgInfo as DAO.Recordset
dim objOutlookMsg as object

Set rsMsgInfo = dgengine(0(0).O penRecordset("S ELECT...FROM... WHERE...",
dbOpenDynamic)
Set objOutlookMsg = objOutlook.Crea teItem(olMailIt em)

do until rsMsgInfo.EOF
with objOutlookMsg
.To = rsMsgInfo.Field s("EmailAddress ")
.Body = "Dear " & rsMsgInfo.Field s("FirstName"). ..
.Subject = "SOME SUBJECT:
.Send
rsMsgInfo.MoveN ext
Loop

rsMsgInfo.Close
set rsMsgInfo=Nothi ng

Hopefully that's more helpful than the last oh so very helpful post...
Pieter

You're doing a great job Piet and getting better all the time. I would
have added a few more errors than you did just to make sure that the
one-time poster is a programmer and not a SPAMMER. I'd hate to get
emailed an advertisement for something like pet training. It's not too
late to say, "Oops" and add some disinformation :-). Yet you gave
enough information that a programmer can get past the biggest hurdle.
Well done.

James A. Fortune
CD********@Fort uneJames.com

[horsetransport]

Actually I do 'horse training, transportation and I happen to really
like to use ms access and dable with asp to make my life 'easier' (hah
somewhat funny since I am SOOOOOO Slow compared to you guys that are
the masters :)

I know positivepets probably is VERY funny on a db group :)

Thank you for taking the time to answer my post and I will let you know
if the responses gets me where I need to be but I have to get the email
addresses from the table not manually type them!

Thanks and oh by the way I am not using this to 'telemarket' or
whatever you would call it - rather I am using this to reply to people
who Post a request for training/transport to my website!!!!

[CDMAPoster]

horsetransport wrote:

Actually I do 'horse training, transportation and I happen to really
like to use ms access and dable with asp to make my life 'easier' (hah
somewhat funny since I am SOOOOOO Slow compared to you guys that are
the masters :)

I know positivepets probably is VERY funny on a db group :)

Thank you for taking the time to answer my post and I will let you know
if the responses gets me where I need to be but I have to get the email
addresses from the table not manually type them!

Thanks and oh by the way I am not using this to 'telemarket' or
whatever you would call it - rather I am using this to reply to people
who Post a request for training/transport to my website!!!!

Thanks.

James A. Fortune
CD********@Fort uneJames.com

Seen on a bumper sticker:
My Labrador is smarter than your honor student.