Upgraded to M365 and getting VBA 400 error...

[richierich1985]

Morning everyone, looking for some help.

I've written some code for an Excel form I am building at work. The form sits on a SharePoint site, essentially a user will complete the form... and then click a button to attach it to a new email in Outlook.

The code was working up until yesterday, when our IT department helpfully 'upgraded' me to M365. I know get the 400 error when clicking the button to attach it to an email.

The code saves it to a temp folder, attaches it to the email, then deletes from the temp folder. So I am sure the issue is being caused by M365. Any ideas?? Code below...

_____________________

Sub Email_Sheet()


   Dim oApp As Object

   Dim oMail As Object

   Dim LWorkbook As Workbook

   Dim LFileName As String


   Application.ScreenUpdating = False

   Application.DisplayAlerts = False

 
   ActiveSheet.Copy

   Set LWorkbook = ActiveWorkbook


   LFileName = LWorkbook.Name & " Email.xlsx"

   On Error Resume Next

   Kill LFileName

   On Error GoTo 0

   LWorkbook.SaveAs fileName:=LFileName

 
   Set oApp = CreateObject("Outlook.Application")

   Set oMail = oApp.CreateItem(0)



   With oMail

      .To = XXXXXXXXXXX@XXXXX.com

      .Subject = "NST New Incident: " & Range("D15")
   
      .Body = Range("D15") & vbCrLf & vbCrLf & "Thank you for raising a Support Ticket with the Network Support Team. Please ensure you attach any relevant documentation/photos relating to this Support Ticket before pressing the send button to submit your ticket to the team." & vbCrLf & vbCrLf & "We will respond to your email within a 2 hour SLA, if however you raise a ticket on a Saturday or Sunday we will then respond to you before 11am on Monday."

   
      .Attachments.Add LWorkbook.FullName

      .Display

   
   End With

 
   LWorkbook.ChangeFileAccess Mode:=xlReadOnly

   Kill LWorkbook.FullName

   LWorkbook.Close SaveChanges:=False


   Application.ScreenUpdating = True

   Application.DisplayAlerts = True

 

   Set oMail = Nothing

   Set oApp = Nothing

 

End Sub

 

[RoryA]

Where does the error occur?

 

[richierich1985]

Morning Rory,

It seems to occur when creating the temporary workbook. It should create the workbook, save it to a temp folder, add to email, then delete from temp folder.

I believe it occurs when trying to save to a temp folder.

Thank you, Richard

 

[RoryA]

Your code doesn't actually specify where to save it, so it will go into whatever the current directory is. Perhaps you need to add a path?

 

[richierich1985]

So perhaps try just dropping in "C:/" into the code?

Thank you, this is the first time I've ever used VBA so it's quite a learning curve.

I think the difference with the 365 version of Excel is it auto saves to OneDrive/SharePoint rather than on your hard drive, so I think that is causing the issue. Previously it would just save it to a temp folder, but its trying to save it temporarily to OneDrive causing an issue. Fun and games eh!