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

[ADezii]

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.

[Hogue]

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.

[NeoPa]

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.

[Hogue]

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

[NeoPa]

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).

[Hogue]

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.NameSpa ce
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.OpenRecord set("Donors", dbOpenSnapshot, dbOpenForwardOn ly)
16.
17. Set oLook = CreateObject("O utlook.Applicat ion")
18. Set olns = oLook.GetNamesp ace("MAPI")
19. Set oMail = oLook.CreateIte m(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

[ADezii]

1. Where are you executing the Code from?
2. Did you set a Reference to the Microsoft Outlook Object Library?

[NeoPa]

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.

[DataAnalyzer]

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 **

[Hogue]

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

[NeoPa]

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.

[ADezii]

@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.

[NeoPa]

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).

[ADezii]

@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.

[NeoPa]

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.