Document Variables in Outlook using VBA

Add Hidden Data using StorageItem in Outlook VBA

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.

Sub Create_Hidden_Data()

Dim oNs As Outlook.NameSpace

Dim oFld As Outlook.Folder

Dim oSItem As Outlook.StorageItem

On Error GoTo OL_Error

oNs = Application.GetNamespace("MAPI")

oFld = oNs.GetDefaultFolder(olFolderDrafts)

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

oSItem.UserProperties.Add("My Footer", olText)

oSItem.UserProperties("My Footer").Value = "VBADud - Samples & Tips on VBA"

oSItem.UserProperties.Add("My Body", olText)

oSItem.UserProperties("My Body").Value = "Hi" & vbCrLf & "Requesting a appointment with you for discussing..."

oSItem.Save()

Exit Sub

OL_Error:

MsgBox(Err.Description)

Err.Clear()

End Sub

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

On Error GoTo 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"))

End If

Exit Sub

OL_Error:

MsgBox(Err.Description)

Err.Clear()

End Sub

'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.

How to use .Net Array.Sort Function in VBA

Using Vb.Net Function in VBA

How to use a VB.Net DLL/TLB in Excel VBA


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:

1. Create a class library project in Visual Studio
2. Add a COM Class item (DND_SortArray in this example)
3. In the assembly information edit the title, company and provide a meaningful description. This would be seen in the References dialog in Excel
4. Add the code shown below:

_

1. Public Class DotNetDud_SortArray

   #Region "COM GUIDs"

   ' These GUIDs provide the COM identity for this class

   ' and its COM interfaces. If you change them, existing

   ' clients will no longer be able to access the class.

   Public Const ClassId As String = "93534c94-9fc1-4a54-b022-338fa7d454c1"

   Public Const InterfaceId As String = "03787ed3-bc65-41a1-9053-d37f390ff94b"

   Public Const EventsId As String = "34d12c14-8afd-44b7-a987-fc2f909724b6"

   #End Region

   ' A creatable COM class must have a Public Sub New()

   ' with no parameters, otherwise, the class will not be

   ' registered in the COM registry and cannot be created

   ' via CreateObject.

   Public Sub New()

   MyBase.New()

   End Sub

   Public Sub SortArray(ByRef arTemp() As String)

   Array.Sort(arTemp)

   End Sub

   End Class

Compile the Project. You will get a DLL and a TLB.

Now open the Excel VBA Editor and add the TLB file to References.

The following code will now use the SortArray .NET Function created

Sub Use_DotNet_Sort()

Dim Cls1 As DotNetDud_SortArray.DotNetDud_SortArray

Set Cls1 = New DotNetDud_SortArray.DotNetDud_SortArray

Dim arTemp(0 To 2) As String

arTemp(0) = "Bottle"

arTemp(1) = "Apple"

arTemp(2) = "Aaron"

Cls1.SortArray arTemp

Set Cls1 = Nothing

End Sub

1. 

See also :

Insert Array to Excel Range using VSTO

How to call a VSTO Addin Function from VBA

Create Custom Task Panes for Word using VSTO

Office host applications and their support for add-ins

Creating Digital Signature for VBA Project (Outlook VBA / Excel VBA/ Word VBA)

How to digitally sign a VBA Project



To create a code-signing certificate, follow these steps:

From Programs menu, choose Microsoft Office -> Microsoft Office Tools -> Digital Certificate for VBA Projects

(or)

Navigate to the folder where your Office applications are installed, usually C:\Program
Files\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 Signature
to open the dialog shown below. In this dialog, click the Choose button and select the
certificate and click OK twice to choose your certificate and
sign 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 in
the 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 certificate
and add it to the trusted publishers list. If you open the Trust Center dialog shown
again and click the Trusted Publishers area, you will now see your code-signing certificate listed
as a trusted publisher.

The same certificate can be used for multiple projects.

Show the Developer tab on Ribbon (Excel) or run in developer mode

Show the Developer tab on Ribbon (Excel) or run in developer mode

You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs.

Do the following in these Microsoft Office programs:

Word, Excel, or PowerPoint

Click the Microsoft Office Button , and then click Excel Options

Click Popular, and then select the Show Developer tab in the Ribbon check box.

Identify Browser Version using Excel VBA

Check Version of Browser using Excel VBA

The following code can be used to check the version of Internet Explorer or Netscape Navigator (whichever is the default browser):

Sub CheckWebOptions()

Dim wkbOne As Workbook

Set wkbOne = Application.Workbooks(1)

' Determine if IE5 is the target browser.
If wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE6 Then
MsgBox "The target browser is IE6 or later."
ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE5 Then
MsgBox "The target browser is IE5 or later."
ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE4 Then
MsgBox "The target browser is IE4 or later."
ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV4 Then
MsgBox "Microsoft Internet Explorer 4.0, Netscape Navigator 4.0, or later."
ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV3 Then
MsgBox "Microsoft Internet Explorer 3.0, Netscape Navigator 3.0, or later."
Else
MsgBox "The target browser is not in the given list"
End If

End Sub

See also:

Search Webpages using VBA / Search Text in Google using Excel VBA / Programmaticaly search text in Google

Check If Workbook is Saved using Excel VBA

Check Saved Status of Workbook using Excel VBA

Use Saved property of Workbook to check the status. Saved returns True if no changes have been made to the specified workbook since it was last saved

Function IsDirty(ByRef OWB As Workbook) As Boolean

If OWB.Saved = False Then
IsDirty = True
End If

End Function

At times, the workbook would have been created and never saved. In that case, you can use the Path property to identify if it was saved at all

Function IsNeverSaved(ByRef OWB As Workbook) As Boolean

If OWB.Path = "" Then

IsNeverSaved = True
End If

End Function

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word

Set No Of Sheets in a Workbook using Excel VBA

Change default number of Sheets using Excel VBA

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

Sub Set_No_Of_Sheets()

' -----------------------------------------
' coded for vbadud.blogspot.com by shasur
' -----------------------------------------


MsgBox "No of sheets in a blank workbook is : " & Application.SheetsInNewWorkbook

' set the Workbook for One Sheet
Application.SheetsInNewWorkbook = 1

' Workbook with only one sheet will be added
Workbooks.Add


' Reset the Workbook for Five Sheets
Application.SheetsInNewWorkbook = 3

End Sub

Here is a way suggested by Jon Peltier, Microsoft Excel MVP (http://PeltierTech.com)

Workbooks.Add([Template])

Template is optional, but if you use one of these constants, it creates a workbook with a single sheet of the type defined by the contant: Many thanks Jon for your suggestion

xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or
xlWBATWorksheet
 
This command then creates a workbook with a single worksheet:

Workbooks.Add xlWBATWorksheet

Many thanks Jon for your valuable suggestion

Check Out-Of-Office Status in Outlook using VBA

Extract Out of Office Status using Outlook VBA

Checking out of office status using VBA can be done using PropertyAccessor. The following code uses PropertyAccessor to extract the information

Unlike NamedProperties, PropertyAccessor uses Schema to get the property

Sub Check_Out_Of_Office()

Dim oNS As Outlook.NameSpace
Dim oStores As Outlook.Stores
Dim oStr As Outlook.Store
Dim oPrp As Outlook.PropertyAccessor

Set oNS = Application.GetNamespace("MAPI")
Set oStores = oNS.Stores
For Each oStr In oStores
If oStr.ExchangeStoreType = olPrimaryExchangeMailbox Then
Set oPrp = oStr.PropertyAccessor
MsgBox oPrp.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x661D000B")

End If
Next


End Sub

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.

Extract Subject and Body of eMail through Outlook VBA

Extract eMail Data (Subject & Body) Programatically using Outlook VBA

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.NameSpace
Dim oFld As Outlook.Folder
Dim oMails As Outlook.Items
Dim oMailItem As Outlook.MailItem
Dim oProp As Outlook.PropertyPage

Dim sSubject As String
Dim sBody

On Error GoTo Err_OL

Set oNS = Application.GetNamespace("MAPI")
Set oFld = oNS.GetDefaultFolder(olFolderInbox)
Set oMails = oFld.Items

For Each oMailItem In oMails
sBody = oMailItem.Body
sSubject = oMailItem.Subject 'This property corresponds to the MAPI property PR_SUBJECT. The Subject property is the default property for Outlook items.
Next

Exit Sub
Err_OL:
If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Err.Clear
Resume Next
End If
End Sub

The Subject property is the default property for Outlook items.

Check Outlook Drafts folder for messages using Outlook VBA

Programatticaly check for draft messages using Outlook VBA

Sub Check_Drafts_Folder()

Dim oNS As Outlook.NameSpace
Dim oFld As Outlook.Folder
Dim 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 Sub
OL_Error:
MsgBox Err.Description
Err.Clear
End 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).

Disable Drag & Drop of Cells in Excel VBA

Enable or Disable dragging and dropping cells in Excel

Sub Disable_Cell_Drag_Drop()

Application.CellDragAndDrop = False

End Sub

Application.CellDragAndDrop = True enables drag & drop

Get Shared Name of a Drive using FileSystemObject

Convert Drive Name to Sharename using VBA

Here is a simple function, which uses FileSystemObject's ShareName function to get the shared name of the drive

Public Function ConvertDrive2ServerName(ByVal sFullPath As String) As String

' --- Replaces the DriveName with ShareName in a given string

Dim FSO As FileSystemObject
Dim sDrive As String
Dim drvName As Drive
Dim sShare As String

On Error GoTo Err_Trap

Set FSO = New FileSystemObject

sDrive = FSO.GetDriveName(sFullPath)
Set drvName = FSO.GetDrive(sDrive)
sShare = drvName.ShareName

If LenB(sShare) <> 0 Then
ConvertDrive2ServerName = Replace(sFullPath, sDrive, sShare, 1, 1, vbTextCompare)
Else
ConvertDrive2ServerName = sFullPath
End If
If Not FSO Is Nothing Then Set FSO = Nothing

' ---------------------------------------
' Error Handling
' ---------------------------------------
Err_Trap:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Function

Get Device Name using QueryDosDeviceW

Private Declare Function QueryDosDeviceW Lib "kernel32.dll" ( _
ByVal lpDeviceName As Long, _
ByVal lpTargetPath As Long, _
ByVal ucchMax As Long _
) As Long
Const MAX_PATH = 260


Public Function GetNtDeviceName( _
ByVal sDrive As String) As String

Dim bDrive() As Byte
Dim bResult() As Byte
Dim lR As Long
Dim sDeviceName As String

If Right(sDrive, 1) = "\" Then
If Len(sDrive) > 1 Then
sDrive = Left(sDrive, Len(sDrive) - 1)
End If
End If
bDrive = sDrive

ReDim Preserve bDrive(0 To UBound(bDrive) + 2) As Byte
ReDim bResult(0 To MAX_PATH * 2 + 1) As Byte

lR = QueryDosDeviceW(VarPtr(bDrive(0)), VarPtr(bResult(0)), MAX_PATH)
If (lR > 2) Then
sDeviceName = bResult
sDeviceName = Left(sDeviceName, lR - 2)
GetNtDeviceName = sDeviceName
End If

End Function

Sub Trial()
MsgBox GetNtDeviceName("p:")
End Sub

Check Protection of VBA project using Excel VBA

Check state of protection of VBProject

Returns a value indicating the state of protection of a project.

Sub Is_Project_Protected()

If Application.VBE.ActiveVBProject.Protection = vbext_pp_locked Then
MsgBox "Protected"
End If

End Sub

Check Default Reference using Excel VBA

BuiltIn property returns a Boolean value indicating whether or not the reference is a default reference that can't be removed.

Sub If_Reference_Is_Default()

Dim i1
For i1 = 1 To Application.VBE.ActiveVBProject.References.Count

'Returns a Boolean value indicating whether or not the reference is a default reference that can't be removed.
If Application.VBE.ActiveVBProject.References(i1).BuiltIn = True Then
MsgBox "Default Reference : " & Application.VBE.ActiveVBProject.References(i1).name
Else
MsgBox "Not Default Reference : " & Application.VBE.ActiveVBProject.References(i1).name
End If
Next i1

End Sub

Get References of VBA Project Programmtically

Extract References of a VBA Project

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 i1
For 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 If
Next i1

End Sub

Set VBProject Properties using VBA

Set the Project Name of VBA project programmatically

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:

Sub Get_The_PRoject_Name()

Application.VBE.ActiveVBProject.name = "MyVBAProject"

Application.VBE.ActiveVBProject.Description = "This is My Personal VBA Project"

End Sub

Scroll to a position in Excel using VBA

Scroll Window using Excel VBA

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.

Hide Excel Status Bar / Show Excel Status Bar using VBA

Show/Hide Application Status bar using Excel VBA

Here is the simple way to hide/show the status bar in Excel

Sub Hide_Status_Bar()

Application.DisplayStatusBar = False


End Sub

Sub Show_Status_Bar()

Application.DisplayStatusBar = True


End Sub


Also you need to clear the contents of the status bar.

Sub Clear_Status_Bar()

Application.StatusBar = False

(or)

Application.StatusBar = ""

End Sub

Retrieving Special Folders using FileSystemObject

There are many ways to get the special folders like Systems folder, Temporary folder etc. One common method is to use the Environ. Here you can achieve the same using FileSystemObject

Sub Get_Special_Folders()

' Uses File System Object
' Need to have reference to Microsoft Scripting Runtime

On Error GoTo Show_Err

Dim oFS As FileSystemObject
Dim sSystemFolder As String
Dim sTempFolder As String
Dim sWindowsFolder As String

Set oFS = New FileSystemObject

' System Folder - Windows\System32
sSystemFolder = oFS.GetSpecialFolder(SystemFolder)

' Temporary Folder Path
sTempFolder = oFS.GetSpecialFolder(TemporaryFolder)

' Windows Folder Path
sWindowsFolder = oFS.GetSpecialFolder(WindowsFolder)


If Not oFS Is Nothing Then Set oFS = Nothing

Show_Err:

If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End If
End Sub


For this you need to reference Microsoft Scripting Runtime


See also:

How to retrieve Application Data Folder using C# (.NET)
How to get the full path of cookies folder using C# (.NET)
Selecting a Folder in VB.Net
How to retrieve MyDocuments Folder using C# (.NET)
How to retrieve Desktop Folder using C# (.NET)