Run-time error for different users

[Wes4444]

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:

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.

 

[jmacleary]

Does the user have write access to the folder you are using?

 

[Wes4444]

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 the saveas puts the temporary file in whatever the current folder is, so the user must have rights to that folder.

 

[Wes4444]

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

 

[jmacleary]

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.

 

[Wes4444]

Ok I'll give that a go and see what happens, cheers.

 

[Norie]

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.