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
.display
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
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