Vba code doesnt work on shared/group/network Drive

  • I have a statistical workbook, within which is a particular sheet, that I need to be able to copy and email in pdf format only. The workbook has been in use for some time and works perfectly from my desktop. However, it has to now be moved to a shared drive, as others in the office need access.

    Subsequently I revised the code to include definition and path "Mypath" - group drive is B and the workbook is with a folder called "Stat" - I have bolded the changes in code.
    Now, it wont work - every time coming up with "Run-time error ' -2147024773 (8007007b)': Document not saved.
    When I debug - it goes to
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    As stated, works perfectly from desktop but I cant get the new path working at all & its driving me mad. So any help would be greatly appreciated :)
    Many thanks.

    Code used

    Private Sub CommandButton3_Click()
    Dim IsCreated As Boolean
    Dim i As Long
    Dim PdfFile As String, Title As String, Mypath As String
    Dim OutlApp As Object

    Title = "Stat Report"

    PdfFile = ActiveWorkbook.FullName
    i = InStrRev(PdfFile, ".")
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
    Mypath = "B:\Stat"

    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    On Error Resume Next
    Set OutlApp = GetObject(, "Outlook.Application")
    If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
    End If
    OutlApp.Visible = True
    On Error GoTo 0

    With OutlApp.CreateItem(0)

    .Subject = Title

    .Body = "Hi," & vbLf & vbLf _
    & "The report is attached in PDF format." & vbLf & vbLf _
    & ActiveSheet.Range("a5").Value
    ' & "Regards," & vbLf _
    ' & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

    On Error Resume Next

    Application.Visible = True
    If Err Then
    MsgBox "E-mail was not sent", vbExclamation
    MsgBox "E-mail ready to send", vbInformation
    End If
    On Error GoTo 0

    End With

    Kill PdfFile

    'If IsCreated Then OutlApp.Quit

    Set OutlApp = Nothing

    End Sub

  • Try changing mypath to B:\Stat\

    Also, if other users in this shared location have this location mapped to a different drive than "B", your macro will fail. You should change mypath from B:\Srat\ to the actual address. Example: \\test.com\usrpub1\Stat\

  • Many thanks Max1616, I tried both suggestions but didn’t work. On the off chance I moved another workbook to the group drive, (that exports a sheet to excel format and then emails).

    The excel Extract vba appears to work fine, could the pdf side be the problem? Again, advice would be appreciated as PDF is def the preferred option.

  • Found a workaround in that pdf file doesn’t try to automatically save to drive, giving users the option to change location and save the extract report to their desktop before emailing.
    For some reason the group drive just didn’t like the pdf file!
    So many thanks for the suggestions.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!