Google Search Using VBA

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

This code requires Microsoft Internet Controls reference (Tools > References from code window)

The following function will search for given data in google and save the search as an HTML File. The Timeout parameter is set to 1 min.

Option Explicit

Private ieBrowser As InternetExplorer
Private Const sSite As String = "http://www.google.com/search?q=" ' Change this Appropriately
Private Const sProofPath As String = "d:\temp\" ' Path to Save Searched Pages



Sub Check_Data_From_Google(ByVal sData As String, ByRef sReturn As String, ByRef sSavePath As String)

'Requires Microsoft Internet Controls reference (Tools > References from code window)
Dim sSearchString As String ' Combination of Google Search String + Data
Dim dtStartTime As Date ' Start Time
Dim dtCurrentTime As Date ' Current Time
Dim iMaxWaitTime As Integer ' Maximum waiting time (in Secs)

Dim sDocText ' WebPage as Text
Dim sDocHTML ' WebPage as HTML

On Error GoTo Err_Clearer
' ---------------------------------
' Build the Search String
' ---------------------------------
sSearchString = sSite & sData

' ---------------------------------
' Start Time
' ---------------------------------
Init_IE
dtStartTime = Now
iMaxWaitTime = 60 'Seconds to be waited
ieBrowser.Navigate (sSearchString)

' ieBrowser.Visible = True


Do While ieBrowser.ReadyState <> READYSTATE_COMPLETE 'wait for page to load
DoEvents
dtCurrentTime = Now
' ---------------------------------
' Exit Process if it is taking long time
' ---------------------------------
If DateDiff("s", dtStartTime, dtCurrentTime) > iMaxWaitTime Then sReturn = "TimeOut": Exit Sub
Loop

' Assign the Webpage Results to Variable
sDocText = ieBrowser.Document.documentElement.innertext
sDocHTML = ieBrowser.Document.documentElement.innerhtml

If InStr(sDocText, "did not match any documents") <> 0 Then
sReturn = "NotFound"
Else
If InStr(1, sDocText, sData) <> 0 Then
sReturn = "Found"
Else
sReturn = "NotFound"
End If
End If

sSavePath = sProofPath & sData & "_" & sSpec & ".html"
sSavePath = ClearCharacters(sSavePath)
Open sSavePath For Output As 1
Print #1, sDocHTML
Close #1

Destroy_IE

' -----------------------------
' Error Handler
' -----------------------------
Err_Clearer:
If Err <> 0 Then
Err.Clear
Resume Next
End If

End Sub

Sub Destroy_IE()

On Error GoTo ReInit_IE
ieBrowser.Quit
If Not ieBrowser Is Nothing Then Set ieBrowser = Nothing

Exit Sub

ReInit_IE:

End Sub


Sub Init_IE()

On Error GoTo ReInit_IE

Set ieBrowser = GetObject(, "InternetExplorer.Application")
Exit Sub

ReInit_IE:

Set ieBrowser = CreateObject("internetexplorer.application")
Application.Wait DateAdd("n", 1, Now) ' Wait for one/Two minutes to Start the Browser
End Sub


Function ClearCharacters(ByVal sDirtyString As String) As String

Dim arUnWantedCharacter(1 To 6) As String
Dim IsClear As Boolean
Dim i As Integer
Dim strCleanString As String
Dim j As Integer

arUnWantedCharacter(1) = "/"
arUnWantedCharacter(2) = "/"
arUnWantedCharacter(3) = "?"
arUnWantedCharacter(4) = "*"
arUnWantedCharacter(5) = "["
arUnWantedCharacter(6) = "]"

IsClear = True

strCleanString = vbNullString
For i = 1 To UBound(arUnWantedCharacter)
If InStr(1, sDirtyString, arUnWantedCharacter(i)) Then
IsClear = False
For j = 1 To Len(sDirtyString)
If Mid$(sDirtyString, j, 1) <> arUnWantedCharacter(i) Then
strCleanString = strCleanString & Mid$(sDirtyString, j, 1)
End If
Next j
sDirtyString = strCleanString
End If
Next i

If IsClear = True Then strCleanString = sDirtyString

Finally:


ClearCharacters = strCleanString

End Function


Excel VBA save the search as an HTML File. The function uses innerhtml to get the searched webpage and saves it in the mentioned location

Save Webpage using VBA, VBA Save Webpage (without images) using Excel VBA


See also http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba.html

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

Solution for 1004 -- The file could not be accessed while saving the File

1004 Microsoft Office Excel cannot access the file 'C:\temp'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

1004 -- The file could not be accessed. Try one of the following:

• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: < > ? [ ] : or *
• Make sure the file/path name doesn't contain more than 218 characters.

This error occurs because of unwanted characters in the File. The following function would help in removing those characters:



Function ClearCharacters(ByVal sDirtyString As String) As String

Dim arUnWantedCharacter(1 To 6) As String
Dim IsClear As Boolean
Dim i As Integer
Dim strCleanString As String
Dim j As Integer

arUnWantedCharacter(1) = "\"
arUnWantedCharacter(2) = "/"
arUnWantedCharacter(3) = "?"
arUnWantedCharacter(4) = "*"
arUnWantedCharacter(5) = "["
arUnWantedCharacter(6) = "]"

IsClear = True

strCleanString = vbNullString
For i = 1 To UBound(arUnWantedCharacter)
If InStr(1, sDirtyString, arUnWantedCharacter(i)) Then
IsClear = False
For j = 1 To Len(sDirtyString)
If Mid$(sDirtyString, j, 1) <> arUnWantedCharacter(i) Then
strCleanString = strCleanString & Mid$(sDirtyString, j, 1)
End If
Next j
sDirtyString = strCleanString
End If
Next i

If IsClear = True Then strCleanString = sDirtyString

Finally:

ClearCharacters = strCleanString

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

Using Excel as Database using VBA (Excel ADO)

VBA ADO Code for using Excel as Database


Though many database systems have come , still there is a need to use Excel as Backend database. The reasons might be many -- you get Excel sheets as a Report and do not want to import that into Access or SQL Server

Here is a simple code that will allow you to do exactly that

Sub Excel_ADO()

Dim cN As ADODB.Connection '* Connection String
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim i1 As Long
Dim lMaxRow As Long '* Last Row in the Sheet
Dim iRevCol As Integer '*
Dim i3 As Integer

On Error GoTo ADO_ERROR

Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
cN.ConnectionTimeout = 40
cN.Open

Set RS = New ADODB.Recordset

lMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iRevCol = 2

For i1 = 2 To lMaxRow

Application.StatusBar = i1
sQuery = "Select * From [Sheet1$]"

RS.ActiveConnection = cN
RS.Source = sQuery
RS.Open

If RS.EOF = True And RS.BOF = True Then
GoTo TakeNextRecord
End If

RS.MoveFirst
Do Until RS.EOF = True
sName = Trim$(RS("Name").Value)
sAge = Trim$(RS("Age").Value)
' Do some operations
RS.MoveNext
Loop


TakeNextRecord:
If RS.State <> adStateClosed Then
RS.Close
End If
Next i1

If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing

ADO_ERROR:
If Err <> 0 Then
Debug.Assert Err = 0
MsgBox Err.Description
Resume Next
End If

End Sub

All the code remains the same as Access ADO code except the change in connection string. cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"

See also:

Opening Comma Separate File (CSV) through ADO

Query Table with Excel as Data Source

Create Database with ADO / ADO Create Database

ADO connection string for Excel

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Macro to Create a Pivot Table from Existing Pivot Cache

Many times you will have a Pivot Table created from a pivot cache and you have a requirment to create another pivot table from the same data. In such cases, instead of creating a new cache, you can use the existing pivot cache to create another pivot table. This will save a good amount of memory too.

Sub Create_Pivot_Table_From_Existing_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

If oWS.PivotTables.Count <>Then Exit Sub

oPC = oWS.PivotTables(1).PivotCache

oPT = oPC.CreatePivotTable(oWS.[J1], "Pivot From Existing Cache", True)

oPT.AddFields(oPT.PivotFields("Item").Name)

oPT.AddDataField(oPT.PivotFields("Customer"), "Quantity", xlCount)

End Sub

Here we are checking if any Pivot Table exist in that particular sheet; if it exists we are using the same cache of the pivot table to create another pivot table

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Create Pivot Table using Excel VBA

Macro to Create a Pivot Table from New Pivot Cache

Sub Create_Pivot_Table_From_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, oWS.UsedRange)

oPT = oPC.CreatePivotTable(oWS.[D20], "Pivot From Cache", True)

oPT.AddFields(oPT.PivotFields("Item").Name, oPT.PivotFields("Customer").Name)

oPT.AddDataField(oPT.PivotFields("Qty"), "Quantity", xlSum)

End Sub

PivotCache represents the collection of memory caches from the PivotTable reports in a workbook. Each memory cache is represented by a PivotCache object. The above example creates a pivotcache from existing data and then using the cache a pivot table is created

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Create Pivot Table from VBA using Wizard

Use PivotTable Wizard Programmatically using Excel VBA

You can use PivotTableWizard method of Worksheet object to create a new PivotTable report. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache

Sub Create_Pivot_Table_Using_Wizard()

Dim oPT As PivotTable

Dim oWS As Worksheet

On Error GoTo Err_PT

If ActiveSheet.Type = xlWorksheet Then

oWS = ActiveSheet

oPT = oWS.PivotTableWizard(xlDatabase, oWS.UsedRange, oWS.Range("A20"), "PivotFromWizard")

oPT.AddFields("Item", , "Qty")

oPT.AddDataField(oPT.PivotFields("Qty"), "Quanity", xlSum)

oPT.TableRange1.Select()

End If

Exit Sub

Err_PT:

MsgBox(Err.Description)

Err.Clear()

End Sub

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Creating a Command Button on Sheet using Excel VBA

Adding an OLE Object (Command Button) to a Worksheet using Excel VBA

Sub Create_Command_Button_2007()

'

' Creates a Command button and Positions it

' Written by Shasur for http://vbadud.blogspot.com

Dim oOLE As OLEObject

' Add a Command Button

oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120)

oOLE.Interior.Color = vbRed

' Move and Size with cells

oOLE.Placement = XlPlacement.xlMoveAndSize

oOLE.Object.Caption = "Click Me..."

End Sub

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.

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.