Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems with DoCmd.SendObject!! 1

Status
Not open for further replies.

Eightball3

Programmer
Nov 23, 2002
138
US
I've converted a Access 97 database over to Access 2000. This code used to work. Can anyone find the reason it doesn't?

Dim Message As String
Dim Subject As String
Dim num As Single
Dim records As Single
Dim x As Single

Message = "This is a test message."
Subject = "Test Date " + Str(Date)

num = DCount("*", "poc_table")
DoCmd.OpenForm "poc_form", acNormal, , , acFormEdit, acIcon
For x = 1 To num
DoCmd.GoToRecord acDataForm, "poc_form", acGoTo, x
records = DCount("*", "recall_e_mail")

If Forms![poc_form]![E_Mail_Recall] = "Y" Then
If records > 0 Then

If IsNull(Forms![poc_form]![E_Mail_Address]) Then
DoCmd.OpenReport "Recall_E_Mail", A_NORMAL 'Print Recall on Paper
Else
DoCmd.SendObject acSendReport, "Recall_E_Mail", acFormatRTF, Forms![poc_form]![E_Mail_Address], , , Subject, Message, False
End If
End If
Else
If records > 0 Then DoCmd.OpenReport "Recall_E_Mail", A_NORMAL
End If

Next x
DoCmd.Close acForm, "poc_form"

This used to work in Access 97 but now it emails out the first message but nothing else. After the first one it just gives me an hour glass like its doing something...but no emails.
Anyone have any ideas?
 
More Information....

If I quit Access. Run Access again. Press the button that activates the above code. Only the first person in the table gets emailed. When finished, if I press the button again, no emails go out. If I quit Access and start over, I get the same results. Anyone have any ideas?
 
Do you need to add a library reference since converting from '97?
 
I don't know. Here is what I have for References:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ADO Ext. 2.5 for DDL and Security
OLE Automation
Mocrosoft Visual Basic for Application Extensibility 5.3
 
Sorry, but as you have both DAO and ADO checked, I'm out of guesses.
 
This is just a guess, but try adding a reference to the Microsoft ActiveX Data Objects 2.5 Library.

The Microsoft ADO Ext. 2.5 for DDL and Security is an extension library, not the main library for ADO.

HTH
Lightning
 
Well I added the Microsoft ActiveX Data Objects 2.1 Library. I exited access. Started up again and I get the same problem. Only the first email goes out.
 
I mean Data Objects 2.5 Library...What about the order of the references? Will that make a difference?
 
It could. Make sure that the ADO 2.5 library is listed before the ADO extension library.

Looking at my existing references, the order is

VBA
Microsoft Access 10.0 Object Library (I'm using Access XP)
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library

If that doesn't work, I'm fresh out of ideas.

HTH
Lightning
 
Thanks Lightning for you help. It looks like they are in the correct order. Any other ideas?
 
Long shot. Try removing the references to ADO completely. if it worked in Access 97 with DAO, removing the references to ADO may remove a conflict between DAO and ADO.

HTH
Lightning
 
Well...I'm really stumped! I've reduced the code down to this just trying to find the problem.

Private Sub Command97_Click()
Dim Message As String
Dim Subject As String
Dim PersonTo As String
Dim num As Single
Dim x As Single
Dim z As Single
Message = "System Test. Do not Reply."
Subject = "System Test"
For x = 1 To 5
Me![num] = x 'field on form to display progress
If x = 1 Then PersonTo = "167019" 'alias in Outlook
If x = 2 Then PersonTo = "141224"
If x = 3 Then PersonTo = "144000"
If x = 4 Then PersonTo = "138357"
If x = 5 Then PersonTo = "143285"
DoCmd.SendObject , , , PersonTo, , , Subject, Message, False
For z = 1 To 10000000 'Delay to see what's happening
Next z
Me.Repaint
Next x
Me![num] = 0
End Sub

If I close access and re-open it, I can press this button as many times as I like and it works. If I enter design mode on the form, make a change(add a remark to the code), compile and save the form, re-open the form, it won't work. I press the button, and the counter counts but no emails go out. It's like the DoCmd.SendObject line isn't even there!! Could this be an Outlook problem? I'm on a network with virus protection software, could this be a problem?
 
Basic question: Have you stepped through the code? Do all your variables (particularly X) return the expected values at the appropriate times?
 
Well I found the problem. This is a documented bug in Access 2000. SP-3 is supposed to fix the problem. I haven't tried it yet but I will post my results. Thanks for all the suggestions.
 
SP-3 I believe will make your OUtlook program stop and prompt you to allow sending from an application.

There is a third party dll that works pretty well as long as you have outlook open to send the message without prompts. I found it in these forums and applied to my pc and it works well.
 
In any case, you may have trouble with Sendobject no matter what. We ran into this about a year ago, and spent the better part of a week pulling our hair out before we finally found the bug 'confession' tucked out of sight somewhere on the MS site. Upgrading to whatever 'fix' was supposed to take care of it at the time made no discernable difference - I don't recall at the moment which fix it was - but it definitely didn't help. :-(
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top