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
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
Incident Investigation Report 04122020 Troy Harkness
This is what it evals to with data in it.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Add the red line to your code and run in with an open Immediate Window in the VBE (press Ctrl G). There could be a vbLineFeed character within the filename that you're not aware of...

Rich (BB code):
    Application.DisplayAlerts = False
    'Path = "\\network.local\dfs\Server\UserData\SAFETY\Incident Reports\TEST Folder\"
    Filename = Range("AH2").Value & ".xlsx"
Debug.Print Filename
    ActiveWorkbook.SaveAs Filename:="Z:\SAFETY\Incident Reports\" & Filename, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = True
 
Solution

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
When I run the Filename = Range("AH2").Value & ".xlsx"
in the Immediate Window It returns False
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
That means there is something wrong with your formula in AH2 since it doesn't produce a valid filename.
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15

ADVERTISEMENT

I will re-work that file name.

Thank you
 

GWteB

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try changing the name of your variable, FileName is a VBA keyword & it could be that VBA is getting confused.
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
15
I have figured out the issue and thank you all for your help. It ended up being I had miss typed the file path.

Again - Thank you all.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Glad we could help and thanks for letting us know.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,976
Members
416,003
Latest member
indyman

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