Hello all,
My company just migrated over to One Drive from a corporate shared drive, our inventory spreadsheet would use ThisWorkbook.path to save a copy each time someone would email it, now with One Drive using an HTTP protocol I get the run time error. Would someone please help me with what I need to change up or insert in my code?
Thanks ahead of time.
Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer
Sub Send_Outlook_Albany()
'>>>>>>>>>>>>>>>
'setup for Outlook 3/5/16
'<<<<<<<<<<<<<<
Dim wb, Wbsf As Workbook
Dim ans, FileVer As Integer
Dim wbsf1, suggname, salesdate, salesdate1, saveformat As String
Dim ExisFile, VerStr As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Terminal As String
Dim I As Long
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Terminal = "Tampa"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
saveformat = Application.DefaultSaveFormat
ans = MsgBox("This will Email a copy of this report via OUTLOOK" & vbCrLf & vbCrLf & _
"OK?", vbYesNoCancel + vbQuestion)
If ans = 2 Then Exit Sub
If ans = 7 Then MsgBox "Try again": Exit Sub
FileExtStr = ".xlsx": FileFormatNum = 51
FileVer = 1
salesdate = Sheets("Tank Report").Range("h4").Text
salesdate1 = Application.Text(salesdate, "mm-dd-yyyy")
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
'>>>>>>>>>>
'Copies File over to new workbook
'use for Emailing via Notes
Set wb = ActiveWorkbook
Set Wbsf = Workbooks.Add
Set Wbsf = ActiveWorkbook
wbsf1 = suggname
' Checks to see if file exists ...
'Add Digit to version number
Do While Dir(ThisWorkbook.Path & "" & wbsf1) <> ""
ExisFile = Dir(ThisWorkbook.Path & "" & wbsf1)
FileVer = Mid(ExisFile, InStr(ExisFile, "(") + 1, 1)
FileVer = FileVer + 1
VerStr = " (" & FileVer & ")"
suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
wbsf1 = suggname
'MsgBox FileVer
'MsgBox "OOPS File Exists", vbOKCancel + vbQuestion
Loop
On Error GoTo Errorhandler1
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & wbsf1, FileFormat:=FileFormatNum
wbsf1 = Wbsf.Name
If Val(Application.Version) > 14 Then
'You use Excel 2013 or higher
With Wbsf
Sheets.Add After:=ActiveSheet
Sheets.Add After:=ActiveSheet
End With
Else
'You use Excel 2010 or lower
End If
With wb
.Activate
.Sheets("Tank Report").Activate
Range("a1:r57").Select
.Sheets(Array("Tank Report", "Inventory", "Status")).Select
Selection.Copy
End With
With Wbsf.Sheets("Sheet1").Range("a1")
.PasteSpecial xlPasteAllUsingSourceTheme
'.PasteSpecial xlPasteAll
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues
End With
'Get a Global logo. Check for name
wb.Sheets("vessel report").Activate
ActiveSheet.Shapes("Picture 199").Select
Selection.Copy
Wbsf.Sheets("sheet3").Activate
ActiveWindow.DisplayGridlines = False
Wbsf.Sheets("sheet2").Activate
ActiveWindow.DisplayGridlines = False
Wbsf.Sheets("sheet1").Activate
ActiveWindow.DisplayGridlines = False
'Paste Global Logo
Wbsf.Sheets("Sheet1").Range("a1").Select
ActiveSheet.Paste
Wbsf.Sheets("sheet3").Activate
Wbsf.Sheets("Sheet3").Range("a1").Select
ActiveSheet.Paste
Wbsf.Sheets("sheet2").Activate
Wbsf.Sheets("Sheet2").Range("a1").Select
ActiveSheet.Paste
'Resets Original Workbook
With wb
.Sheets("Status").Activate
.Sheets("Status").Select
Range("a1").Select
.Sheets("Tank Report").Activate
.Sheets("Tank Report").Select
Range("c10").Select
.Sheets("Rate Report").Activate
.Sheets("Rate Report").Select
Range("D3").Select
.Sheets("Vessel Report").Activate
.Sheets("Vessel Report").Select
Range("C5").Select
.Sheets("Inventory").Select
End With
Application.CutCopyMode = False
' sets up the send file
With Wbsf
.Sheets("sheet1").Name = "Tank Report"
.Sheets("sheet2").Name = "Inventory"
.Sheets("sheet3").Name = "Status"
' activate and set print area
.Sheets("Tank Report").Activate
.Sheets("Tank Report").Select
With ActiveSheet.PageSetup
.PrintArea = "A1:R57"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintGridlines = False
End With
.Sheets("Tank Report").Range("a1").Select
'activate and set print area
.Sheets("Inventory").Activate
.Sheets("Inventory").Select
With ActiveSheet.PageSetup
.PrintArea = "A1:p35"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintGridlines = False
End With
.Sheets("Inventory").Range("a1").Select
'activate and set print area
.Sheets("Status").Activate
.Sheets("Status").Select
With ActiveSheet.PageSetup
.PrintArea = "A1:i42"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintGridlines = False
End With
.Sheets("Status").Range("a1").Select
.Sheets("Inventory").Activate
.Sheets("Inventory").Range("a1").Select
End With
ActiveWorkbook.Save
'<<<<<<<<<<<MAIL it
Application.CutCopyMode = False
Bookmarks