Results 1 to 10 of 10

Macro works on desktop but not Network environment

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Macro works on desktop but not Network environment

    Hello, I recently had help from Logit getting a macro created that could save an Excel file - a daily shift attendance sheet - to a specified location on my desktop under a specified name as well as compose an email with the same file attached with pre-populated Subject, Body, and email recipients. That can be found here: https://www.excelforum.com/excel-pro...-and-exit.html. I presented this to the managers here at work and they all loved the idea because it would eliminate needless emails and phone calls when someone didn't receive the sheet that was supposed to and so they gave me to go ahead to get it implemented in the actual network drive where the attendance sheet templates are kept. Basically, they would be saved into a separate folder within that same network drive.Here is the macro for the desktop location to get an idea behind the coding:

    Sub savesheet()
    Dim Name As String
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ActiveSheet.Copy
    
        Name = Environ("UserProfile") & "\Desktop\attendance sheets\3rd Shift Grocery&Dairy\" & _
            Format(Now(), "mmddyy") & " " & "Grocery & Dairy 3rd Shift ABSENTEE BLANK (1ST SHIFT)" & ".xlsm"
        
        ActiveSheet.SaveAs Filename:=Name, FileFormat:=52
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        EmailWBAttached
        
    End Sub
    
    Sub EmailWBAttached()
    
    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 = "emailtest@test.com,"        '<-- enter email addresses here. Multiple emails separate by comma
            .CC = ""
            .BCC = ""
            .Subject = "3rd Shift Attendance: " & Format(Now(), "mm.dd.yy")  '<-- enter subject here
            .Body = "Attached is the attendance sheet or revision to 3rd Shift Grocery & Dairy."      '<-- enter message body here
            
            .Attachments.Add Application.ActiveWorkbook.FullName
            
            '.Send 'or use .Display
            .Display
        End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
        
    End Sub
    This one is working fine. I had pasted this same macro in the file that is located in the network drive. I changed it to have the file pathway leading to the folder that I would need it saved in there. The coding for that looks like this:

    Sub savesheet()
    Dim Name As String
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ActiveSheet.Copy
    
        Name = Environ("UserProfile") & "\\servername\Data\DNS\Group\Warehouse\Management\Absentee tracking\UNFI F21\Perishable 1st\" & _
            Format(Now(), "mmddyy") & " " & "Perishable 1st Shift ABSENTEE BLANK (1ST SHIFT)" & ".xlsm"
        
        ActiveSheet.SaveAs Filename:=Name, FileFormat:=52
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        EmailWBAttached
        
    End Sub
    
    Sub EmailWBAttached()
    
    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 = "emailtest@test.com,"        '<-- enter email addresses here. Multiple emails separate by comma
            .CC = ""
            .BCC = ""
            .Subject = "1st Shift Attendance: " & Format(Now(), "mm.dd.yy")  '<-- enter subject here
            .Body = "Attached is the attendance sheet or revision to 1st Shift Perishable."      '<-- enter message body here
            
            .Attachments.Add Application.ActiveWorkbook.FullName
            
            '.Send 'or use .Display
            .Display
        End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
        
    End Sub
    I didn't realize that a network location would operate differently and that this code would not be valid. It was composing the email just fine, but not saving the file where I wanted it to go. I'm not even sure where it saved it if it even did - I just know it wasn't where intended. Is it completely different coding needed here in the case of network drives? Is something like this actually even possible?
    Last edited by PitchNinja; 06-16-2021 at 02:38 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] macro to delete all CSV files on my desktop desktop folders and and its subfolders
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-19-2017, 03:42 PM
  2. Replies: 4
    Last Post: 04-10-2014, 12:11 PM
  3. Add-in Installation in a Network Environment
    By Fowmy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2012, 10:02 AM
  4. OnAction macro assigment through a macro in network environment behaves strange
    By kbx17 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2012, 02:22 PM
  5. Use an icon from a network drive for a desktop shortcut
    By jhize in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2007, 09:22 AM
  6. Workbooks are getting damaged in network environment
    By Mikeyhend in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-12-2006, 07:52 AM
  7. [SOLVED] Desktop file sharing:network drive
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1