Outlook unable to open

chintu

New Member
Joined
Apr 1, 2018
Messages
17
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.
 
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;

Code:
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
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top