How to make my macro that creates an email work for other users?

I have an Access DB with a macro that will run a module that will email a report on the last step which works great, but only for me.  When another user tries to run the macro via a command button they get the following error:

“Microsoft Access 'C:\Users\(My username)\Desktop\QM_Tool.accdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.”

How do I fix this?  Here is the vba code for the email.
Option Compare Database

'------------------------------------------------------------
' RunDailyReport
'
'------------------------------------------------------------
Function RunDailyReport()
On Error GoTo RunDailyReport_Err

    DoCmd.SetWarnings False
    DoCmd.OpenForm "frmFOL", acNormal, "", "", , acNormal
    DoCmd.OpenForm "frmFSL", acNormal, "", "", , acNormal
    DoCmd.OpenForm "frmROM", acNormal, "", "", , acNormal
    DoCmd.SendObject acReport, "rptDailyReport", "XPSFormat(*.xps)", Forms!frmFOL![FOL Email] & "; " & Forms!frmFSL![FSL Email], Forms!frmROM![ROM Email] & "; " & "GroupMailBox@domain", "", "Daily Audit", "Here are the results for today's audit.  Please let me know if you have any questions." & vbCrLf & vbCrLf & "Team" & vbCrLf & "Company Name" & vbCrLf & "Email: GroupMailBox@domain", True, ""
    DoCmd.Close acForm, "frmROM"
    DoCmd.Close acForm, "frmFSL"
    DoCmd.Close acForm, "frmFOL"


RunDailyReport_Exit:
    Exit Function

RunDailyReport_Err:
    MsgBox Error$
    Resume RunDailyReport_Exit

End Function

Open in new window

kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NVITEnd-user supportCommented:
Looks like the user can't see the QM_Tool.accdb at your Desktop . Try moving the file to a folder you both can see, such as the Public folder.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If more than one person is using the database, then you should place a COPY of the database on each users desktop. Users should not share the SAME copy of your database, no more than they should share the SAME copy of Word or Excel.

Best practices would dictate that you split the database into a Backend (Tables only) and a Frontend (everything), and then place the BE on a shared network resource. Relink the FE to the BE, then make copies of the FE and distribute those to your users.
kbay808Author Commented:
@NewVillageIT – Your post got me thinking about my table locations.  Most of the tables were located on the sharepoint, but there was one table that was not and that was causing the issue.   I fixed it by creating a query that would create the table and added it to the autoexec macro.  Since the table is created by the user when the DB opens, there is no longer an issue.
NVITEnd-user supportCommented:
hi kbay808...
I'm glad you worked it out.
Aloha!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.