VBA Code to Save to a network location

[mmorris1965]

Good afternoon. I have a workbook that contains the code below. It works perfectly; however, I would like to alter the code to save the file to a network location with a new name derived from the text found in cell B8 on sheet1 and the current date and time rather than the users local files. I've tried several modifications to the code; however nothing seems to work.


Dim answer As String

answer = MsgBox("Save Form to Shared Folder and Notify Team Representatives?", vbYesNo, "Form Submission")
If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Jane Doe")
newmsg.Recipients.Add ("janedoe@xxx.org")
'add subject
newmsg.Subject = "A New Form Has Been Uploaded for Review"
'add body
newmsg.Body = "Form has been saved to Shared Network Folder for review and approval."
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "The Form has been saved and Team Members have been notified.", , "Form Submission Confirmation"
End If
'save the document
'Me.Worksheets.Save

End Sub

Thank you in advance for your assistance

 

[friel300]

Hi mmorris1965,

Give this a go

Sub g()

Dim answer As String


    answer = MsgBox("Save Form to Shared Folder and Notify Team Representatives?", vbYesNo, "Form Submission")
    If answer = vbNo Then Cancel = True
    If answer = vbYes Then
        'open outlook type stuff
        Set OutlookApp = CreateObject("Outlook.Application")
        Set OlObjects = OutlookApp.GetNamespace("MAPI")
        Set newmsg = OutlookApp.CreateItem(olMailItem)
        'add recipients
        'newmsg.Recipients.Add ("Jane Doe")
        newmsg.Recipients.Add ("janedoe@xxx.org")
        'add subject
        newmsg.Subject = "A New Form Has Been Uploaded for Review"
        'add body
        newmsg.Body = "Form has been saved to Shared Network Folder for review and approval."
        newmsg.Display 'display
        newmsg.Send 'send message
        'give conformation of sent message
        MsgBox "The Form has been saved and Team Members have been notified.", , "Form Submission Confirmation"
    End If
'    save the document
    ActiveWorkbook.SaveAs "[B]Your Network Path here[/B]" & Range("B8").Value & " - " & Replace(Date, "/", ".") & " - " & Replace(Format(Now(), "hh:mm"), ":", "") & ".xlsm"


End Sub

it will give you a file name somthing like this

TEST - 16.12.2016 - 0955.xlsm

Hope this helps

 

[mmorris1965]

friel300 thank you for your reply. Unfortunately, it appears that the later part of the code is generating a "Runtime error 1004". Any suggestions as to what I may need to do to resolve?

 

[friel300]

Sorry Mmorris, I could have explained that a little better.

At the bottom there is the line

 ActiveWorkbook.SaveAs "[B]Your Network Path here[/B]" & Range("B8").Value & " - " & Replace(Date, "/", ".") & " - " & Replace(Format(Now(), "hh:mm"), ":", "") & ".xlsm"

you need to change the "Your Network Path here" to your desired save location, i.e. "C:".
Make sure it’s a valid location, with a \ as the last character.