send excel as attachment error in code

[bazbuyer]

Hi
I've previously used code without issue, but in a different employer and in Office 2016, now Office 365. I'm getting a runtime 91 error "Object variable or with block variable not set". The code is set out below, I assume its causing an error at the line "Set OutApp = CreateObject("Outlook.Application")" and that the Office 365 is now not recognising that or something relating to the different IT environment?

Sub Email_Open_WB_as_Attachment()
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wbl = ActiveWorkbook
'Make copy of file, open oit,mail it, delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wbl.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wbl.Name, Len(wb1.Name) - InStrRev(wbl.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "DPO"
.CC = ""
.BCC = ""
.Subject = "Internal Inventory/Retention Exercise"
.Body = "Completed form attached"
.Attachments.Add TempFilePath & TempFileName & FileExtStr
.SEND
End With
On Error GoTo 0

'delete file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

 

[6StringJazzer]

I assume its causing an error at the line "Set OutApp = CreateObject("Outlook.Application")"

Not sure why you use the word "assume" here. You can determine this with certainty. If you get a runtime error, you will see a dialog box. Click on Debug. What line of code is highlighted in yellow?

 

[bazbuyer]

Not sure why you use the word "assume" here. You can determine this with certainty. If you get a runtime error, you will see a dialog box. Click on Debug. What line of code is highlighted in yellow?

the dialogue box gave me an ok or help (which brings to a web page on objects). I used F8 which jumped through most of the code line by line and errored at the line above CreateObject("Outlook.Application")

 

[smozgur]

It looks like you have a problem with the new file name. Insert this code just above the failing ws1.SaveCopyAs ... line, and execute the code then look at the immediate (debug - Ctrl + G) window to see what it will print:

Debug.Print TempFilePath & TempFileName & FileExtStr

Make sure the file path and name are correct because it should be the problem.

Note: Please do not mark a post that doesn't contain an answer. That's why I unmarked it.

 

[smozgur]

A closer look: You have 1 (one) instead of l (lower case L) in the variable name in this line.

FileExtStr = "." & LCase(Right(wbl.Name, Len(wb1.Name) - InStrRev(wbl.Name, ".", , 1)))

Just fix it as shown below and retry:

FileExtStr = "." & LCase(Right(wbl.Name, Len(wbl.Name) - InStrRev(wbl.Name, ".", , 1)))

 

[6StringJazzer]

A closer look: You have 1 (one) instead of l (lower case L) in the variable name in this line.

I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors. Option Explicit would have caused the compiler to flag wbl as an undeclared variable.

Interestingly this bug is not related to the version of Excel being used Nine times out of 10 when someone tells me that the only thing that changed is the version of Excel, something else changed.

 

[bazbuyer]

Thank you both for your replies and help kind regards

 

[bazbuyer]

Actually found this from some years back and this replaces the entire code much more simply

Sub SEND()
ActiveWorkbook.SendMail Recipients:=Array(email1,email2)
End Sub

 

[smozgur]

Actually found this from some years back and this replaces the entire code much more simply

Exactly. However, these are completely different methods that work for different requirements. Glad to hear that the SendMail method helps your project.

In the meantime, I will mark post #6 as the solution to this question as it contains the most useful recommendation about declaring variables as well as confirming the typo in the code in the question. This way it will help future readers to see the typo in this code and encourage them to declare variables in their own projects. The question itself provides good information about using the Outlook object in VBA to attach a workbook, and finally, your final post shows another way of sending a workbook as an attachment. This is a good thread