Application Automation

[NeoPa]

In VBA (I expect VB would be similar) controlling another Office Application (Automation) can be done using the following steps :

1. Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate.
2. Set up an application object variable to use (You can use With...End With if you prefer).
   Either :
   1. Use CreateObject(Cl ass) to open a new object for multi-instance programs (For single Instance programs it will return the current instance if already open).
   2. If supported for your desired application use GetObject(Docum ent, Class) to open a specific document.
3. Set the application's .Visible property. This determines if your changes are visible to the operator.
4. While the application's object is active, you can run code as if within the other application simply by referencing the application object (EG. accApp.Visible = True...Call accApp.CurrentD B.Close).
5. If you're finished with one document but you still want to keep the application ready for your use then set the .Visible property to False and re-activate later.
6. When you're finished completely either :
   1. If you want to close down the application simply close it (Call accApp.Quit).
   2. If you want to leave it open for the operator to use just make sure you leave .Visible set to True.
7. Release any associated objects (Set to Nothing or End With).

Here are some useful Methods / Properties that can be used from within your code :

• Quit - Each Application object has a Quit method.
• Activate - This sets the focus to the Application.
• Run - Enables you to run code from the Application (or Document).
• Copy / Paste - This should work happily between different Applications
• WindowState - Can be set to Maximized, Minimized or Normal.

NB.
When working with any Office Application's own VBA IDE (Independent Development Environment) there are generally a whole bunch of defaults you get used to. For instance when referring in Excel to a Range("A1") object it will understand such a reference to mean to the ActiveSheet object of Excel's Application. In any foreign environment these defaults are not set so, assuming you have an Excel.Applicati on object called appXL, you would refer to it as appXL.ActiveShe et.Range("A1") instead. This is just one example but the point holds true for all Office Applications.

Most people, when starting to use Application Automation for the first time, find they fall over this problem quite a lot. Look out for it and do what you can to avoid it as much as possible.

Posted by fauxanadu

Basic Outlook Automation (including Send New e-Mail)

' Requires A References to the Microsoft Outlook Object Library
' in VBE, select Tools->References, and ensure it is checked

Private Sub ExportToOutlook()
    ' Variable Declaration
    Dim objOutlook as Object
    Dim objEMail as Object

    ' If Outlook is open, use GetObject, otherwise open Outlook
    Set objOutlook = GetObject(, "Outlook.Application")
    If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")

    ' Creates a new e-mail
    Set objEMail = objOutlook.CreateItem(0)
    With objEMail

        ' Adds To Recipient
        Set ToContact = .Recipients.Add("Me@Gmail.Com")

        ' Adds CC recipient
        ToContact.Type = olCC
        Set ToContact = .Recipients.Add("You@Gmail.com")

        ' Sets the Subject
        .Subject = "Service Report 1234"

        ' Sets the Body
        .Body = "Attached herein are the Reports"

        ' Adds attachment
        .Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"

        ' Embeds attachment
        .Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"

        ' Receipt upon delivery
        .OriginatorDeliveryReportRequested = True

        ' Recipt upon read
        .ReadReceiptRequested = True

        ' Displays the E-Mail
        .Display

        ' Sends the E-Mail
        .Send

        ' Saves a Draft of the E-Mail
        .Save                                       
    End With

End Sub

Basic Excel Automation

' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
' In VBE, goto Tools->References... and select it from the list
Private Const conAppNotRunning As Long = 429

Private Sub ExportToExcel()
    ' Variable Declarations
    Dim objExcel As Excel.Application

    ' If Excel is open, use GetObject, otherwise create a new Excel object
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Err = conAppNotRunning Then Set objExcel = New Excel.Application

    With objExcel
        ' Adds a new workbook to the Excel environment
        .Workbooks.Add

        ' Excel VBA Code goes here

        ' Causes the Excel window to become visible
        .Visible = True
    End With
End Sub

[NeoPa]

Be careful when developing code for controlling other applications. The temptation is always to develop the code in the actual application natively, then to port what has been developed across and paste into the controlling application within a standard shell of code to link across to it (EG. Inside a shell of code opening and closing an Excel workbook from Access). This is actually a pretty sound way to develop your code, but there is one pretty fundamental problem with this :

Many Office applications have their own default shortcuts that are active when, but only when, the code is run from the native application itself. For instance, some code that would work quite well from within Excel may include references to the Range() property. By default, this can be used alone to default to ActiveSheet.Ran ge(). Outside of Excel though, this default does not work (is not available). A reference to Range() (on its own) within Access code controlling a workbook will fail due to the absence of a reference object.

[NeoPa]

I just came across a problem using Access Automation while working on a process to manage a number of MDB files in Access 2003.

It seems that using .OpenCurrentDat abase in a controlled version of Access will sometimes fail with a spurious error. I wanted to log the error details but it seems it's a little unpredictable. I ran it without the setting and this time it worked fine.

To get around this you can set .UserControl = True before the call and it will work fine (Application.Use rControl Property).

PS. I subsequently noted the error which occurred again :
7866 - Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user.

[GKJR]

Many Office applications have their own default shortcuts that are active when, but only when, the code is run from the native application itself. (...)

This drove me crazy for the longest time. I never did figure out why Range() wasn't working.

Thanks for the article, this is really useful.

[hvsummer]

"Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate"

instead of reference to exactly application
can we use this method ? (for example: excel automaton)

Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")

we can create whatever object application that vba support without reference (VBA Window / Tools / References), isn't it ?

[zmbd]

Yes you can do something like that - late binding:\

Here's part of the code I use to late bind to Outlook:

'...
    Dim zobjOutlook As Object    'Outlook.Application  (Note dimensioned as Object)
    Dim zobjEmail As Object      'Outlook.MailItem     (Note dimensioned as Object)
    Dim zobjNameSpace As Object  'Outlook.NameSpace    (Note dimensioned as Object)
    Const zolMailItem As Long = 0    'For Late Binding
    Const zolFolderInbox As Long = 6 'For Late Binding
    Const zolFormatHTML As Long = 2  'For Late Binding
    Dim zstrSubject As String
    Dim zstrAddress As String
    
    On Error Resume Next
    Set zobjOutlook = GetObject(, "Outlook.Application")
'...

This is, however, a more advanced level of programing and IMHO Neopa was targeting a fairly large audience wherein many would not be comfortable using late-binding. :)

(and in fact, I tend to use the early binding method for development and then switch to late binding (as in the above) once I have things working - I love the intellisense :-D)

[hvsummer]

@zmbd: yeah, now I recall that we call those method early binding and late binding, thank you to remind me.
I don't have anything to say other than I'm lazy so I won't go to reference and tick on the right box when I can

Dim abc as object
set abc = createobject("Whatever.application")
'then slap on keyboard with a tons of code

:D

[zmbd]

lazy... lazier would be to use the intellisense to help write your code, this also helps to avoid syntax issues (along with the Option Explicit setting); thus, producing viable code is more efficient as there is less troubleshooting required and what is required is more quickly pinpointed by the debug/compile option. It's a simple matter to then add the createobject line, set explicates to object, and finally pull the constants out... the pros and cons would however, be a topic for another thread. ;-)

[hvsummer]

I still could not find the like button == want to give Neopa and you some Like :D

[NeoPa]

Intended Like accepted with gratitude :-)