Trouble saving to a Network location with VBA

troyh68

New Member
Joined
Nov 14, 2016
Messages
24
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
What exactly is going wrong or not as expected?
Are you getting any error messages?
 
Upvote 0
Are you sure you do have write permission on the "Z:\SAFETY\Incident Reports\" folder?
 
Upvote 0
Yes. I even went into the folder and gave myself Full Control on the Parent Folder
 
Upvote 0
What is in AH2 when you get the error?
 
Upvote 0
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).
 
Upvote 0
AH2 is a concatenation of the Injured persons name and the Date. It looks like this.

=K1&" "&TEXT(F9, "ddmmyyyy")&" "&H15
 
Upvote 0
Okay, but this is just an explanation, NOT an example of the text result after evaluation of the formula.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top