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.