locked
Send Email When Outlook is Closed. RRS feed

  • Question

  • What is the proper way to handle sending an email when Outlook is closed?  I'm using Office 365.  I needed  quick solution so I wrote a routine that opens Outlook if it is not running.  Is there a better way?

    #If VBA7 Then ' Excel 2010 or later
      Private Declare PtrSafe 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
    #End If
    
    Sub SendReport()
      Dim oApp As New Outlook.Application
      Dim msg As Outlook.MailItem
      Dim oAcc As Outlook.Account
     
      Set msg = oApp.CreateItem(olMailItem)
      Set oAcc = oApp.Session.Accounts.Item(2)
      
     ' ....
    End Sub
    
    Sub OpenOutlook()
      Dim oOutlook As Object
      Dim ret As Long
    
      On Error Resume Next
      Set oOutlook = GetObject(, "Outlook.Application")
      If oOutlook Is Nothing Then
        On Error GoTo handler
        ret = ShellExecute(Application.hwnd, vbNullString, "Outlook", vbNullString, "C:\", SW_SHOWNORMAL)
        If ret < 3 Then
          Call MsgBox("Outlook is not found.", vbOKOnly, "Outlook Not Found")
        End If
      End If
    Exit Sub
    
    handler:
      Call MsgBox(Err.Number & ": " & Err.Description, vbOKOnly, "Error Opening Outlook")
    End Sub

     
    Friday, August 14, 2020 4:24 PM

All replies