Please try the code below. In addition to saving to Sharepoint, the file is temporarily saved in the TEMP folder. After shipment, the temporary file is deleted.
Sub SendWeeklies_NewCode_1()
Dim OlApp As Object
Dim NewMail As Object
Dim NewWb As Workbook
Dim NameOfFile As String
Dim Recipient As String
Dim CCRecipient As String
Dim FilePath As String
NameOfFile = Range("WeekliesName").Value
If LCase(Right(NameOfFile, 5)) <> ".xlsx" Then
NameOfFile = NameOfFile & ".xlsx"
End If
Recipient = Range("Reciever").Value
CCRecipient = Range("CCReciever").Value
FilePath = Range("WAFilePath").Value
Range("Weeklies").Copy
Set NewWb = Workbooks.Add(Template:=xlWBATWorksheet)
With NewWb.Worksheets(1)
.Range("B2").PasteSpecial xlPasteAll
.Range("B2").PasteSpecial xlPasteColumnWidths
.Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.DisplayAlerts = False
'save into SharePoint...
.Parent.SaveAs Filename:=FilePath & NameOfFile, FileFormat:=xlOpenXMLWorkbook
'... and save in TEMP folder (locally)
.Parent.SaveAs Filename:=Environ("TEMP") & Application.PathSeparator & NameOfFile, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
End With
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
With NewMail
.To = Recipient
.CC = CCRecipient
.Subject = Left(NameOfFile, Len(NameOfFile) - 5)
'attach from TEMP
.Attachments.Add NewWb.FullName '(Environ("TEMP") & Application.PathSeparator & NameOfFile)
.Body = "Hermed denne uges SalgsDB."
.Display 'Send (After successful tests, replace with Send)
End With
Set NewMail = Nothing
Set OlApp = Nothing
NewWb.Close SaveChanges:=False
'remove file from TEMP
On Error Resume Next
Kill Environ("TEMP") & Application.PathSeparator & NameOfFile
On Error GoTo 0
End Sub
Artik