Trouble saving to a Network location with VBA

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
Back story - Our Safety team has an Incident Report that is to be used by all Supervisors and Leads for anyone that has gotten hurt. To make this easier and to make sure that everyone gets the information I have created a macro to do a few things.
1. Save a copy of the Incident Report as an .xlsx file to a specific directory that the file name is created from a cell range in the spreadsheet.
2. Create the Email to be sent to all of the people that need to get a copy of these. The Subject line will have the Persons name that was injured as well as the Incident date.
3. Close the workbook
I have changed this multiple times trying to get this correct so any help is VERY appreciated.

Current Code

VBA Code:
Sub IncidentReport()
'Save to File Destination
    
    'Dim Path As String
    'Dim Filename1 As String
    
    
    'Application.DisplayAlerts = False
    'Path = "C:\Users\t.harkness\Desktop\" '"\\network.local\dfs\Server\UserData\SAFETY\Incident Reports\"
    'Filename1 = Range("AH2").Value & ".xlsx"
    'ActiveWorkbook.SaveAs Path & Filename1, xlOpenXMLWorkbook
    'Application.DisplayAlerts = True


'Troys Code From here to Send out Email
Dim FileName As String
Dim Path As String


Application.DisplayAlerts = False
'Path = "\\network.local\dfs\Server\UserData\SAFETY\Incident Reports\TEST Folder\"
FileName = Range("AH2").Value & ".xlsx"
ActiveWorkbook.SaveAs FileName:= _
    "Z:\SAFETY\Incident Reports\" & FileName, FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True




'Send out Email
    Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = "InjuryReport@millerbaking.com"
        .CC = ""
        .BCC = ""
        .Subject = "Incident Investigation Report" & " " & Range("H15").Value & " " & Range("F9").Value
        .Body = "Attached is an incident investigation report."
        .Attachments.Add ActiveWorkbook.FullName
        
        .Send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    


'Close workbook without saving
    Application.DisplayAlerts = False
    Application.Quit


End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
I forgot to post the error which is breaking on the ActiveWorkbook.SaveAs FileName:= _ Line
Run-Time error '1004'
Method 'SaveAs' of object'_Workbook' failed
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
What exactly is going wrong or not as expected?
Are you getting any error messages?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Are you sure you do have write permission on the "Z:\SAFETY\Incident Reports\" folder?
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15

ADVERTISEMENT

Yes. I even went into the folder and gave myself Full Control on the Parent Folder
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
What is in AH2 when you get the error?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Okay, in cases like this, the most obvious causes are the following:
- no write permission on / in that disk and / or folder;
- a file with an identical name already exists but has a read-only file attribute;
- the file name contains invalid characters;
Occasionally it can happen on network drives that the depth of the folder structure results in path names that are too long, at least longer than 260 characters, but that seems not the case here.
I would advise checking if cell AH2 actually contains a valid file name (or in case of a formula evaluates to a valid file name).
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
AH2 is a concatenation of the Injured persons name and the Date. It looks like this.

=K1&" "&TEXT(F9, "ddmmyyyy")&" "&H15
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
K1 = Persons name
F9 = Date
H15 = Incident Investigation Report (all text)
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Okay, but this is just an explanation, NOT an example of the text result after evaluation of the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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