Problem with e-mailing from within Access

[julietbrown]

Sorry, me again! I picked up the following very useful-looking bit of code from this site somewhere a while back. Now having need of something like it I decided to try it out. I can fully understand what it's doing, and so expected that it would work. But it hit a problem. The code is ...

Private Sub Command0_Click()
 
'First, make sure that you have a Reference set to the
'Microsoft Outlook XX.X Object Library.
'Assuming you have a Table named tblEMailAddress, and it contains
'a Field to hold the E-Mail Addresses named [EAddr] :
 
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("TEST_EMAIL_TBL", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![EAddr] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Test E-Mail to Multiple Recipients"
  .Subject = "Yada, Yada, Yada"
   .Send         'code halts here with error message
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing
 
End Sub
 

I have set a reference to the Outlook 12.0 object library, as per the initial comment.

The code halts at the line .Send, with the message "Application defined or Object defined error" which, in this case, presumably means Outlook doesn't like it.

Can anyone help me out with this?

[ADezii]

I recognize the code quite well, and it works fine. It must be something very simple that you are missing, so I sent an Attachment to point you in the right direction. Simple fill in a valid E-Mail Address or two in the [EAddr] Field of tblEMailAddress, then fire away.

[julietbrown]

Thank you. The long delay in replying to your kind help has been caused by my attempts to find out what I did/didn't do which you didn't/did do!

The mystery is not resolved!
Your code works, my code is exactly the same as yours except my .Body and .Send are capitalised and yours are not. Thinking this could not possibly make a difference, I tried to change my .Send to .send (try anything once!) but Access won't let me, it just recapitalises it. ... And my code still doesn't work!!!

I have checked the References, and note that your code doesn't actually have a ref to the Outlook 12.0 OL. I tried matching my code's refs exactly to yours, but it made no difference. Still getting same message at same line.

If I run your code in your form, it works. If I paste it into a button on my form, it doesn't???

I'm generally having a bad Microsoft Day! I won't bore you with the rest!

[ADezii]

Two things, Juliet:

1. Replace .Send with .Display and see if if works. This will display the Outlook Window and you will manually have to Send.
2. Upload the Database with the code so we can have a look at it.
3. There is also a chance that Outlook may have some Security Mechanism in effect to prevent the automatic Sending of E-Mails.
4. As far as capitalization goes, I doubt very much as to whether or not it would make any difference.
5. You actually do not need a Reference to Outlok due to Late Binding.

[julietbrown]

Thank you.
1. Yes, that makes it work. I imagine this is most useful/reassuring way for user, anyway ... so they can see the message and really know it's gone!
2. I want to send you a little zipped db, but I'm not sure I've actually managed to attach it! It is strange, too.
3. I don't think this can be so, as it sent the message fine from your little form.
4. I'm sure it wouldn't make any difference! You know how it is when you are casting about for things to try!
5. I need to research the implications of late binding (generally)

Why (2) is strange ... I just added another button to your form with my code behind it. Now neither your button nor mine works unless I replace .send with .Display. Blurrrgh!

Meanwhile, another very strange thing maybe you can shed light on.

I have also tried using .SendObject to semi-automate the sending of an individual e-mail as per the following

Private Sub EBut_Click()
Dim jb As String
jb = "[e-mail]"
DoCmd.SendObject , , , jb, jb, jb, "Test message", "I am testing something", True
End Sub

This displays correctly ... a mail message with jb as the recipient, cc and bcc, the subject "Test Message" and body "I'm testing something" ... and the True makes it display the thing for editing before it goes off.

BUT, very strangely, it doesn't open the message in Outlook, but in my old Yahoo mail program that is still on the system but is not my default mail program any more (because, ironically enough, I couldn't get Access to talk to it!)