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

MS Access 2003 sending emails

P: n/a
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(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
On Error GoTo ErrorMsgs
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
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.Add("train...@positivepets.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 = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = 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.com/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!TrialEmail) 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.OpenRecordset("select * from softwareUsers " &
whereStr)
If rstMail.RecordCount = 0 Then GoTo exitCmdUserDetails
rstMail.MoveFirst
Do Until rstMail.EOF ' Begin loop
postIt = MsgBox(UserName & " " & rstMail!UserEmail & _
" ... " & rstMail!UserName & " ... " & _
rstMail!UserCompany, 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.SendObject acSendNoObject, , acFormatTXT, _
rstMail!UserEmail, , , Me![SubjectReq], _
Me![GreetingReq] & " " & rstMail!UserName & _
Chr(10) & Chr(10) & Me![Instructions] & _
Chr(10) & Chr(10) & rstMail!UserComments
' Update the email sent box
rstMail.Edit
rstMail("EmailSent") = True
rstMail.Update
Else
exitCmdUserDetails:
rstMail.Close
Exit Sub
End Sub
Thankyou,
Susan

Dec 20 '05 #1
Share this Question
Share on Google+
5 Replies


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

Dec 20 '05 #2

P: n/a
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).OpenRecordset("SELECT...FROM...WHERE ...",
dbOpenDynamic)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

do until rsMsgInfo.EOF
with objOutlookMsg
.To = rsMsgInfo.Fields("EmailAddress")
.Body = "Dear " & rsMsgInfo.Fields("FirstName")...
.Subject = "SOME SUBJECT:
.Send
rsMsgInfo.MoveNext
Loop

rsMsgInfo.Close
set rsMsgInfo=Nothing

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

Dec 20 '05 #3

P: n/a
pi********@hotmail.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).OpenRecordset("SELECT...FROM...WHERE ...",
dbOpenDynamic)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

do until rsMsgInfo.EOF
with objOutlookMsg
.To = rsMsgInfo.Fields("EmailAddress")
.Body = "Dear " & rsMsgInfo.Fields("FirstName")...
.Subject = "SOME SUBJECT:
.Send
rsMsgInfo.MoveNext
Loop

rsMsgInfo.Close
set rsMsgInfo=Nothing

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********@FortuneJames.com

Dec 20 '05 #4

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

Dec 20 '05 #5

P: n/a
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********@FortuneJames.com

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

Dec 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.