• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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

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
sherman6789
Asked:
sherman6789
  • 12
  • 9
  • 7
  • +2
2 Solutions
 
jfkruegerCommented:
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
 
sherman6789Author Commented:
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
 
jfkruegerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sherman6789Author Commented:
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
 
WingYipCommented:
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
 
jfkruegerCommented:
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
 
jfkruegerCommented:
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
 
jfkruegerCommented:
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
 
sherman6789Author Commented:
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
 
WingYipCommented:
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
 
sherman6789Author Commented:

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
 
jfkruegerCommented:
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
 
WingYipCommented:
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
 
sherman6789Author Commented:
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
 
sherman6789Author Commented:

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
 
jfkruegerCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
sherman6789Author Commented:
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
 
jfkruegerCommented:
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
 
jfkruegerCommented:
CDO can also send Text e-mails as opposed to HTML...
0
 
WingYipCommented:
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
 
sherman6789Author Commented:
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
 
WingYipCommented:
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
 
sherman6789Author Commented:

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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
WingYipCommented:
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
 
sherman6789Author Commented:
No.

Thanks for the time
0
 
Eric ShermanAccountant/DeveloperCommented:
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
 
sherman6789Author Commented:

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

Thanks

sherman6789
0
 
WingYipCommented:
Fraid I cant say for 2000 at this time.

Better test it I guess

Wing
0
 
Eric ShermanAccountant/DeveloperCommented:
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
 
sherman6789Author Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 12
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now