Send Email from Excel - Error in Macro

[TG2812]

Hello, I came up with the below code in order to send an excel file from VBA leveraging outlook.
While some part of the code works, I'm stuck when Excel is not opened /or is opened. I got an error.

I have highlighted the part I'm struggling with in red. Any idea what I have to adjust?
Thank you very much in advance for your help!

-------------------------------------------------------

Sub SendEmail()

Dim rng As Range, OutApp As Object, OutMail As Object, Dest As Workbook, wb As Workbook
Dim sSubj As String, iMonth As String, iYear As Integer, Obj As Object
Dim TempFilePath As String, TempFileName As String

Application.Calculation = xlCalculationManual

iMonth = MonthName(Month(Now()))
iYear = Year(Now())
sSubj = iMonth & "-" & iYear & " " & " my subject"

Set Obj = CreateObject("WScript.Shell")
desktop = Obj.SpecialFolders("Desktop")
Set wb = ThisWorkbook
Set Dest = Workbooks.Add
Set rng = Nothing
On Error Resume Next
Set rng = wb.Sheets("Tracking").Cells(1).CurrentRegion
rng.Copy

With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With

TempFilePath = desktop & ""
TempFileName = "My Subject" & "-" & Format(Now, "dd-mmm-yyyy h-mm-ss")
Dest.SaveAs TempFilePath & TempFileName & ".xlsx", FileFormat:=51
Dest.Close

On Error GoTo 0

With Application
.EnableEvents = 0
.ScreenUpdating = 0
End With

Set OutApp = CreateObject("Outlook.Application")
If OutApp Is Nothing Then
MsgBox "Outlook is not open, Open Outlook and try again!"
Kill TempFilePath & TempFileName & ".xlsx"
Exit Sub
Else
Set OutMail = OutApp.CreateItem(0)

End If

On Error Resume Next
With OutMail
.Subject = sSubj
.Attachments.Add Dest.FullName
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = 1
.ScreenUpdating = 1
End With

Dest.Close savechanges:=False
Kill TempFilePath & TempFileName & ".xlsx"
Set OutMail = Nothing: Set OutApp = Nothing


End Sub

 

[TG2812]

Thank you for your prompt answer. I'm probably missing something but the macro is not working.
When I run the code on debug mode, the errors happens on this line "Set oOutlook = GetObject(, "Outlook.Application")"

I do not know what to do...

Here's the latest code I have. Do not hesitate to adjust it.

Sub SendEmail()




Dim rng As Range, oOutlook As Object, OutMail As Object, Dest As Workbook, wb As Workbook
Dim sSubj As String, iMonth As String, iYear As Integer, Obj As Object
Dim TempFilePath As String, TempFileName As String
Dim desktop As String
Application.Calculation = xlCalculationManual




iMonth = MonthName(Month(Now()))
iYear = Year(Now())
sSubj = iMonth & "-" & iYear & " " & " my subject"




Set Obj = CreateObject("WScript.Shell")
desktop = Obj.SpecialFolders("Desktop")
Set wb = ThisWorkbook
Set Dest = Workbooks.Add
Set rng = Nothing
On Error Resume Next
Set rng = wb.Sheets("Tracking").Cells(1).CurrentRegion
rng.Copy




With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With




TempFilePath = Environ("USERPROFILE") & "\Desktop\"
TempFileName = "My Subject" & "-" & Format(Now, "dd-mmm-yyyy h-mm-ss")
Dest.SaveAs TempFilePath & TempFileName & ".xlsx", FileFormat:=51




On Error GoTo 0




With Application
.EnableEvents = 0
.ScreenUpdating = 0
End With




Set oOutlook = GetObject(, "Outlook.Application")
On Error GoTo 0




If oOutlook Is Nothing Then
MsgBox "Outlook is not open, Open Outlook and try again!"
Kill TempFilePath & TempFileName & ".xlsx"
Exit Sub
Else
Set OutMail = oOutlook.CreateItem(0)




End If




On Error Resume Next
With OutMail
.Subject = sSubj
.Attachments.Add Dest.FullName
.Display
End With
On Error GoTo 0




With Application
.EnableEvents = 1
.ScreenUpdating = 1
End With




Dest.Close savechanges:=False
Kill TempFilePath & TempFileName & ".xlsx"
Set OutMail = Nothing: Set oOutlook = Nothing




End Sub

 

[Logit]

.
You did not use the code posted in #10 .

Copy the code shown in Post #10 and paste it into a module. Connect your CommandButton to that macro.

As indicated, the macro I posted in #10 runs here.

 

[TG2812]

Logit, it does work. I did not read carefully post #10 . My apologies.
Thank you very much for your time and help here!

 

[Logit]

You are welcome. Glad to help.