Page 1 of 3 123 LastLast
Results 1 to 10 of 21

VBA code to Open Outlook

This is a discussion on VBA code to Open Outlook within the Excel Questions forums, part of the Question Forums category; I have code that sends an email through VBA code in excel. I want to test to see if Outlook ...

  1. #1
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default VBA code to Open Outlook

    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

  2. #2
    Board Regular
    Join Date
    Dec 2010
    Location
    New Delhi, India
    Posts
    56

    Default Re: VBA code to Open Outlook

    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

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default Re: VBA code to Open Outlook

    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?

  4. #4
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    96

    Default Re: VBA code to Open Outlook

    Try

    .Display

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default Re: VBA code to Open Outlook

    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?

  6. #6
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default Re: VBA code to Open Outlook

    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?

  7. #7
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    96

    Default Re: VBA code to Open Outlook

    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

  8. #8
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default Re: VBA code to Open Outlook

    ShellExecute is not defined.

  9. #9
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    96

    Default Re: VBA code to Open Outlook

    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

  10. #10
    Board Regular
    Join Date
    Feb 2005
    Posts
    85

    Default Re: VBA code to Open Outlook

    Awesome jd!
    After I send the email, I want to close it, can I just use Application.Quit to do this?

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com