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

How to send email via code in an Access Project?

P: n/a

Hi,

I have an Access project that I use to allow a client to hit an SQL
server at my host. This project has several forms that are used to
udpate tables, etc, but I'd also like to be able to include the ability
to allow my client to send an email to everyone that has subscribed to
our web site by going to the table that contains the names and email
addy's and then loop thru the same and send an email to them all by
appending their address to the email TO: field.. or BCC< etc.. I've done
this in ASP, and VB, but not in access.. I of course will be using the
SMTP server at my hosting provider for this.. Can someone point me to
some sample code to do this? I'm using Access 2002 / Office pro 2002..
Thanks

Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Tim Smallwood <do*******@netscape.net> wrote:
I have an Access project that I use to allow a client to hit an SQL
server at my host. This project has several forms that are used to
udpate tables, etc, but I'd also like to be able to include the ability
to allow my client to send an email to everyone that has subscribed to
our web site by going to the table that contains the names and email
addy's and then loop thru the same and send an email to them all by
appending their address to the email TO: field.. or BCC< etc.. I've done
this in ASP, and VB, but not in access.. I of course will be using the
SMTP server at my hosting provider for this.. Can someone point me to
some sample code to do this? I'm using Access 2002 / Office pro 2002..


See the Sample Code illustrating looping through a DAO recordset page at the Access
Email FAQ at http://www.granite.ab.ca/access/email/recordsetloop.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #2

P: n/a
"Tim Smallwood" <do*******@netscape.net> wrote in message
news:40*********************@news.frii.net...

Hi,

I have an Access project that I use to allow a client to hit an SQL
server at my host. This project has several forms that are used to
udpate tables, etc, but I'd also like to be able to include the ability
to allow my client to send an email to everyone that has subscribed to
our web site by going to the table that contains the names and email
addy's and then loop thru the same and send an email to them all by
appending their address to the email TO: field.. or BCC< etc.. I've done
this in ASP, and VB, but not in access.. I of course will be using the
SMTP server at my hosting provider for this.. Can someone point me to
some sample code to do this? I'm using Access 2002 / Office pro 2002..
Thanks

http://www.aspfaq.com/show.asp?id=2403
Nov 12 '05 #3

P: n/a
Tony,

Thanks so much.. I'm at the site now, and what a great web site.. Love
the UI design stuff..
Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
Tim Smallwood <do*******@netscape.net> wrote:
Thanks so much.. I'm at the site now, and what a great web site.. Love
the UI design stuff..


Thanks for the comments. They make the effort worthwhile.

But, umm, what UI design stuff? Other than the one dealing with combing families for
reducing mailing costs I don't have much UI stuff.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #5

P: n/a
Tim,
This is how I have done it.
Create a stored procedure named "GetEMailAddresses"
Create a function to loop through the stored procedure.
Code is below. I hope this helps.
lq
The stored procedure:

/* Create list of e-mail addresses from tblEmployees */

Alter Procedure GetEMailAddresses
As
SELECT
EmailAddress
FROM
tblEmployees
WHERE
(InactiveEmployee = 0)
AND
(EmailAddress Is Not Null)
ORDER BY
tblEmployees.WorkCity

The Function:

Function EmailList()
On Error GoTo myErr
'PURPOSE: Returns a string list of all employees with an e-mail
addresses that are still active employees

Dim RS As ADODB.Recordset, myAddress As String, mySP As
String, _
myField As String, mySubject as string, myMsg as string

mySP = "GetEMailAddresses"
myField = "EmailAddress"
Set RS = CurrentProject.Connection.Execute("EXEC " & mySP)
myAddress = ""
If Not RS.BOF And Not RS.EOF Then '>has found at least one
record:
Do While Not RS.EOF
myAddress = myAddress & ";" & RS(myField)
RS.MoveNext
Loop
End If
myAddress = Mid(myAddress, 2)

'call your email program using sendobject or another function:

mySubject = "Notice to all employees"
myMsg = "If you are receiving this email it means your job has
been eliminated and you should consider yourself immediately
terminated." & vbcrlf & "Your job has been outsourced to a cheaper
country."

DoCmd.SendObject acSendNoObject, , , myAddress, , ,
mySubject, myMsg, True

myExit:
On Error Resume Next
RS.Close
Set RS = Nothing
Exit Function
myErr:
If Err.Number = 2296 Or Err.Number = 2501 Then
'user cancelled the email after the email template opened
ElseIf Err.Number = 3021 Then
MsgBox "There are no records that match your search.",
vbInformation, "Search Results"
ElseIf Err.Number = 94 Then 'there was a record but it contains a
null value:
'>user cancelled
Else
MsgBox Err.Number & " " & Err.Description
End If
Resume myExit
End Function


Tim Smallwood <do*******@netscape.net> wrote in message news:<40*********************@news.frii.net>...
Hi,

I have an Access project that I use to allow a client to hit an SQL
server at my host. This project has several forms that are used to
udpate tables, etc, but I'd also like to be able to include the ability
to allow my client to send an email to everyone that has subscribed to
our web site by going to the table that contains the names and email
addy's and then loop thru the same and send an email to them all by
appending their address to the email TO: field.. or BCC< etc.. I've done
this in ASP, and VB, but not in access.. I of course will be using the
SMTP server at my hosting provider for this.. Can someone point me to
some sample code to do this? I'm using Access 2002 / Office pro 2002..
Thanks

Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #6

P: n/a
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Tim,
This is how I have done it.
Create a stored procedure named "GetEMailAddresses"
Create a function to loop through the stored procedure.
Code is below. I hope this helps.
lq


If you're using sql server and you have permissions to do so I recommend
configuring mail on the server instead of the client. You only have to
configure it once and thereafter it will work for everyone - even if you
decide to move to a new front-end. You don't need to worry about references,
libraries, outlook versions etc. etc. It's a lot easier to set up and to
administer.
Nov 13 '05 #7

P: n/a
John,
Is there a nutshell version of how this is done? I've never tried
doing this. How does the server resolve issues involving the sender
and return address for a sender's e-mail if everyone is hitting the
same server-side mail client?
lq

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2h************@uni-berlin.de>...
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Tim,
This is how I have done it.
Create a stored procedure named "GetEMailAddresses"
Create a function to loop through the stored procedure.
Code is below. I hope this helps.
lq


If you're using sql server and you have permissions to do so I recommend
configuring mail on the server instead of the client. You only have to
configure it once and thereafter it will work for everyone - even if you
decide to move to a new front-end. You don't need to worry about references,
libraries, outlook versions etc. etc. It's a lot easier to set up and to
administer.

Nov 13 '05 #8

P: n/a
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
John,
Is there a nutshell version of how this is done? I've never tried
doing this. How does the server resolve issues involving the sender
and return address for a sender's e-mail if everyone is hitting the
same server-side mail client?
lq


Although you can use the built-in SQLMail, I prefer to use the xpsmtp
extended stored procedure - this is a custom extended stored procedure (dll)
written to send mail from sql server - more details at
http://www.sqldev.net/xp/xpsmtp.htm This gets around the need to have a
MAPI client on the server. So far we haven't had any problems, except for
sending attachments - for attachments you have to figure out a way to map a
unc path back to the client. We haven't seen any problems with multiple
users hitting the server at the same time - each message gets sent with the
correct to and from addresses.
Nov 13 '05 #9

P: n/a
I'm sorry Tony.. It was a link you provided to
http://www.joelonsoftware.com that I was meaning to thank you for..
Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #10

P: n/a

Laureen,

Thank you so much.. I'll save this and try it out later..

Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #11

P: n/a
Tim Smallwood <do*******@netscape.net> wrote:
I'm sorry Tony.. It was a link you provided to
http://www.joelonsoftware.com that I was meaning to thank you for..


Gotcha. Yes, I quite like visiting that website myself. Interesting stuff.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.