Email code stopped working

[Bagsy]

I wonder if anybody could just take a look at my email code. This was working just fine until today and now when it gets to the line

Set OutlookApp = CreateObject("Outlook.Application")

It just shuts excel down. I have tried this on another PC and it works just fine
Any help on this would be really appreciated

Code below

Sub RunCode()
'this is running the code to copy & email material booked in sheet and colour font to black
 Dim ans As Integer
    ans = MsgBox("Warning this will email the Booked In list", vbOKCancel)
        Select Case ans
    Case vbOK
Application.ScreenUpdating = False
'ActiveSheet.Unprotect Password:="password"
    Sheets("Sheet1").Activate
    
    Call EmailMaterialBookedIn
        Rows("6:105").Select
             With Selection.Font
        .Color = vbBlack
    End With
Range("B6").Select
'    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True, Password:="password"
    ActiveWorkbook.Save
Application.ScreenUpdating = True
Case vbCancel
End Select
End Sub

Sub EmailMaterialBookedIn()

Dim xFile As String
Dim xFormat As Long
Dim Wb As Workbook
Dim Wb2 As Workbook
Dim FilePath As String
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
'On Error Resume Next

    Sheets("Sheet1").Activate

Set Wb = Application.ActiveWorkbook
ActiveSheet.Copy
Set Wb2 = Application.ActiveWorkbook
Select Case Wb.FileFormat
Case xlOpenXMLWorkbook:
    xFile = ".xlsx"
    xFormat = xlOpenXMLWorkbook
Case xlOpenXMLWorkbookMacroEnabled:
    If Wb2.HasVBProject Then
        xFile = ".xlsm"
        xFormat = xlOpenXMLWorkbookMacroEnabled
    Else
        xFile = ".xlsx"
        xFormat = xlOpenXMLWorkbook
    End If
Case Excel8:
    xFile = ".xls"
    xFormat = Excel8
Case xlExcel12:
    xFile = ".xlsb"
    xFormat = xlExcel12
End Select
FilePath = Environ$("temp") & "\"
FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
With OutlookMail
    .To = Range("M3").Value
    .CC = Range("M4").Value
    .BCC = ""
    .Subject = "Material booked in"
    .Body = "Please find attached material booked in spreadsheet."
    .Attachments.Add Wb2.FullName
    .Send
End With
Wb2.Close
Kill FilePath & FileName & xFile
Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

 

[6StringJazzer]

There is nothing wrong with that line of code (I haven't taken the time to analyze all your code since you know what line is causing the problem). You are using late binding to create an instance of Outlook, and it looks correct.

I fear some sort of corruption. I suggest you make up a very simple test file on the same PC and see if the same thing happens. Just something like this

Sub TestOutlook()

Dim OutlookApp As Object
Dim OutlookMail As Object

Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)

With OutlookMail
    .To = "example@example.com"
    .Subject = "test of email creation"
    .Body = "test of email creation."
    .Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

If it also crashes Excel then you may have a corruption in your Excel installation and need to repair or reinstall. Do not do this just on my say-so, but that is my opinion.

 

[Bagsy]

There is nothing wrong with that line of code (I haven't taken the time to analyze all your code since you know what line is causing the problem). You are using late binding to create an instance of Outlook, and it looks correct.

I fear some sort of corruption. I suggest you make up a very simple test file on the same PC and see if the same thing happens. Just something like this

Sub TestOutlook()

Dim OutlookApp As Object
Dim OutlookMail As Object

Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)

With OutlookMail
    .To = "example@example.com"
    .Subject = "test of email creation"
    .Body = "test of email creation."
    .Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

If it also crashes Excel then you may have a corruption in your Excel installation and need to repair or reinstall. Do not do this just on my say-so, but that is my opinion.

Thanks for your help, really appreciated I will give it a go

 

[Bagsy]

6StringJazzer
Thanks for your support, not sure what went wrong, I had rebooted & tried several times no luck, then it just started to work, after I had been working in outlook, honestly you can't make this stuff up.

 

[Bagsy]

In case this helps anybody else, I seem to have solved this issue.
I had set a reminder in outlook to send this workbook every afternoon, the issue with the code seems to be connected with that. I found if I closed the reminder message before I ran the code there was no problem. I only had a problem when the reminder message was left open.

 

[6StringJazzer]

Thanks for sharing your resolution. The code seems to have no relationship to whether there is a reminder open. I suspect this is a bug in Outlook for how VBA interacts with the application.