send emails from access

A

angie

i have been searching the internet for two days now and i am really confused
with what i have to do. i have a table with all my clients' email addresses.
my goal is to send separate email with pdf attachment to each customer
separately (not like bcc).

i assume i have to create a form with message subject, message body, etc.
then i need a "sendemailtoall" command button.

could someone help me? i need the code i have to paste in a module.

i am using office 2003.
 
R

Rick Brandt

angie said:
i have been searching the internet for two days now and i am really
confused with what i have to do. i have a table with all my clients' email
addresses. my goal is to send separate email with pdf attachment to each
customer separately (not like bcc).

i assume i have to create a form with message subject, message body, etc.
then i need a "sendemailtoall" command button.

could someone help me? i need the code i have to paste in a module.

i am using office 2003.

What have you got so far? Basically you need to combine two separate
concepts. Sending an Email that includes data from a Form for some of its
components and looping through a set of records pulled from a table.

Concept 1)
Assuming your PDF attachment is being created with Access 2007's built in
PDF capability the simplest method is to use the SendObject method of the
DoCmd object...

DoCmd.SendObject acSendReport,"ReportName", acFormatPDF,
"(e-mail address removed)",,,"Some Subject","Some Message",True

I'm guessing about the acFormatPDF as I don't actually know what the pdf
constant is for Access 2007 having never used it.

Now, If that function were being called in a Form's code module, it is
relatively easy to replace argument literals with references to controls on
the form...

DoCmd.SendObject acSendReport,"ReportName", acFormatPDF,
"(e-mail address removed)",,,Me.Subject,Me.MessageBody,True

In the above the subject and message body are being pulled from the form.

Concept 2)
Retrieve the Emails from your table and loop through the results sending an
Email in each loop cycle. I personally would use a DAO Recordset for
this...

Dim rs as DAO.Recordset
Dim db as Database
Dim sql as String
Set db = CurrentDB
sql = "SELECT EmailAddress FROM SomeTable"
Set rs = db.OpenRecordset(sql,dbOpenSnapshot)

Do Until rs.EOF
DoCmd.SendObject acSendReport,"ReportName", acFormatPDF,
rs!EmailAddress,,,Me.Subject,Me.MessageBody,True
rs.MoveNext
Loop

Notice how inside the loop a reference to the EmailAddress field within the
Recordset has been substituted for a hard-coded literal.

Note that I included a final "True" argument in all of the SendObject calls
above. That would be there for testing as it causes the message to be
displayed on-screen before sending (at which point you can cancel sending
it). Once you have the routine doing what you want, changing that to False
would cause the message to be sent automatically in the background.
 
M

mcescher

i have been searching the internet for two days now and i am really confused
with what i have to do. i have a table with all my clients' email addresses.
my goal is to send separate email with pdf attachment to each customer
separately (not like bcc).

i assume i have to create a form with message subject, message body, etc.
then i need a "sendemailtoall" command button.

could someone help me? i need the code i have to paste in a module.

i am using office 2003.  

Well, why don't you start by telling us what you've tried so far.
What is the format of your table? Are the PDFs different for each
email address? How will you associate the email address to the PDF
file? What email client are you using? (MS Exchange, IBM Domino,
etc...)

We like to volunteer, but we don't want to do your entire job. Give
us a little more information, and we'll go from there.

Chris M.
 
A

angie

first of all i apologize for the multiple posts, but for a strange reason i
could not find my posts. i will try to be more specific. i am using access
2003, ms outlook 2003 and i have acrobat writer,distiller, etc. installed on
my pc.

my database consists of one table with client data, e.g. name, email
address, category, etc.

i have created a query with criteria that returns records (clients) of my
choice with name and email address fields.
i have created a form with the following: file path (the file i want to send
as an attachment located in my computer), sender (my name), subject , message
body.

i also have a command button "sendemailtoall".

my aim is when i press this button to have access send separate email to
each customer with the file attached.

i will skip the convert report to pdf step for later when i achieve this
goal first.

if anyone could help me with the code i have to paste behind the command
button i would very greatful.
 
R

Rick Brandt

angie said:
first of all i apologize for the multiple posts, but for a strange reason
i could not find my posts. i will try to be more specific. i am using
access 2003, ms outlook 2003 and i have acrobat writer,distiller, etc.
installed on my pc.

my database consists of one table with client data, e.g. name, email
address, category, etc.

i have created a query with criteria that returns records (clients) of my
choice with name and email address fields.
i have created a form with the following: file path (the file i want to
send as an attachment located in my computer), sender (my name), subject ,
message body.

i also have a command button "sendemailtoall".

my aim is when i press this button to have access send separate email to
each customer with the file attached.

i will skip the convert report to pdf step for later when i achieve this
goal first.

if anyone could help me with the code i have to paste behind the command
button i would very greatful.

There is no built-in messaging capability that will allow you to add an
external file attachment. For that you would need to use OLE Automation to
manipulate an external messaging library like Outlook or CDOSys.

If you do not use Microsoft Outlook (Outlook Express doesn't count) then you
are best off using CDOSys. That is automatically included in Windows 2000
and higher.

If you search the web (or these Access groups) you should be able to find
links to example code for using the CDOSys library for sending an Email
message.
 
A

angie

thank you for the useful link. i have downloaded the page with the sample
database containing the two modules.

i assume i have to paste the two modules in my database. but how can i make
the command button to work?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top