SendObject is not available now (1 Viewer)

flscotty1

New member
Local time
Today, 09:41
Joined
Oct 20, 2014
Messages
6
Hi, my name is Scott, and this is my first request for help in this, or any forum.

I've been using Access for many years, but have only recently begun using VBA. This forum has been a great source of information for me. I have found answers to almost every question I've had here. However, I haven't been able to find anything that helps me with my current issue.

I have written an Access 2013 database that produces invoices for about 100 customers each month. The invoice data is compiled in a table (tbl Invoices) which generally has around 150 records.

The database is in a shared Dropbox folder. There are three users, but only one user is in the database at a time.

A report (Current Invoices) creates an invoice for each client in the table. Each invoice can be emailed separately by clicking a button on the invoice as it's being reviewed.

The code I've written is below. It has been working fine since I wrote it a couple months ago. It creates a client-specific version of the invoice then uses DOCMD.SENDOBJECT to send a PDF version of that invoice to the client. The body of the email varies based on whether or not the client pays via EFT, or not.
Finally, it updates the record in tbl Invoices to let the user know the invoice has been sent.

Yesterday, the process stopped working for all three users. When I remove the error handling, we get an error 2046 - 'The command or action "SendObject' isn't available now.'

I have not been able to find any reason for the command to stop working.

I installed Access Runtime 2013 on my computer since the last time it worked. None of the other users made any changes to their computer.

I would welcome any help or advice.

Code:
Function EmailInvoice(ClientID, EFT As Boolean, Address As String)
     Dim Body As String
    Dim Subject As String
     Subject = "Invoice from Company Name"
        
    On Error GoTo Error_Happened
    
    If EFT = True Then
        Body = "Please see the attached invoice. Your account will be debited the invoiced amount on or around the 5th of the month." & _
            Chr(11) & Chr(11) & "Please email with any questions." & _
            Chr(11) & Chr(11) & "Regards," & _
            Chr(11) & "Company Name"
        
        Else
        Body = "Please mail payment to the address on the attached invoice." & _
            Chr(11) & Chr(11) & "Please email with any questions." & _
            Chr(11) & Chr(11) & "Regards," & _
            Chr(11) & "Company Name"
    End If
    
    DoCmd.OpenReport "Current Invoices", acViewPreview, , "ClientID = " & ClientID
    DoCmd.SendObject acSendReport, "Current Invoices", acFormatPDF, Address, , , Subject, Body, True

     ' Sets Sent field to equal "Sent"
    DoCmd.RunSQL "UPDATE [tbl Invoices] " & _
        "SET [tbl Invoices].Sent = ""Sent"" " & _
        "WHERE ((([tbl Invoices].ClientID)=" & ClientID & "));"

     DoCmd.Requery Reports![tbl Review Invoices]
    
Error_Happened:
    
    DoCmd.Close acReport, "Current Invoices", acSaveNo
        
End Function
 
Last edited:

Minty

AWF VIP
Local time
Today, 16:41
Joined
Jul 26, 2013
Messages
10,354
This sounds like either corruption (very weird though if all three users have their own front end?) or a missing reference.

In the Debug window click on tools and see if it says 'Missing' against any of the ticked listed extensions/dll's
 

vbaInet

AWF VIP
Local time
Today, 16:41
Joined
Jan 22, 2010
Messages
26,374
The database is in a shared Dropbox folder. There are three users, but only one user is in the database at a time.
Actually, they don't have their own Front End, it's shared.

flscotty1, you can't guarantee that there would only ever be one user logged in at any given time even if they work different shifts. There's the question of whether the user has logged out or not. Besides that, the main problem with your setup is that when the db is run, the db needs to find an Access runtime or full version on the host machine, which then reports back to the db on the share and this process continues. This back and forth 'handshake' isn't an ideal setup. This is one of the main causes of your "possibly" corrupted db.

You will need to first split your db into a back end (tables) and give each of your users their own front end (forms, reports).
 

flscotty1

New member
Local time
Today, 09:41
Joined
Oct 20, 2014
Messages
6
Thanks for the quick responses! I really appreciate it.

I have found that SendObject doesn't work on any database on my computer. I even created a couple new databases specifically to test the capability. I got the same error every time.
It's odd that one command doesn't work in any database on my computer, or on any computer using a database from my computer.

Minty - I checked the references. None were missing.

VbaInet - I don't think multiple users in the database are contributing to the issue. The database is in a Dropbox folder. Dropbox - the way we have it set up - is not very good for collaborative projects. If more than one person is in any file at a time, Dropbox creates a separate 'conflicted copy' of the file labeled with the computer name of the second user. We haven't had a conflicted copy of the database for several months. The two people that use the database are very good at ensuring one has closed the database before the other opens it. (Poor design on my part, I now realize).

With that said, database splitting is clearly a 'best practice' I should be using in my design if there is a chance of having multiple users. I will work on splitting the database to prevent future problems.
 

spikepl

Eledittingent Beliped
Local time
Today, 17:41
Joined
Nov 3, 2010
Messages
6,144
If SendObject does not work ... what mail client have you got ? Does that work OK? Cause SendObject uses the installed mail client.

Also - BANISH "DOES NOT WORK" - the most useless phrase ever.

What happens when you use SendObject in the newly created db's?

That it happened for all the users simultaneously implies a common factor. Do you share work environment? Permissions/privileges? Mailserver?
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Feb 19, 2013
Messages
16,553
just a thought - if you are using 2013 runtime, what version was the db developed in? If an earlier version it may not have the right references, so perhaps is looking for the wrong version of outlook for example.

might be an idea to use CDO rather than sendobject

With that said, database splitting is clearly a 'best practice' I should be using in my design if there is a chance of having multiple users
I'm not sure that splitting will work in this situation - when the user connects to the back end, they will still create their own 'personalised copy' of the back end when they connect to it.

Have you considered using remote access to a spare pc which hosts your db? still only one user at a time, but probably much faster performance. Look at providers like teamviewer, Jumpdesktop, logmein.
 

spikepl

Eledittingent Beliped
Local time
Today, 17:41
Joined
Nov 3, 2010
Messages
6,144
SendObject does not need references to Outlook
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Feb 19, 2013
Messages
16,553
it was just a thought:D I don't use it myself. So I guess this would work for say Lotus Notes, Thunderbird etc?
 

vbaInet

AWF VIP
Local time
Today, 16:41
Joined
Jan 22, 2010
Messages
26,374
VbaInet - I don't think multiple users in the database are contributing to the issue. The database is in a Dropbox folder. Dropbox - the way we have it set up - is not very good for collaborative projects. If more than one person is in any file at a time, Dropbox creates a separate 'conflicted copy' of the file labeled with the computer name of the second user. We haven't had a conflicted copy of the database for several months.
Splitting the db and giving each user their FE will definitely help alleviate most problems and will reduce the number of handshakes Access needs to do and with a shared cloud Dropbox drive the number of handshakes is increased.

SendObject first converts the report to an appropriate format, saves it somewhere on the host machine, creates a message item, attaches the file and sends the message using the mail service on the host machine. Because the FE (but in your case the entire db) is not on the host machine there will be a lot of back and forth handshakes via the cloud. Whereas on a split fe/be the 'only' handshake will be sending the data via the cloud.

If you want something on the cloud then you might want to look into Sharepoint or upscale to SQL Server.
 

spikepl

Eledittingent Beliped
Local time
Today, 17:41
Joined
Nov 3, 2010
Messages
6,144
vbaInet - not quite. The shaking of hands or other bodily parts is performed just like with a normal local or networked Access file. The cloud part of this enterprise is taken care of by the Dropbox application.

The installed Access application on the user machine is faced with a local .accdb file, which then is copied into (or from) the cloud when appropriate by the Dropbox app.
 

vbaInet

AWF VIP
Local time
Today, 16:41
Joined
Jan 22, 2010
Messages
26,374
Now that would depend on the cloud architecture. In a cloud environment that includes Office the cloud service will attempt to support the handling/servicing of the db requests. In an environment where Office isn't included, the host machine handles everything via the cloud.

By handshake I mean a request for service. Opening a form is a request, as is SendObject. Each time a service is requested, the request is sent from the host machine to the share for resolution, the share then sends back an answer for the host machine to carry out the operation. Whereas in a split fe/be (with each user having his/her own fe) the host machine resolves any non-data requests.
 

flscotty1

New member
Local time
Today, 09:41
Joined
Oct 20, 2014
Messages
6
Thank you for your help, Everyone! I appear to have solved the problem.

I noticed that the access wizards were not working this afternoon (I used a query wizard to start the structure of an SQL statement). That made me think that my copy of Access had somehow become corrupted.

Using the Windows Add/Remove programs feature, I repaired Runtime 2013 and did a quick repair of my copy of Office 365. After that, SendObject commands worked as they had in the past.

Thanks again for all the help!
 

vbaInet

AWF VIP
Local time
Today, 16:41
Joined
Jan 22, 2010
Messages
26,374
Good to hear flscotty1!

That made me think that my copy of Access had somehow become corrupted.
Yes possibly corruption, most likely due to the setup. There's a less likely chance of your db becoming corrupt if it's split so I hope that you take this advice on board. You don't want data to get corrupt either.
 

flscotty1

New member
Local time
Today, 09:41
Joined
Oct 20, 2014
Messages
6
vbaInet...
Yes. I am indeed taking your advice to split the databases I've built. The first will be the database I built for my client (referred to in this thread).

Once I've finished that, I'll work on the database I use to make my living. That will be part of a larger project to move many of the tables to the web. I will split the database before I move the tables to the web.

Thanks again for all your help.
 

Users who are viewing this thread

Top Bottom