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

How can I Send E-Mail from an Access Table using MS Outlook

P: 12
I am running access 2010. I have a table with email address' as one of the fields. I want to send the same message to all or some of my contacts. The table is named Donors and the field is Email address. I am using outlook express for my e-mail.
Jan 30 '12 #1
Share this Question
Share on Google+
29 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Outlook Express is not an MS Office application, and as such is not likely to fall within the experience range of most of our experts.

Application Automation may help some, but that's as far as I can go with the Outlook Express part I'm afraid.
Jan 30 '12 #2

P: 12
NeoPa, my email is through outlook. If this is not able to work with access what other options do I have.

Thanks for any help
Jan 31 '12 #3

NeoPa
Expert Mod 15k+
P: 31,492
Outlook is well set up to be controlled from within Access. Your question title indicates you're using Outlook Express though. This new post is a contradiction of that so you won't be surprised to find we're somewhat confused at this point.

Please clarify before continuing.
Jan 31 '12 #4

ADezii
Expert 5K+
P: 8,638
The Logic would be as follows, any questions feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. Dim oLook As Object
  2. Dim oMail As Object
  3. Dim olns As Outlook.NameSpace
  4. Dim strTO As String
  5. Dim strMessageBody As String
  6. Dim strSubject As String
  7. Dim MyDB As DAO.Database
  8. Dim rst As DAO.Recordset
  9.  
  10. 'Do you even have E-Mail Addressess in the Donors Table?
  11. '[E-Mail Address] cannot be NULL
  12. If DCount("[E-Mail Address]", "Donors") = 0 Then Exit Sub
  13.  
  14. Set MyDB = CurrentDb
  15. Set rst = MyDB.OpenRecordset("Donors", dbOpenSnapshot, dbOpenForwardOnly)
  16.  
  17. Set oLook = CreateObject("Outlook.Application")
  18. Set olns = oLook.GetNamespace("MAPI")
  19. Set oMail = oLook.CreateItem(0)
  20.  
  21. 'Build the Recipient List
  22. With rst
  23.   Do While Not .EOF
  24.     strTO = strTO & ![E-Mail Address] & ";"
  25.       .MoveNext
  26.   Loop
  27. End With
  28.  
  29. 'Remove Trailing ';'
  30. strTO = Left$(strTO, Len(strTO) - 1)
  31.  
  32. '******************************* USER DEFINED SECTION ********************************
  33. strMessageBody = "Message to ALL Recipients       "
  34. strSubject = "Test Project for E-Mailing Multiple Recipients in Outlook"
  35. '*************************************************************************************
  36.  
  37. With oMail
  38.  .To = strTO
  39.  .Body = strMessageBody
  40.  .Subject = strSubject
  41.    '.Display
  42.      .Send      'Immediately Sends the E-Mail without displaying Outlook
  43. End With
  44.  
  45. Set oMail = Nothing
  46. Set oLook = Nothing
  47.  
  48. rst.Close
  49. Set rst = Nothing
Jan 31 '12 #5

P: 12
Sorry, my mail is through Outlook, not Outlook express.

The table is Donors and the field is E-Mail address

Thanks again and sorry for the misinformation
Jan 31 '12 #6

P: 12
Will try ADezzi Thanks.

ADezii, can I run this code through a Macro or should I copy it into a module and run the module from a Macro?
Jan 31 '12 #7

ADezii
Expert 5K+
P: 8,638
What context do you need to run it in?
Jan 31 '12 #8

P: 12
What I will be doing is sending a message to all listed email address'. One message to all either in a query from the donors table or the donors table itself
Jan 31 '12 #9

NeoPa
Expert Mod 15k+
P: 31,492
Hogue:
Sorry, my mail is through Outlook, not Outlook express.
Right. With that cleared up (I've updated the thread title for you) I can probably leave you in ADezii's capable hands. He's a dab-hand at Outlook automation from Access. I'll keep an eye on this though, in case I can help at any stage.
Feb 1 '12 #10

ADezii
Expert 5K+
P: 8,638
The Code can literally be executed from anywhere. The logical choice would be in the Click() Event of a Command Button on a Form, where all the Code can be self-contained, as posted.
Feb 1 '12 #11

P: 12
I have copied the code into a module and will try to open the module with a macro command within the current table. Will get back to you later. Thanks ADezii
Feb 1 '12 #12

P: 12
ADezii, I would like to run the code from a macro. Can I include the Click() event or some othe start code command in a macro. If so How??????

Thanks for all your help.
Feb 2 '12 #13

ADezii
Expert 5K+
P: 8,638
@Hogue:
Why do you insist on running this Code from a Macro?
Feb 2 '12 #14

P: 12
It doesn't have to be a macro, anything just to make the code run.
Feb 2 '12 #15

ADezii
Expert 5K+
P: 8,638
Create a Command Button on a Form, then Copy-N-Paste the Code to its Click() Event. Be sure to Customize the Code to fit your specific needs.
Feb 2 '12 #16

P: 12
Will give it a try ADezii. As you can see I'm a rookie altogether using code. Thanks

I installed a command button on a form. On the expression builder I set it to On Click. Then I copied the code to the form. When I hit the button nothing happens.
Feb 2 '12 #17

NeoPa
Expert Mod 15k+
P: 31,492
  1. When designing your form, select the object you want to trigger the code from (Probably a Command Button control).
  2. Open the Properties Window (Alt-Enter) and navigate to the On Click property.
  3. At the right of the property value space will appear a button marked with an ellipsis (...). Click on this.
  4. Select the 'Code Builder' option, which takes you to the VBA IDE window and creates a procedure template for you.
  5. Paste ADezii's code into here.

PS. Please stop posting responses which include quotes from other posts which are irrelevant and unnecessary. It just wastes my time removing them all for you. Also, if you have two sentences to reply with then please post them in one post. If you're not ready to post yet - wait until you're ready before posting. It's not too complicated really, even for someone with very little experience.
Feb 2 '12 #18

P: 12
ADezii, When I hit the command button I get an error message

Compile error:

Expected: line number or label or statement or end of statement
Feb 2 '12 #19

NeoPa
Expert Mod 15k+
P: 31,492
To save time later, and until ADezii becomes available, I suggest you post the code as you now have it in your module (exactly - Use Copy & Paste) and also provide the line number that the code stops at when you choose Debug from the error message. The following threads give instructions on how to handle that properly - Before Posting (VBA or SQL) Code & Debugging in VBA - 3) General Tips. If you follow the instructions carefully this should save you both a fair bit of time and trouble (but feel free to ask for clarification if anything is not clear to you).
Feb 3 '12 #20

P: 12
This is the code I copied from the post you gave me Adzii. When I try to run it I get the message,
Compile error
Expected line number or label or statement or end of statement.

It stops on the first line. My table is Donors and the Field is E-Mail address, The database is Database1

1. Dim oLook As Object
2. Dim oMail As Object
3. Dim olns As Outlook.NameSpace
4. Dim strTO As String
5. Dim strMessageBody As String
6. Dim strSubject As String
7. Dim MyDB As DAO.Database
8. Dim rst As DAO.Recordset
9.
10. 'Do you even have E-Mail Addressess in the Donors Table?
11. '[E-Mail Address] cannot be NULL
12. If DCount("[E-Mail Address]", "Donors") = 0 Then Exit Sub
13.
14. Set MyDB = CurrentDb
15. Set rst = MyDB.OpenRecordset("Donors", dbOpenSnapshot, dbOpenForwardOnly)
16.
17. Set oLook = CreateObject("Outlook.Application")
18. Set olns = oLook.GetNamespace("MAPI")
19. Set oMail = oLook.CreateItem(0)
20.
21. 'Build the Recipient List
22. With rst
23. Do While Not .EOF
24. strTO = strTO & ![E-Mail Address] & ";"
25. .MoveNext
26. Loop
27. End With
28.
29. 'Remove Trailing ';'
30. strTO = Left$(strTO, Len(strTO) - 1)
31.
32. '******************************* USER DEFINED SECTION ********************************
33. strMessageBody = "Message to ALL Recipients "
34. strSubject = "Test Project for E-Mailing Multiple Recipients in Outlook"
35. '************************************************* ************************************
36.
37. With oMail
38. .To = strTO
39. .Body = strMessageBody
40. .Subject = strSubject
41. '.Display
42. .Send 'Immediately Sends the E-Mail without displaying Outlook
43. End With
44.
45. Set oMail = Nothing
46. Set oLook = Nothing
47.
48. rst.Close
49. Set rst = Nothing
Feb 8 '12 #21

ADezii
Expert 5K+
P: 8,638
  1. Where are you executing the Code from?
  2. Did you set a Reference to the Microsoft Outlook Object Library?
Feb 8 '12 #22

NeoPa
Expert Mod 15k+
P: 31,492
NeoPa:
I suggest you post the code as you now have it in your module (exactly - Use Copy & Paste) and also provide the line number that the code stops at when you choose Debug from the error message.
I don't know what could possibly be confusing about these instructions. I took great care to make them, what we refer to in the trade as, idiot-proof. I'm confused then that you've posted a poor copy of ADezii's original post (which is entirely unhelpful as we already have that - we need to see what you've done with it, which we cannot from this), without even using the code tags.

NeoPa:
If you follow the instructions carefully this should save you both a fair bit of time and trouble (but feel free to ask for clarification if anything is not clear to you).
I suggest you go back and try again. Please note the last sentence of my previous post (quoted just above). If managing to copy your code into here in code tags is too complicated for you, then please ask for assistance. I've already made the instructions as simple as I can, but if there's anything I can help with, or clarify, I'm happy to assist.

NB. I haven't fixed your post as there's really no point. It contains no useful information at all.
Feb 8 '12 #23

P: 15
There's an easier solution if you have an outlook.pst file storing Outlook data locally. If so, you can link to the file and have a contacts table that you can view and even edit directly within Access. It looks just like a table.

You can then process your emails from that. ** Commercial link removed **
Feb 11 '12 #24

P: 12
Adezzi, I don't know enough about VB to do any of the things u mentioned above. I have sent the code exactly as I copied it and have provided the DB name, table name and the field containing the email address's.

If thats not enough we will have to forget about the process.

Thanks for your help Adezzi
Feb 14 '12 #25

NeoPa
Expert Mod 15k+
P: 31,492
Hogue:
If thats not enough we will have to forget about the process.
I know you've been struggling with this, but I suspect we may have to. We don't exactly forbid experts from doing all the work for a member, but that's not what most of us come here for. We generally expect members to be able to take tips and do the work on their own (That's the concept of Bytes.com, after all), which seems to be a step or two too far for you at this time.

I would suggest, in a spirit of simple good advice, that you start into Access, and the code specifically, at a simpler level. This question is quite an advanced one for a beginner, and even to be able to ask it properly requires a level of understanding that is beyond the pure basics.

Good luck with your project anyway.
Feb 14 '12 #26

ADezii
Expert 5K+
P: 8,638
@Hogue:
I hate to see you leave this Thread empty handed, so I revisited this Thread and examined everything carefully again. If you Copy-N-Pasted my Code verbatim, that could be the problem. You indicated that your Field containing E-Mail Addresses was named [EMail Address], whereas my Code Demo used [E-Mail Address]. In Post# 5, replace [E-Mail Address] in Lines 12 and 24 with [EMail Address], and see what happens.

P.S. - Make sure you have a Reference set to the Microsoft Outlook ?X.X Object Library. Good Luck.
Feb 14 '12 #27

NeoPa
Expert Mod 15k+
P: 31,492
I'll leave this in your capables ADezii, though be warned - it may be a struggle getting accurate and reliable feedback, as most will go over their head at this stage. Maybe some time dealing with you will help them get to a stage where they can co-operate more fully with their questions.

Good luck to you both, and I hope you don't need too much of it (I think Hogue's already had a decent share of it, getting ADezii involved in the thread).
Feb 14 '12 #28

ADezii
Expert 5K+
P: 8,638
@NeoPa:
I think that you hit the nail in the head in that Outlook Automation Code for a Beginner may be a little too much to tackle. The part that bothers me is that, even with no knowledge whatsoever, the Code should work assuming the Field Names are exactly as stated, and a Reference exists to the Outlook Object Library. In any event, I feel as though it is worth another attempt. Thanks for all.
Feb 14 '12 #29

NeoPa
Expert Mod 15k+
P: 31,492
I understand ADezii, and wish you the best of luck. I suspect you may have to do more hand-holding on this one than usual, but I guess, from your comments, that you're up for that. I believe code modules and references are areas that Hogue needs explaining in some detail.
Feb 14 '12 #30

Post your reply

Sign in to post your reply or Sign up for a free account.