?
Solved

How do I send email messages via Access using addresses from a table? The message will be different for each receiver.

Posted on 2005-04-20
34
Medium Priority
?
549 Views
Last Modified: 2010-05-18
Hello,

We need to send email messages to about four hundred persons.  The email letter being sent to each of them will indicate that they have been accepted into a special program (the email will tell them which program); they have been awarded a certain amount of money to work with (the email will tell them how much); and they will be given a contact person, number and address to call (the email will given them that information also).  The table has all of the information they need and a filter will separate the selected person from the those not selected.  We use Outlook and would like for this to be close to automatic where we do not have to create separate letters and cut-n-paste into email messages.  The subject line will be the same for all messages.  The return will be from the user who is signed onto the system.

We have MS Word and can use it in conjunction with Access, if necessary.

Any ideas?  A simple solution, if possible, will be appreciated since we don't know a lot about the technical end of Access.

Thanks!

sherman6789
0
Comment
Question by:sherman6789
  • 12
  • 9
  • 7
  • +2
33 Comments
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13826626
You can use the CDO For Windows 2000 library along with ADO (to get the e-mail addresses from the table) in a simple loop.  CDO is very easy to use in sending e-mail although the e-mails sent will not show up in the "sent items" folder as it uses direct connection to your mail server.  You can also use the Outlook object if you want to retain the sent e-mails in your sent items folder.  Let me know if you need an example of CDO usage, like I said it is pretty easy.

Hope this helps get you in the right direction!
0
 

Author Comment

by:sherman6789
ID: 13826676
Thanks for your prompt response,  jfkrueger!

I would like to see CDO samples.  Do I need to download anything or is it part of the Access system?

sherman6789
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13826712
I use this function in my ASP - you could loop through your recordset containing e-mail addresses and call the function in each pass:

Function SendMail(strSMTPServer, strBody, strSendFrom, strSubject, strTo)

    Dim iMsg
    Dim iConf
    Dim Flds
    Dim strHTML    
   
    Const cdoSendUsingPort = 2
   
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
   
    Set Flds = iConf.Fields
   
    'Set the CDOSYS configuration fields to use port 25 on the SMTP server.
   
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPServer
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        .Update
    End With
   
    'Apply the settings to the message.
    With iMsg
   
        Set .Configuration = iConf
       
        .From = strSendFrom
        .Subject = strSubject
        .HTMLBody = strBody
        .To = strTo
       
        .Send
       
    End With
   
    'Clean up objects.
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing

End Function
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:sherman6789
ID: 13826722
Hi again,

What do "CDO" and "ADO" stand for?
Do I need to make any adjustments for Outlook to use this data?

Thanks!

sherman6789
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13826738
Presumably you have a table with a list of names and email addresses.  You can do a simple mail merge in Word.  There is an email message option.

Which version of Word / Access are you using?

Wing
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13826757
It is a reference you can point to in your ASP project (are you using InterDev?) ...if so just go to "Project" on the menu bar, then "Project References" and check the "Microsoft CDO for Windows 2000 library".  If you don't have that, you could also use the "Microsoft CDO 1.21 Library".

Jkrueger
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13826803
CDO is much easier than doing a Mail Merge with Word.  The ASP function I posted can be easily converted to VBA and used.  
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13826839
Hey Sherman,

CDO stands for "Collaborative Data Objects" (I think) and ADO is "Active X Data Objects".  You could also use "DAO" instead of ADO, but both of them are just a means of connecting to your tables and pulling the data.  Basically you just need a way to get the e-mail addresses from the table and ADO or DAO will allow you to do this...if you have this taken care of then don't worry about ADO or DAO.  CDO does not neccessarily use Outlook, it can but the function I posted completely bypasses it and sends the e-mails directly out through your mail server.  I usually just CC myself on them so I know the e-mails went out.
0
 

Author Comment

by:sherman6789
ID: 13826850
Thanks you.

I am using MS Word 2002.

As you can see by my questions, I am very new at this.  I will try to digest the information that you have given me.  I will also show your messages to other who have more knowledge about this and can help.

Where do I put the control functions that you have listed?  Where or how do I get access to the Microsoft CDO Library?

I will accept this as the correct answer tomorrow after I try several experiments.  Thanks again for your assistance.

Sherman6789
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13826970
Chuckle chuckle

Well I'd have to disagree ( and I am sure that most people would) that pure code is easier than MS Word to a complete newbie.  Maybe I havent got the full picture here but where are they going to put this code?  There has been no mention of ASP here?

Stick with Word matey unless you want to hit the ground running with VBA.  It's not that difficult but Word will give you an ongoing solution that is already set up.  

Anyway, best of luck to you.

Wing
0
 

Author Comment

by:sherman6789
ID: 13827030

Hello WingYip

Thanks for writing,  How do I find the Word solution that is already set up?  Remember, all of the data is in Access.

Also, I have asked jfkrueger where to put the code that was written.  I am waiting for that information also.

We do appreciate the responses that I have been getting.

Sherman6789
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13827109
WingYip...I would also agree that for a newbie Word MAY be easier, however that can open up a whole new can of worms when you go messing with Word & Mail Merge (which I have seen fail many, many times).  The VBA is very simple, but for someone who is not all that familiar with VBA it may take some work to understand.  However, it provides a much cleaner solution in the long run that can be used throughout basically any application.  I had the function already written in ASP, that is why I posted it...it does port easily to VBA or VB.  

Sherman, this is what I would do (high level):

     1) Create a new module in Access
     2) Create a procudeure that will open up your table and pull the records you need in order to get the e-mail addresses.
    3) Loop through this recordset, setting up the e-mail body, to address, from address etc. in each iteration of the loop.
    4) Call the sendMail function in each iteration of the loop.

If you need some details on the code needed to make this happen just let me know, I can provide some code for you that should get you off the ground and running.  You can re-use this function in any access application or VB application or ASP application that you create, which is a huge advantage and should be a motivating factor in learning just a little VBA.  

I am not sure how much work the "Word" route would take, it may be easier for you but I still think it would be wise to learn this little bit of VBA, it will help you much more in the future than learning how to do a mail merge.

From access (sorry about referring to InterDev) to get the CDO reference you just open up any module, click on Tools --> References and select the appropriate reference the same as I described for the InterDev example.  You will probably also need to select a "Microsoft ActiveX Data Objects" reference, I usually use the 2.1 library.

0
 
LVL 1

Accepted Solution

by:
WingYip earned 1000 total points
ID: 13827114
Here goes:

I just copied this from a web site I found

1. Open a blank Word document
2. From the Tools menu, select Letters and Mailings » Mail Merge Wizard...
The Mail Merge task pane appears.
3. From the Select document type section, select E-mail messages
4. At the bottom of the task pane, click NEXT: STARTING DOCUMENT
5. From the Select starting document section, select Use the current document
6. At the bottom of the task pane, click NEXT: SELECT RECIPIENTS
7. From the Select recipients section, select Use an existing list
8. From the Use an existing list section, click BROWSE...  The Select Data Source dialog box appears.

Note from Wing -  Here is where you select your access database.  IN the Files of Type drop down select either 'All files' or 'Access databases' and browse to the location of your db.  You will then be requried to select the table or query that contains your data

9. Locate and select the file you will use for your list
10. Click OPEN
The Mail Merge Recipients dialog box appears.
11. Select which recipients you want to include in your mail merge
NOTE: To edit the recipients' information, refer to Working with the Recipients List.
12. Click OK
13. At the bottom of the task pane, click NEXT: WRITE YOUR E-MAIL MESSAGE
14. If you have not already done so, type your message and insert the variable fields
15. When finished, click NEXT: PREVIEW YOUR E-MAIL MESSAGES
A preview of your first recipient appears.
NOTE: For more information on editing the recipients' information, refer to Working with the Recipients List.
HINT: To remove a recipient from the mail merge, from the Make changes section, click EXCLUDE THIS RECIPIENT
16. Click NEXT: COMPLETE THE MERGE
17. From the Merge section, click ELECTRONIC MAIL...
The Merge to E-mail dialog box appears.
17. From the To pull-down list, select the field with the email address
18. In the Subject line text box, type a subject line
19. From the Mail format pull-down list, select the desired format for your message
RECOMMENDED: To avoid being alerted on every record regarding an Outlook security measure, select HTML.
20. In the Send records section, select which records you want to include: All, Current Record, or to select only certain records, type in the appropriate values in the From and To text boxes
21. Click OK
The records will now be sent to your Outlook email recipients.

Good luck

Regards

Wing
0
 

Author Comment

by:sherman6789
ID: 13827168
Hi,

A friend just mentioned that we should have the messages sent to the "outbox" so that we can see the results before they are actually sent to these people.  Do you know how, what and where to set Outlook to make to email messages from this program go to the outbox to be released later after they are checked? Or is there a command that will send the output directly to the outbox?

I've also just found out that we need to send cc: to different addresses also.  I believe that I need to create a field called "cc1", "cc2" and "cc3".  This way, when the message is sent, cc: can be sent at the same time to the addresses listed in the table.

Thanks!

sherman6789

0
 

Author Comment

by:sherman6789
ID: 13827190

Thanks WingYip,

Your solution also sound interesting.  I'm taking all of this into consideration and consulting others in the office.  I'll report on our progress tomorrow and I'll keep checking this string for comments.

Thanks to everyone.

sherman6789
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13827285
Hey Sherman,

If you need to messages to go to the outbox first, and not send until you have looked at them, you can use an Outlook application object and MAPI (Instead of CDO) (which is probably going to be a little more complicated than the CDO method).  You can use an Outlook object to create mail messages and place them into MAPI folders (eg. sent items, outbox etc..).  It may be easier to use Word for now until you have more of a handle on the programming aspect.  Just out of curiosity, why do you need to look at them before they go out?  Seems like this kind of defeats the purpose of automating it - especially with 400+ e-mails.  If you can use the CDO and test with your own address enough to know that the messages are being created and displayed correctly, you can be assured that when they get sent out to the real people they will be correct  - eliminating the need to look at each one before it goes out.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13827341
Sherman ...

I would recommend going the VB route since you will have to dynamically change each email message to tell them which program, how much, etc. for each recipient.

Seems like jfkrueger has pointed you in the direction to get started so I will not duplicate his efforts.  You should also be aware that Microsoft is not going to let you dump 400 emails into Outlook.  The Security update will prevent this.  You will have to add this little add on to your Outlook in order to bypass the Security updates.

Check this site for Redemption Outlook ...

http://www.dimastr.com/redemption/


Also, I set this up for a client "email information to customers" same as what you are trying to accomplish with a little twist to it.  Since each report/letter can be different for each customer/recipient when I created the email loop I formatted the Recipient, Subject, CC, and a short brief Body "The attahed document, etc., etc." for the email.  Then the actual report/letter was an Access report that gets printed down to a PDF file.  The PDF file is then attached to the email and sent to the Outbox in Outlook.  The user invokes the Send/Receive button to actually send the emails.

I would make the information that you have stored in Access a report in Access.  That way you will have more flexibility over controlling the variable information that needs to be included.  You can do it with a string in VBA and use that as the Body text for the email but you will be limited especially on how it is formatted.

ET

0
 

Author Comment

by:sherman6789
ID: 13827382
I agree with you that it will defeat one of the purposes by looking at each message first.  We had decided that we would only send a few this way for testing purposes using local email addresses.  We plan to use you previous advice and try to send cc: to a certain address as a historical record of what went out, date and time.  In summary, we actually had not planned to look at 400+ messages but do the above.  Thanks for checking on this intent.

I feel that I had better try to soak in what all of you have written so far and respond later.  If I don't, none of you will have time to do other things because I am full of questions.  To be fair to everyone, I'll spend time digesting these suggestions.

Thanks.

Sherman6789
0
 
LVL 2

Assisted Solution

by:jfkrueger
jfkrueger earned 1000 total points
ID: 13827485
lol...I know that is a lot to digest.  fyi, you can send multiple CC's using CDO (I don't think my function is set up to do this but I could show you how if the time comes).  etsherman makes a very good point about how each letter has to be dynamic, here is an example of how I do this in ASP (easily ported to VBA or VB):

<%
     Dim strBody
     Dim strSubject
     Dim strTo
     Dim strFrom

      strBody = "<HTML>"
      strBody = strBody & "<HEAD>"
      strBody = strBody & "<BODY>"
      strBody = strBody & "Your Enrollment has been reviewed and accepted by your plan administrator and your eligibility administrator."
      strBody = strBody & "Your enrollment should be processed within 3 business days."
      strBody = strBody & "<BR><BR>"
      strBody = strBody & strComments
      strBody = strBody & "</BODY>"
      strBody = strBody & "</HTML>"
          
      strTo = partEmail <---variable with recipients e-mail address (pulled from table)
      strFrom = "<ebmsonline@ebmstpa.com>"
      strSubject = "Updated Enrollment Status"
          
      SendMail strServer, strBody, strFrom, strSubject, strTo
%>

This can all be put into a loop so the e-mail address and any other dynamic elements you want to put in the body (name etc.) will change with each iteration of the loop.

Good luck with this, let us know how it worked out.

jkrueger
0
 
LVL 2

Expert Comment

by:jfkrueger
ID: 13827493
CDO can also send Text e-mails as opposed to HTML...
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13827562
email messages will be sent to the outlook outbox when doing the Word email merge.

However, you will not have the option in the Mail merge wizard to add cc recipients.

AFAIK, the only way to do what you need to do (ie add to the outlook outbox as well as add several cc recipients) would be to use vba and the outlook object library.  Not an entirely trivial task I'm afraid, if you are as you say, very new to this.

The steps would be along the lines of what Mr jfkrueger was suggesting but you would be using objects from the Outlook library instead of CDO.

If you are up to the challenge then please let us all know.

Regards

Wing
0
 

Author Comment

by:sherman6789
ID: 13827631
Thanks,

I've just talked with my supervisors and they've said "drop the cc:" for now.  We'll be lucky to get the first part working.  After that, we can think about cc:.

sherman6789
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13827777
OK if you are dropping the cc requirement then it can be done in Word no problem.

Mail merge was created for dynamic letters but this mail merge will be more complex if you need it to make decisions based on what is in your database.

It would be better if you had all of the information in your database records that is required for each email message.  For example.  I need to send this simple email out to several clients

Dear <name>

Your <course> starting on <coursedate> is confirmed.

The amount payable is <courseprice>.

The course will take place at <CourseLocation>

Bye for now

Each part inside the <> is data from a single row in a database table.  If you have all the information that is required in a db table or query then your message will be dynamic.

It is possible to get the mail merge to make simple decisions based on data it finds but you may not be able to do exactly what you need.

Regards

Wing

0
 

Author Comment

by:sherman6789
ID: 13827955

Thanks WingYip,

Your example is exactly what we need.  I made sure that all of the information was in each row.  All filtering and decision will be pre-made in Access.


sherman6789
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13828949
WingYip ...

As I mentioned earlier, when I run your Mail Merge option Outlook will prompt you on each message that another program is electronically trying to access Outlook??  Is there a way to turn the prompt off.  The Outlook Redemption DLL works with the Outlook Object Library.

ET
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 13829923
Just a little note.

Though I admitt I did not read all the posts.

FMS has a utility that can send emails from Access.

I don't know if it fits your question, but here it is:

http://www.fmsinc.com/Products/Emailer/index.asp
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13831413
Hi etsherman

On a fully patched version of OFF2002, if you choose HTML as your email message format then you should be able to allow access to your mailbox for say 5 mins and should not be prompted to confirm for each email.  Plain text will cause the prompt however.

Did you already try that?

Wing
0
 

Author Comment

by:sherman6789
ID: 13836184
No.

Thanks for the time
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13836563
In Off2002 it probably works but not in Office 2000 you will get that prompt each time.

Which version of Office is sherman6789 using???

ET
0
 

Author Comment

by:sherman6789
ID: 13837163

Sherman6789 is using Word 2002; however, other persons who will be using it may have 2000.  

Thanks

sherman6789
0
 
LVL 1

Expert Comment

by:WingYip
ID: 13837379
Fraid I cant say for 2000 at this time.

Better test it I guess

Wing
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13837498
sherman6789 and Wing

If you are going to use Off2000 then you will need the following ...

http://www.contextmagic.com/express-clickyes/

Office 2000 will prompt you on each email which will be annoying.  Even if you select access for 10 minutes, it still prompts you.

Just FYI ...

ET
0
 

Author Comment

by:sherman6789
ID: 13861875

Hello everyone,

It's time for me to close this thread.  I want to thank all of you who have spent your valuable time helping me with this problem.  Special thanks go to JFKRUEGER and WINGYIP for their solutions and advice plus the tips from ETSHERMAN and BOAG200.

I will use the solution from WINGYIP because it is the quickest solution for our immediate problem; however, I will also spend time to learn more about VB and use the solution from JFKRUEGER for our long term solution.  This will be a continuing task and it seems that Mr. Krueger's solution will be execellent for the long run plus I will learn the ins and outs of the Windows Library, CDO, ADO, DAO, etc.  Mr. Yip's solution will get us up and running immediately and I am learning more about the built-in features of Microsoft Word.

I am therefore, spliting the points in half,  250 points for JFKRUEGER and 250 points for WINGYIP.

Again, thanks!
William R. Sherman, Jr.
sherman6789
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question