- 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 :- 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).
- 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 :
- If you want to close down the application simply close it (Call accApp.Quit).
- 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).
- 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.Application
object called appXL
, you would refer to it as appXL.ActiveSheet.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)
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
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