Welcome to VBA Tips & Tricks.
All VBA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too
Happy reading

Pages

Sunday, April 27, 2008

There are multiple ways to have a template in Outlook for achieving tasks. Sometimes, you will require to hold some document variable in outlook like you do with Microsoft Word. The following example shows a simple way to add some text in drafts folder. This will not be visible to user.

The Properties are stored in Drafts Folder and can be retrieved using the following code

Sub GetData_From_StorageItem()

Dim oNs As Outlook.NameSpace

Dim oFL As Outlook.Folder

Dim oItem As Outlook.StorageItem

OnErrorGoTo OL_Error

oNs = Application.GetNamespace("MAPI")

oFld = oNs.GetDefaultFolder(olFolderDrafts)

oItem = oFld.GetStorage("My Appt Template", olIdentifyBySubject)

If oItem.Size <> 0 Then

MsgBox(oItem.UserProperties("My Footer"))

MsgBox(oItem.UserProperties("My Body"))

EndIf

ExitSub

OL_Error:

MsgBox(Err.Description)

Err.Clear()

EndSub

'StorageItem is a message object in MAPI that is always saved as a hidden item in the parent folder and stores private data for Outlook solutions.

'

'A StorageItem object is stored at the folder level, allowing it to roam with the account and be available online or offline.

'

'The Outlook object model does not provide any collection object for StorageItem objects. However, you can use Folder.GetTable to obtain a Table with all the hidden items in a Folder, when you specify the TableContents parameter as olHiddenItems. If keeping your data private is of a high concern, you should encrypt the data before storing it.

'

'Once you have obtained a StorageItem object, you can do the following to store solution data:

'

'Add attachments to the item for storage.

'Use explicit built-in properties of the item such as Body to store custom data.

'Add custom properties to the item using UserProperties.Add method. Note that in this case, the optional AddToFolderFields and DisplayFormat arguments of the UserProperties.Add method will be ignored.

'Use the PropertyAccessor object to get or set custom properties.

'

'The default message class for a new StorageItem is IPM.Storage. If the StorageItem existed as a hidden message in a version of Outlook prior to Microsoft Office Outlook 2007, the message class will remain unchanged. In order to prevent modification of the message class, StorageItem does not expose an explicit MessageClass property.

Here is an example for using the customized .NET Function in Excel VBA. Unfortunately EXcel VBA doesn't have a Array.Sort function. To overcome the shortcomings, we create our own function here in .Net and use the same in Excel VBA

Here are the steps:

Create a class library project in Visual Studio

Add a COM Class item (DND_SortArray in this example)

In the assembly information edit the title, company and provide a meaningful description. This would be seen in the References dialog in Excel

Navigate to the folder where your Office applications are installed, usually C:\ProgramFiles\Microsoft Office\Office12 for Office 2007 and run the Selfcert.exe program.

<>

SelfCert.exe is provided with Office 2000 and later. If it is not installed on your system, run Office Setup and install Office Tools  Digital Signature for VBA Projects.

Type a name for your certificate - just use your own name or product name and click OK to create your personal code-signing certificate.

<>

Now open the VBA project using Alt+F11 and select Tools ➪ Digital Signatureto open the dialog shown below. In this dialog, click the Choose button and select thecertificate and click OK twice to choose your certificate andsign your code project with that certificate.

Save your VBA project; then exit and restart Application (Excel / Word / Outlook).When you open your VBA project next time, you will be prompted to enable your macros inthe dialog shown below

You can use this dialog to trust your certificate by selecting the option to "Always trust macros from this publisher", which prevents this dialog from appearing again, or you can just enable the macros for that session.

Click the Trust All Documents from This Publisher button to trust your code-signing certificateand add it to the trusted publishers list. If you open the Trust Center dialog shownagain and click the Trusted Publishers area, you will now see your code-signing certificate listedas a trusted publisher.

There are many times when you need more than three sheets in a workbook. But when you use Workbooks.Add, it creates a New Workbook with three sheets (default for Microsoft Excel). Later you will add (or delete) the sheets for your use.

Here is another way to solve the problem. Use the Application's SheetsInNewWorkbook property to set the default no. of worksheets

The PropertyPage object is an abstract object. That is, the PropertyPage object in the Microsoft Outlook Object Library contains no implementation code. Instead, it is provided as a template to help you implement the object in Microsoft Visual Basic. This provides a predefined set of interfaces that Outlook can use to determine whether your custom property page has changed and to notify your program that the user has clicked the Apply or OK button. (If your custom property page does not rely on the Apply button, then you do not need to implement the PropertyPage object.)

A custom property page is an ActiveX control that is displayed by Outlook in the Options dialog box or in the folder Properties dialog box when the user clicks on the custom property page’s tab.

Many automation revolves around mails; you may want to trigger some process once a mail arrives in the InBox. The following code will help you extract the subject and body content of all mails in InBox

Sub Extract_Body_Subject_From_Mails()

Dim oNS As Outlook.NameSpaceDim oFld As Outlook.FolderDim oMails As Outlook.ItemsDim oMailItem As Outlook.MailItemDim oProp As Outlook.PropertyPage

Dim oNS As Outlook.NameSpaceDim oFld As Outlook.FolderDim oItems As Outlook.Items

On Error GoTo OL_Error

Set oNS = Application.GetNamespace("MAPI")

Set oFld = oNS.GetDefaultFolder(olFolderDrafts)

Set oItems = oFld.Items

If oItems.Count <> 0 Then MsgBox "There are some messages in the draft"End If

Exit SubOL_Error: MsgBox Err.Description Err.ClearEnd Sub

The program uses the MAPI Namespace and Draft DefaultFolder.

The only supported name space type is "MAPI". The GetNameSpace method is functionally equivalent to the Session property, which was introduced in Microsoft Outlook 98.

A Folder object that represents the default folder of the requested type for the current profile. If the default folder of the requested type does not exist, for example, because olFolderManagedEmail is specified as the FolderType but the Managed Folders group has not been deployed, then GetDefaultFolder will return Null (Nothing in Visual Basic).

The References property returns the set of references in a project. It is an accessor property (that is, a property that returns an object of the same type as the property name).

Sub Get_References_in_Project()

Dim i1For i1 = 1 To Application.VBE.ActiveVBProject.References.Count ' Get the Name of the Reference RefName = Application.VBE.ActiveVBProject.References(i1).name ' Get the Description of Reference RefDesc = Application.VBE.ActiveVBProject.References(i1).Description 'Returns a Boolean value indicating whether or not the Reference object points to a valid reference in the registry. Read-only. If Application.VBE.ActiveVBProject.References(i1).IsBroken = True Then RefBroken = True End IfNext i1

The ActiveVBProject property returns the project that is selected in the Project window or the project in which the components are selected. In the latter case, the project itself isn't necessarily selected. Whether or not the project is explicitly selected, there is always an active project. This name, description of the VB Project can be set as follows:

Application.GoTo can be used to scroll to a specific location in Excel sheet. Application.GoTo selects any range or Visual Basic procedure in any workbook, and activates that workbook if it’s not already active.

Sub Scroll_To_A_Location()

Application.GoTo Sheets(3).Range("A200"), True

End Sub

This method differs from the Select method in the following ways:

If you specify a range on a sheet that’s not on top, Microsoft Excel will switch to that sheet before selecting. (If you use Select with a range on a sheet that’s not on top, the range will be selected but the sheet won’t be activated).

This method has a Scroll argument that lets you scroll through the destination window.When you use the Goto method, the previous selection (before the Goto method runs) is added to the array of previous selections. You can use this feature to quickly jump between as many as four selections.The Select method has a Replace argument; the Goto method doesn’t.