asked on

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

ASKER CERTIFIED SOLUTION

NVIT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

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.

kbay808

ASKER

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

NVIT

hi kbay808...
I'm glad you worked it out.
Aloha!