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
 
Incident Investigation Report 04122020 Troy Harkness
This is what it evals to with data in it.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
Solution
When I run the Filename = Range("AH2").Value & ".xlsx"
in the Immediate Window It returns False
 
Upvote 0
That means there is something wrong with your formula in AH2 since it doesn't produce a valid filename.
 
Upvote 0
Try changing the name of your variable, FileName is a VBA keyword & it could be that VBA is getting confused.
 
Upvote 0
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.
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
Glad we could help and thanks for letting us know.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
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