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

Problem with e-mailing from within Access

P: 99
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 ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. 'First, make sure that you have a Reference set to the
  4. 'Microsoft Outlook XX.X Object Library.
  5. 'Assuming you have a Table named tblEMailAddress, and it contains
  6. 'a Field to hold the E-Mail Addresses named [EAddr] :
  7.  
  8. Dim strEMail As String
  9. Dim oOutlook As Object
  10. Dim oMail As Object
  11. Dim strAddr As String
  12. Dim MyDB As DAO.Database
  13. Dim rstEMail As DAO.Recordset
  14.  
  15. Set oOutlook = CreateObject("Outlook.Application")
  16. Set oMail = oOutlook.CreateItem(0)
  17.  
  18. 'Retrieve all E-Mail Addressess in tblEMailAddress
  19. Set MyDB = CurrentDb
  20. Set rstEMail = MyDB.OpenRecordset("TEST_EMAIL_TBL", dbOpenSnapshot, dbOpenForwardOnly)
  21.  
  22. With rstEMail
  23.   Do While Not .EOF
  24.     'Build the Recipients String
  25.     strEMail = strEMail & ![EAddr] & ";"
  26.       .MoveNext
  27.   Loop
  28. End With
  29. '--------------------------------------------------
  30.  
  31. With oMail
  32.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  33.   .Body = "Test E-Mail to Multiple Recipients"
  34.   .Subject = "Yada, Yada, Yada"
  35.   .Send         'code halts here with error message
  36. End With
  37.  
  38. Set oMail = Nothing
  39. Set oOutlook = Nothing
  40.  
  41. rstEMail.Close
  42. Set rstEMail = Nothing
  43.  
  44. End Sub
  45.  
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?
Feb 28 '10 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,597
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.
Attached Files
File Type: zip EMail.zip (14.9 KB, 58 views)
Feb 28 '10 #2

P: 99
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!
Mar 1 '10 #3

ADezii
Expert 5K+
P: 8,597
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.
Mar 1 '10 #4

P: 99
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 = "j.brown265@btinternet.com"
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!)
Attached Files
File Type: zip EMail2.zip (26.9 KB, 44 views)
Mar 1 '10 #5

Post your reply

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