CODEWITHSUNDEEP
vbaexcel
SaiKiran Mandhala
SaiKiran Mandhala

Reputation: 365

How to send mail when outlook is closed

i have following lines of code. It works fine when outlook is opened but i want it to work even though outlook is closed. I kept the code in command button click event.

Private Sub btnSend_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = GetObject("", Outlook.Application)
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test mail from Excel Sheet-OutLook Closed"
    .Body = "This is body of the mail"
    .Display
    .Send
    .ReadReceiptRequested = True
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I tried it with both GetObject and CreateObject methods. If i execute this code after closing outlook it's not showing any error but it's not sending any mail.

The following lines of code sending the mails but they are queuing in the outlook's outbox. when user opens outlook then only they are moving out from outbox.

Private Sub btnSend_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test mail from Excel Sheet-OutLook Closed"
    .Body = "This is body of the mail"
    .Display
    .Send
    .ReadReceiptRequested = True
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Upvotes: 1

Views: 10159

Answers (2)

sanitycheck
sanitycheck

Reputation: 11

For Outlook 2013, this is an issue with Outlook settings, not the VBA code.

  • Open OUTLOOK

  • Go To FILE -> OPTIONS -> ADVANCED

  • Scroll to 'Send and Receive' heading and click 'Send/Receive…' button

  • Under 'Setting for group 'All Accounts' ', ensure that 'Perform an automatic send/receive when exiting' is CHECKED

This ensures all items in the OUTLOOK 'Outbox' are sent when Outlook closes. This fixed the issue for me. Likely similar for other versions of Outlook.

Upvotes: 1

user2481205
user2481205

Reputation: 46

You can use shell commands to actually open outlook before sending a mail.Precisely being 

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Public Sub OpenOutlook()
Dim ret As Long
 On Error GoTo aa
 ret = ShellExecute(Application.hwnd, vbNullString, "Outlook", vbNullString, "C:\", SW_SHOWNORMAL)
 If ret < 3 Then

 MsgBox "Outlook is not found.", vbCritical, "SN's Customised Solutions"
 End If
aa:
End Sub

keep this in a separate module and call the module from the code where you are sending the mail.The part i am trying to work on is how to hide this so that activation is still with excel

Upvotes: 1

Related Questions