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

[Hogue]

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.

[NeoPa]

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.

[Hogue]

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

[NeoPa]

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.

[ADezii]

The Logic would be as follows, any questions feel free to ask.

Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strTO As String
Dim strMessageBody As String
Dim strSubject As String
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
'Do you even have E-Mail Addressess in the Donors Table?
'[E-Mail Address] cannot be NULL
If DCount("[E-Mail Address]", "Donors") = 0 Then Exit Sub
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Donors", dbOpenSnapshot, dbOpenForwardOnly)
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
 
'Build the Recipient List
With rst
  Do While Not .EOF
    strTO = strTO & ![E-Mail Address] & ";"
      .MoveNext
  Loop
End With
 
'Remove Trailing ';'
strTO = Left$(strTO, Len(strTO) - 1)
 
'******************************* USER DEFINED SECTION ********************************
strMessageBody = "Message to ALL Recipients       "
strSubject = "Test Project for E-Mailing Multiple Recipients in Outlook"
'*************************************************************************************
 
With oMail
 .To = strTO
 .Body = strMessageBody
 .Subject = strSubject
   '.Display
     .Send      'Immediately Sends the E-Mail without displaying Outlook
End With
 
Set oMail = Nothing
Set oLook = Nothing
 
rst.Close
Set rst = Nothing

[Hogue]

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

[Hogue]

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?

[ADezii]

What context do you need to run it in?

[Hogue]

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

[NeoPa]

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.

[ADezii]

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.

[Hogue]

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

[Hogue]

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.

[ADezii]

@Hogue:
Why do you insist on running this Code from a Macro?

[Hogue]

It doesn't have to be a macro, anything just to make the code run.

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

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