Run-time error for different users

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
Hi,

I added a Macro to my workbook which, when run, takes the current worksheet, saves it as a temporary file, emails it out and then deletes the file. It works fine for me but when another user tries to run it, they get the error message: Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed. When I try to debug the issue the following line is highlighted:

Code:
Sub Email_Button()
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

   Dim oApp As Object
   Dim oMail As Object
   Dim LWorkbook As Workbook
   Dim LFileName As String

   'Turn off screen updating
   Application.ScreenUpdating = False

   'Copy the active worksheet and save to a temporary workbook
   ActiveSheet.Copy
   Set LWorkbook = ActiveWorkbook

   'Create a temporary file in your current directory that uses the name
   ' of the sheet as the filename
   LFileName = LWorkbook.Worksheets(1).Name
   On Error Resume Next
   'Delete the file if it already exists
   Kill LFileName
   On Error GoTo 0
   'Save temporary file
   [COLOR=#ff0000]LWorkbook.SaveAs Filename:=LFileName[/COLOR]

   'Create an Outlook object and new mail message
   Set oApp = CreateObject("Outlook.Application")
   Set oMail = oApp.CreateItem(0)

   With oMail
   .Display
   End With
   Signature = oMail.body
   With oMail
      .To = "..."
       .Subject = "Measurement Report - " & Format(Date, "dd/mm/yyyy")
      .Attachments.Add LWorkbook.FullName
      .body = "Hi All," & vbNewLine & vbNewLine & _
                         "Please see attached measurement report for " & Format(Date, "dd/mm/yyyy.") & vbNewLine & vbNewLine & _
                         "Kind Regards," & Signature
      .Send
   End With

   'Delete the temporary file and close temporary Workbook
   LWorkbook.ChangeFileAccess Mode:=xlReadOnly
   Kill LWorkbook.FullName
   LWorkbook.Close SaveChanges:=False

   'Turn back on screen updating
   Application.ScreenUpdating = True
   Set oMail = Nothing
   Set oApp = Nothing

End Sub

Is this just an issue for the user and their preferences etc. or is there an issue in the coding?

Any help will be much appreciated.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Does the user have write access to the folder you are using?
 

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
Thanks for your reply. I haven't used an folder structure in the macro that requires any access have I? The macro saves the worksheet as a temproary file, attaches it to the email and then deletes it so there isn't any folder structures needed? Correct me if I am wrong of course.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Well the saveas puts the temporary file in whatever the current folder is, so the user must have rights to that folder.
 

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21

ADVERTISEMENT

Ah ok makes sense. But yes they do have access to the folder.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
OK a bit stumped then. You could put a breakpoint in on the saveas, then kill the macro and try doing the saveas manually to see if you get more info.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
Wes4444

You should specify the folder and the file format, the folder because you can't guarantee what the current directory is the file format because VBA sometimes doesn't like it if you don't use the correct format.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,586
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top