VBA code to Open Outlook

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
90
I have code that sends an email through VBA code in excel. I want to test to see if Outlook is open (not everone will be using this on their primary email box) and if not, to open then send the attachments.
I have simpliefied the code below, would appreciate it if someone can give me the additional code to open this application.
When I run the following code on a box that does not have Oulook open, I get into some kind of a loop with a message that pops up that says "Excel is waiting for another OLE application to finish", which it never does...
Thoughts?
Thanks so much, in advance.

Code:
Sub send_test()
Dim Outlook_App As Object
Dim Outlook_Mail As Object

Set Outlook_App = CreateObject("Outlook.Application")
Set Outlook_Mail = Outlook_App.CreateItem(0)
On Error Resume Next
With Outlook_Mail
    .To = "me@xxxx.com"
    .Subject = "Test Email"
    .Body = "Help me!"
    .Send
End With
On Error GoTo 0

Set Outlook_Mail = Nothing
Set Outlook_App = Nothing
    
End Sub
 

ashish koul

Board Regular
Joined
Dec 30, 2010
Messages
56
try this see if it helps

Code:
Sub check_is_running()
Dim Process As Object
Dim proc_name As String
Dim bl  As Boolean
proc_name = "Outlook.exe" '\change process name here
bl = False
    For Each Process In GetObject("winmgmts:").ExecQuery("Select Name from Win32_Process Where Name = '" & proc_name & "'")
        If UCase(Process.Name) = UCase(proc_name) Then
            bl = True
            Exit For
        End If
    Next Process
    
    If bl = False Then
        MsgBox "Outlook is not open"
    Else
        MsgBox "Outlook is open"
    End If
End Sub
 

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
90
Ashish, thanks for the assistance.
That works to establish whehter the program is running, now, do you know what the command to open Outlook is so I can send an email?
 

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
90
thanks jd.
I have tried .Display.
It displays the email, but does not open Outlook to allow the email to be sent.
That is how I got into the loop that says "Excel is waiting for anothe OLE application to finish"
Any other thoughts?
 

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
90
By the way, Isn't the CreateObject Function supposed to start the application? Or does it just start a new email from an alreadyu open application?
 

jdsouza

Board Regular
Joined
Jul 19, 2012
Messages
101
try this

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
 

jdsouza

Board Regular
Joined
Jul 19, 2012
Messages
101
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

Put the above at the top of the module or form code where the sub is located
 

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
90
Awesome jd!
After I send the email, I want to close it, can I just use Application.Quit to do this?
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top