Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-03-2015, 04:00 AM   #1
flscotty1
Newly Registered User
 
Join Date: Oct 2014
Location: Michigan
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
flscotty1 is on a distinguished road
SendObject is not available now

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 by flscotty1; 06-03-2015 at 04:50 AM.
flscotty1 is offline   Reply With Quote
Old 06-03-2015, 05:50 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: SendObject is not available now

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-03-2015, 06:33 AM   #3
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: SendObject is not available now

Quote:
Originally Posted by flscotty1 View Post
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).

vbaInet is offline   Reply With Quote
Old 06-04-2015, 04:40 AM   #4
flscotty1
Newly Registered User
 
Join Date: Oct 2014
Location: Michigan
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
flscotty1 is on a distinguished road
Re: SendObject is not available now

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.
flscotty1 is offline   Reply With Quote
Old 06-04-2015, 04:50 AM   #5
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: SendObject is not available now

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?
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Last edited by spikepl; 06-04-2015 at 05:14 AM.
spikepl is offline   Reply With Quote
Old 06-04-2015, 05:24 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,024
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: SendObject is not available now

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

Quote:
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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-04-2015, 05:30 AM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: SendObject is not available now

SendObject does not need references to Outlook

__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 06-04-2015, 05:44 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,024
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: SendObject is not available now

it was just a thought I don't use it myself. So I guess this would work for say Lotus Notes, Thunderbird etc?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-04-2015, 07:03 AM   #9
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: SendObject is not available now

Quote:
Originally Posted by flscotty1 View Post
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.
vbaInet is offline   Reply With Quote
Old 06-04-2015, 07:09 AM   #10
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: SendObject is not available now

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.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 06-04-2015, 07:19 AM   #11
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: SendObject is not available now

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.
vbaInet is offline   Reply With Quote
Old 06-05-2015, 01:07 PM   #12
flscotty1
Newly Registered User
 
Join Date: Oct 2014
Location: Michigan
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
flscotty1 is on a distinguished road
Re: SendObject is not available now

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!
flscotty1 is offline   Reply With Quote
Old 06-05-2015, 02:37 PM   #13
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: SendObject is not available now

Good to hear flscotty1!

Quote:
Originally Posted by flscotty1 View Post
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.
vbaInet is offline   Reply With Quote
Old 06-10-2015, 05:56 AM   #14
flscotty1
Newly Registered User
 
Join Date: Oct 2014
Location: Michigan
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
flscotty1 is on a distinguished road
Smile Re: SendObject is not available now

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.
flscotty1 is offline   Reply With Quote
Old 06-10-2015, 06:07 AM   #15
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: SendObject is not available now

Good luck with your project!

vbaInet is offline   Reply With Quote
Reply

Tags
sendobject

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SendObject Help please! Kroghr Modules & VBA 2 03-11-2009 06:07 AM
Sendobject access_novice1234567 Modules & VBA 1 02-12-2008 05:52 AM
SendObject mlr0911 Modules & VBA 7 09-12-2007 12:36 PM
SendObject isn't available now Crampton Forms 5 04-17-2007 05:17 AM
SendObject aziz rasul Modules & VBA 2 02-15-2001 01:49 AM




All times are GMT -8. The time now is 01:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World