Outlook unable to open

[chintu]

Hi Team,

I am trying to use the below VBA Code to generate the outlook Draft report with attachment, however was unable to do so. Please navigate me to the correct coding:

Sub SEND_EMAIL_WITH_FILE()
On Error Resume Next
Sheet6.Activate
Dim Otl As Outlook.Application
Set Otl = New Outlook.Application
Dim otlmail As Outlook.MailItem

Dim i As Long

For i = 15 To Range("A150").End(xlUp).Row
If Cells(i, 6).Value = "yes" Then
Set otlmail = Otl.CreateItem(olMailItem)

With otlmail
.Body = "Hi " & Cells(i, 1).Value _
& vbNewLine & vbNewLine & _
"Please provide the BS Recon for attached global account details for " & Range("B2") & " of " & Range("B1")
.To = Cells(i, 2).Value
.CC = Cells(i, 3).Value
.Subject = Range("B3") & ":" & Cells(i, 4).Value & " BS RECON BACKUP " & Range("B1")
.Attachments.Add Cells(i, 5).Value
.Display

End With
'MsgBox "Selected Item Sent", vbOKOnly


' ElseIf Cells(i, 6).Value = "NO" Then
' MsgBox "Mail Not Required", vbOKOnly
' Exit Sub
End If
Next i
Set Otl = Nothing
Set otlmail = Nothing

MsgBox "DONE", vbOKOnly
'MsgBox "Mail Has Been Sent To Respective Person Along With Files", vbOKOnly

End Sub

FYI - I have accessed all related References.

 

[daverunt]

What do you mean by 'Outlook mail box'? You mean you expect Outlook to open and display the Inbox?
I'm sure one of the experts here will correct me, but if Outlook isn't running already, as far as I know this code only creates a background
running instance to handle the mail generated. In this case it will only display the mail for you to press Send.
You can check Outlook is running in the Task Manager and on my system I see a taskbar icon. The instance is terminated when you set it to nothing.

If you want it to display the Inbox - making Outlook visible, try;

Sub SEND_EMAIL_WITH()

Dim i As Long
Dim Otlapp As Outlook.Application
Dim otlmail As Outlook.MailItem
Dim OtlappInbox As Outlook.MAPIFolder

 Sheet1.Activate
 
 Set Otlapp = New Outlook.Application
 Set OtlappInbox = Otlapp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
 OtlappInbox.Display
 
 For i = 2 To Range("A150").End(xlUp).Row
 If Cells(i, 6).Value = "yes" Then

 Set otlmail = Otlapp.CreateItem(olMailItem)

 On Error Resume Next
 With otlmail
 .Body = "Hi " & Cells(i, 1).Value _
 & vbNewLine & vbNewLine & _
 "Please provide the BS Recon for attached global account details for " & Range("B2") & " of " & Range("B1")
 .To = Cells(i, 2).Value
 .CC = Cells(i, 3).Value
 .Subject = Range("B3") & ":" & Cells(i, 4).Value & " BS RECON BACKUP " & Range("B1")
 .Attachments.Add Cells(i, 5).Value
 .Display

 End With
 End If
 Next i


 Set otlmail = Nothing
 Set Otlapp = Nothing

 MsgBox "DONE", vbOKOnly
 End Sub