CODEWITHSUNDEEP
excelvbafilepath
Zinardy
Zinardy

Reputation: 11

VBA script saving file to correct path from one computer but not others

I have an automated Excel sheet with the following script being part of the functionality. When I click the button on the sheet, it saves exactly to the path and also emails it to the next person to view.

When run from another computer, it does not save it to the path. Note that I had the error handler removed. Before removing it, it would still email the attachment, but would not save to path from the other computers.

All users have full access to that folder. The code is in the worksheet. If I put it in a module and click the buttons nothing happens.

Debug shows module getting stuck at saveas filepath section.

Any help is greatly appreciated.

Thanks

Private Sub CommandButton1_Click()


If WorksheetFunction.CountA(Range("$A27:$O32")) = 0 Then
MsgBox "Order has no accessories. Please submit to Distribution.", vbOKOnly
Exit Sub
End If


Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
  Dim TheAddress As String
  Dim Attachment As String
  Dim FName           As String
  Dim FPath           As String
  Dim Fmail As String
  Dim FSection As String
  Dim Ddate As String

    FPath = "N:\Administration\Vehicle Orders"
    FName = Sheets("Company Vehicle Order Form").Range("A13").Text
    FSection = Sheets("Company Vehicle Order Form").Range("A25").Text
    Ddate = Date
    ActiveWorkbook.SaveAs Filename:= _
    FPath & "\" & FName & " " & FSection & Ddate, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

  Set objOutlook = CreateObject("Outlook.Application")


  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  TheAddress = "[email protected]"
  Attachment = FPath & "\" & FName & " " & FSection & Ddate & ".xlsm"

     With objOutlookMsg

        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo
        Set objAoutlookAttach = .Attachments.Add(Attachment)





        .Subject = "Company Vehicle Order Form"
        .Body = "Good day. Please review the attached Vehicle Order Form approve/ omit Accessories. If omitting, just delete the part and click on 'Submit to Distribution' button in the bottom BLUE section. " & _
        vbCrLf & vbCrLf & "Thank you.  "

        .Importance = olImportanceHigh  'High importance


          .Send
      End With

   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing

   ActiveWorkbook.Close
End Sub

Upvotes: 1

Views: 641

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

Change the Drive Letter Alias N in

FPath = "N:\Administration\Vehicle Orders"

to the UNC path.

So:

FPath = "\\myserver\mypath\Administration\Vehicle Orders"

Since Drive Letters are just aliases and can be set to any letter desired by any user, using it variables can produce errors. The UNC path will never change though (unless IT migrates servers, but you will definitely know if that happens before it happens :))

UNC Paths can be found in Windows Explorer:

enter image description here

Upvotes: 2

Related Questions