Opening Outlook from Access when Outlook is Closed

[dlblack]

I am using the following code to send emails with attachments from Access using Outlook. The code works fine if Outlook is already open and emails are sent as expected via Outlook. When Outlook is closed, data is saved to the database but the email is not sent. Here is the the code. If anyone has suggestions on how I can get Outlook to open before trying to send the email I would appreciate it.

Private Sub Approve_Click()
On Error GoTo Err_Approve_Click
    DoCmd.GoToRecord , , acNewRec
 
    DoCmd.OpenQuery "Qry_Final Capital Budget Approval"
 
    DoCmd.SelectObject acForm, "Capital Request Finance", False
 
    DoCmd.OutputTo acOutputForm, "Capital Request Finance", acFormatHTML, "Attachment Path", False, "", 0, acExportQualityPrint
 
 
 
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
 
 
          ' Create the Outlook session.
 
          Set objOutlook = CreateObject("Outlook.Application")
 
 
          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
 
          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("recipient email address")
              objOutlookRecip.Type = olTo
 
              ' Add the CC recipient(s) to the message.
              'Set objOutlookRecip = .Recipients.Add("")
              'objOutlookRecip.Type = olCC
 
             ' Add the BCC recipient(s) to the message.
              'Set objOutlookRecip = .Recipients.Add("")
              'objOutlookRecip.Type = olBCC
 
             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Capital Project Request"
             .Body = "Please Review the following Capital Project Request." & vbCrLf & vbCrLf
             .Importance = olImportanceHigh  'High importance
 
             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("Attachment Path")
             End If
 
             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next
 
             ' Should we display the message before sending?
             'If DisplayMsg Then
              '   .Display
             'Else
                .Save
                .Send
             'End If
          End With
          Set objOutlook = Nothing
 
 
Err_Approve_Click:
     'MsgBox "Error No.:" & Err.Number
    'Resume Exit_Approve_Click
 
    Response = acDataErrContinue
 
    If Err.Number = 0 Then
 
      MsgBox "Data has been successfully saved. Please click the Exit button to close application"
    Else
        MsgBox "Data Saved Successfully"
 
 
 
    Resume Exit_Approve_Click
    End If
Exit_Approve_Click:
    Exit Sub
End Sub

[NeoPa]

I've not used automation in Outlook, but check out some details on how to automate and what to use in which circumstances (Application Automation).

[ADezii]

I'm rather in a rush right now, but I'll post the code for you, and should you have any questions whatsoever, some of the other Experts/Moderators/Members, will be happy to assist you. I'll check back on this Thread later if you need specifics on the code.

'Declaration for the API Function
Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" _
 (ByVal strClassName As String, ByVal lpWindowName As Any) As Long

See if Application is running by attempting to retrieve its Handle by passing its Class Name to apiFindWindow(). If it isn't running, launch it, if it is, do whatever you 'wish.

Dim retVal As Variant
 
'Adjust if necessary
Const conPATH_TO_OUTLOOK As String = "C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE"
 
 
If apiFindWindow(CStr("rctrl_renwnd32"), 0&) = 0 Then
  'Outlook is not running, let's open it
  retVal = Shell(conPATH_TO_OUTLOOK, vbMaximizedFocus)
Else
  'Outlook is running, I'll leave the rest up to you!
End If

[dlblack]

I think the code referenced by ADezii will work but being a newbie at VBA, I'm not sure how to integrate the new code into my existing code. I have my existing code running behind my forms and not in a module. I have been researching as to whether I need to put this in a module and the more research I do the more confused I get. Any suggestions would be helpful.

[NeoPa]

Did you check out the Application Automation link?

[ADezii]

@dlblack
How about executing the code in an AutoExec Macro or in your Start Up Form's Open() or Load() Event?

[ADezii]

I've finally sat back and took a closer look at things. If you are using Automation code to E-Mail and send Attachments via Outlook, when you create a New Instance of the Application, you are in effect 'Opening' it, and you can retrieve a Handle to it. I cannot actually see why your code is not working, and you need to 'Open' Outlook externally.