By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Opening Outlook from Access when Outlook is Closed

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Approve_Click()
  2. On Error GoTo Err_Approve_Click
  3.     DoCmd.GoToRecord , , acNewRec
  4.  
  5.     DoCmd.OpenQuery "Qry_Final Capital Budget Approval"
  6.  
  7.     DoCmd.SelectObject acForm, "Capital Request Finance", False
  8.  
  9.     DoCmd.OutputTo acOutputForm, "Capital Request Finance", acFormatHTML, "Attachment Path", False, "", 0, acExportQualityPrint
  10.  
  11.  
  12.  
  13.           Dim objOutlook As Outlook.Application
  14.           Dim objOutlookMsg As Outlook.MailItem
  15.           Dim objOutlookRecip As Outlook.Recipient
  16.           Dim objOutlookAttach As Outlook.Attachment
  17.  
  18.  
  19.           ' Create the Outlook session.
  20.  
  21.           Set objOutlook = CreateObject("Outlook.Application")
  22.  
  23.  
  24.           ' Create the message.
  25.           Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  26.  
  27.           With objOutlookMsg
  28.               ' Add the To recipient(s) to the message.
  29.               Set objOutlookRecip = .Recipients.Add("recipient email address")
  30.               objOutlookRecip.Type = olTo
  31.  
  32.               ' Add the CC recipient(s) to the message.
  33.               'Set objOutlookRecip = .Recipients.Add("")
  34.               'objOutlookRecip.Type = olCC
  35.  
  36.              ' Add the BCC recipient(s) to the message.
  37.               'Set objOutlookRecip = .Recipients.Add("")
  38.               'objOutlookRecip.Type = olBCC
  39.  
  40.              ' Set the Subject, Body, and Importance of the message.
  41.              .Subject = "Capital Project Request"
  42.              .Body = "Please Review the following Capital Project Request." & vbCrLf & vbCrLf
  43.              .Importance = olImportanceHigh  'High importance
  44.  
  45.              ' Add attachments to the message.
  46.              If Not IsMissing(AttachmentPath) Then
  47.                  Set objOutlookAttach = .Attachments.Add("Attachment Path")
  48.              End If
  49.  
  50.              ' Resolve each Recipient's name.
  51.              For Each objOutlookRecip In .Recipients
  52.                  objOutlookRecip.Resolve
  53.              Next
  54.  
  55.              ' Should we display the message before sending?
  56.              'If DisplayMsg Then
  57.               '   .Display
  58.              'Else
  59.                 .Save
  60.                 .Send
  61.              'End If
  62.           End With
  63.           Set objOutlook = Nothing
  64.  
  65.  
  66. Err_Approve_Click:
  67.      'MsgBox "Error No.:" & Err.Number
  68.     'Resume Exit_Approve_Click
  69.  
  70.     Response = acDataErrContinue
  71.  
  72.     If Err.Number = 0 Then
  73.  
  74.       MsgBox "Data has been successfully saved. Please click the Exit button to close application"
  75.     Else
  76.         MsgBox "Data Saved Successfully"
  77.  
  78.  
  79.  
  80.     Resume Exit_Approve_Click
  81.     End If
  82. Exit_Approve_Click:
  83.     Exit Sub
  84. End Sub
Feb 19 '09 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,417
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).
Feb 20 '09 #2

ADezii
Expert 5K+
P: 8,623
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.
Expand|Select|Wrap|Line Numbers
  1. 'Declaration for the API Function
  2. Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" _
  3.  (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.
Expand|Select|Wrap|Line Numbers
  1. Dim retVal As Variant
  2.  
  3. 'Adjust if necessary
  4. Const conPATH_TO_OUTLOOK As String = "C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE"
  5.  
  6.  
  7. If apiFindWindow(CStr("rctrl_renwnd32"), 0&) = 0 Then
  8.   'Outlook is not running, let's open it
  9.   retVal = Shell(conPATH_TO_OUTLOOK, vbMaximizedFocus)
  10. Else
  11.   'Outlook is running, I'll leave the rest up to you!
  12. End If
Feb 20 '09 #3

P: 6
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.
Feb 20 '09 #4

NeoPa
Expert Mod 15k+
P: 31,417
Did you check out the Application Automation link?
Feb 20 '09 #5

ADezii
Expert 5K+
P: 8,623
@dlblack
How about executing the code in an AutoExec Macro or in your Start Up Form's Open() or Load() Event?
Feb 21 '09 #6

ADezii
Expert 5K+
P: 8,623
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.
Feb 21 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.