Application Automation

NeoPa

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

Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate.
Set up an application object variable to use (You can use With...End With if you prefer).
Either :
1. Use CreateObject(Class) 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(Document, Class) to open a specific document.
Set the application's .Visible property. This determines if your changes are visible to the operator.
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.CurrentDB.Close).
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.
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.
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.

Posted by fauxanadu

Basic Outlook Automation (including Send New e-Mail)

Expand|Select|Wrap|Line Numbers

 ' 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

Expand|Select|Wrap|Line Numbers

 ' 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

Application Automation

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about