locked
Macro Runs Independently, but not Part of a Schedule RRS feed

  • Question

  • System: Windows 7; Dell Lattitude E5520; Outlook 2010

    My computer wakes from Hibernation using Task Scheduler. Outlook is already active prior to the computer being placed into Hibernation.  Using Outlook Tasks, I have created routines that run at scheduled times and within the routine, they get re-scheduled for the next run time.

    One of my Macros crashes the routine.  However, when I select "End" The debugging session, and run it manually, it works with no problems.  The macro is below, when ran as part of my "automation" schedule crashes on the bold/Italic/Underline.

    I thought I saw something posted before that the line is creating a new Outlook Session??? If that is the problem, how can I change it to use the existing outlook session?  Do I need to place the e-mail macro into the This Session Module?

    It puzzles me, because after I end the debug session, and click the macro manually, it runs with no errors. Why would it create an error when part of the automation process?

    Any thoughts / suggestions are appreciated.  If more information is needed, please let me know.

    Thanks to all for your help.

    Merry Christmas and Happy New Year.

    JMData Consultant

    Sub SalesPerson1Email()
    Dim appOutLook As Outlook.Application
      Dim MailOutLook As Outlook.MailItem
      Dim strPath As String
      Dim strFileName As String
      Set appOutLook = CreateObject("Outlook.Application")
      Set MailOutLook = appOutLook.CreateItem(olMailItem)
      With MailOutLook
        .BodyFormat = olFormatRichText
        .To = "SalesPerson1@xxxxxx.com"
        .CC = "Admin@xxxxxx.com"
        '.bcc = ""
        .Subject = "AUTOMATIC MESSAGE: Sales Person 1 REPORTS for: " & Format(Date, "mm-dd-yy") & " " & Format(Now(), "hh:mm AMPM")
        .HTMLBody = "HTML BODY MESSAGE"
        'add all PDF files
        strPath = "h:\Automated Report Files\SalesPerson1\"
        strFileName = Dir(strPath & "* " & Format(Date, "yyyy-mm-dd") & ".* ")
        While strFileName <> ""
          .Attachments.Add (strPath & strFileName)
          strFileName = Dir()
        Wend
        .Send
      End With
    End Sub

    Monday, December 23, 2013 5:55 PM

Answers

  • When using the Outlook VBA project you never create a new Outlook Application object, you use the one handed to you in the project as Application. That object is trusted and won't fire the Outlook security. You can only have 1 Outlook session running at a time anyway.

    The Outlook object model is not supported for use in schedulers or scheduled processes, or in services. Using it that way will either fail with exceptions or crash the Outlook session. You can use Redemption or even CDO 1.21 for that, but not the Outlook object model.


    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 7:02 PM
  • NameSpace doesn't have a CreateItem() method, Outlook.Application does. The item creation should look like this:

    Dim MailOutLook As Outlook.MailItem

    Set MailOutLook = Application.CreateItem(olMailItem)

    The example I gave was just that, an example of getting a NameSpace object. It wasn't meant to imply that NameSpace could be used in place of Application.

    Just use Application everywhere you'd use an Outlook.Application object, or set one if you want:

    Dim appOutlook As Outlook.Application

    Set appOutlook = Application

    Dim MailOutLook As Outlook.MailItem

    Set MailOutLook = appOutlook.CreateItem(olMailItem)

    Again, that was just an example.


    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 8:35 PM

All replies

  • When using the Outlook VBA project you never create a new Outlook Application object, you use the one handed to you in the project as Application. That object is trusted and won't fire the Outlook security. You can only have 1 Outlook session running at a time anyway.

    The Outlook object model is not supported for use in schedulers or scheduled processes, or in services. Using it that way will either fail with exceptions or crash the Outlook session. You can use Redemption or even CDO 1.21 for that, but not the Outlook object model.


    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 7:02 PM
  • So how do I use the session that is already open? Everything is running out of Outlook.  I'm not using outside applications or third party programs that are referencing these macros.

    Monday, December 23, 2013 7:10 PM
  • Just use Application. In the Outlook VBA project it means Outlook.Application. For example:

    Sub Whatever()

        Dim oNS As Outlook.NameSpace

        Set oNS = Application.GetNameSpace("MAPI")

    and so on.

    Just make sure you don't use the OOM from a service, scheduled task or other such.


    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 7:31 PM
  • Ken,

    Thanks for the response.

    I've changed the code to the following:

    Sub GregPearsonEmail()
    Dim appOutLook As Outlook.NameSpace ' was Outlook.Application
      Dim strPath As String
      Dim strFileName As String
      Set appOutLook = Application.GetNamespace("MAPI") ' was "CreateObject("Outlook.Application")"
      Set MailOutLook = appOutLook.CreateItem(olMailItem)
      With MailOutLook

    I'm now getting Run-time error '438' - Object doesn't support this property or method on the Set MailOutlook = appOutLook.CreateItem(olMailItem) line.

    What am I missing??

    JMData Consultant

    Monday, December 23, 2013 8:18 PM
  • Ken.... I believe I figured out the correct formatting.

    I have the following and it appears to be working without issues.

    Dim appOutLook As Outlook.NameSpace ' was Outlook.Application
      Dim strPath As String
      Dim strFileName As String
      Dim MailOutlook As Object
      Set appOutLook = Application.GetNamespace("MAPI") ' was CreateObject("Outlook.Application")
      Set MailOutlook = Application.CreateItem(olMailItem)

    Is there any pitfall here that will comeback later to haunt me??

    Thanks for your help on this.. I've been struggling with this for a while.

    JMData Consultant

    Monday, December 23, 2013 8:31 PM
  • NameSpace doesn't have a CreateItem() method, Outlook.Application does. The item creation should look like this:

    Dim MailOutLook As Outlook.MailItem

    Set MailOutLook = Application.CreateItem(olMailItem)

    The example I gave was just that, an example of getting a NameSpace object. It wasn't meant to imply that NameSpace could be used in place of Application.

    Just use Application everywhere you'd use an Outlook.Application object, or set one if you want:

    Dim appOutlook As Outlook.Application

    Set appOutlook = Application

    Dim MailOutLook As Outlook.MailItem

    Set MailOutLook = appOutlook.CreateItem(olMailItem)

    Again, that was just an example.


    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 8:35 PM
  • That should be fine. Using Application is the recommended way of doing things within the Outlook VBA project.

    Ken Slovak MVP - Outlook

    Monday, December 23, 2013 9:05 PM
  • Hi Ken - Can you please let me know how you had scheduled a macro to run automatically. I tried it in windows task scheduler and passed the macro name as argument. It throws an error. 

    Can you please share the syntax and any other useful tips.

    thanks

    Suresh

    Thursday, May 21, 2015 12:22 AM
  • Hello Suresh,

    I have just answered to your post here. Don't use old threads for asking separate questions.

    Be aware, Outlook should be run to get the macro working periodically.  

    Thursday, May 21, 2015 7:12 AM
  • I never said that I had run a macro periodically. What I said was the Outlook object model is not suitable or supported for running from a scheduler, a background process or thread or anything other than a user context.

    One can set up a Windows API system timer using VBA and Win32 API calls. That will run in the same thread as Outlook and can be used to start up a macro. But that code would have to run inside the Outlook VBA project and Outlook would have to be running. However that's a hack.


    Ken Slovak MVP - Outlook

    Thursday, May 21, 2015 1:50 PM