SendMail Method in VBA

The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients.

As the code below specifies the Active Workbook the code is best stored in your Personal.xls

Sub SendActiveWorkbook()

ActiveWorkbook.SendMail _

Recipients:="chithu.palaniappan@gmail.com", _

Subject:="Try Me " & Format(Date, "dd/mmm/yy")

End Sub

If you only wish to send one Worksheet from a Workbook we can use the method shown below. It creates a new Workbook housing

ONLY the sheet we copy. It then sends the 1 sheet Workbook as an attachment, then closes the new Workbook without saving.

Send mail in JavaScript

function TriggerOutlook(to,project_name,from)
{
var body = "Access Required for eTracker project";
var subject = "Access Required for eTracker project :" + ;
window.location.href = "_mailto:?body="+body+"&subject="+subject;
}

Send mail in VBScript

Sub SendMailOutlook(aTo, Subject, TextBody, aFrom)
Dim Outlook 'As New Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim Message 'As Outlook.MailItem
Set Message = Outlook.CreateItem(olMailItem)
With Message
.Subject = Subject
.HTMLBody = TextBody
.Recipients.Add (aTo)
Const olOriginator = 0
.Send
End With
End Sub

Auto open function

Auto open function to automatically executing the macro function.

Private Sub Auto_Open()
MsgBox "This is auto open macro in Module"
End Sub

Open workbook trigger events

Private Sub Workbook_Open()
MsgBox "This is auto open macro in This workbook"
End Sub

Viewing Many Workbooks

To programmatically refer to a workbook, access the Item property and pass either the index or the file name of the workbook

to it. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Workbooks.Item (2)
End Sub


After referring to a workbook, if you want to perform an action on it, you must get a reference to it. To do this, declare a

Workbook variable and assign the calling Item() to it. This would be done as follows:

Private Sub cmdSelectWorkbook_Click()
Dim SchoolRecords As Workbook
Set SchoolRecords = Workbooks.Item(2)
End Sub

Microsoft Excel as an MDI

To support the ability the programmatically access a workbook, the Workbook class is equipped with a method named Activate.

Its syntax is:

Workbook.Activate()This method takes no argument. Therefore, to call it, you can get a reference to the workbook you want to

access, then call the Activate() method. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Item(2)
SchoolRecords.Activate
End SubYou can also do this with less code by applying the index directly to the Workbooks collection. Here is an example:

Private Sub cmdSelectWorkbook_Click()
Workbooks(2).Activate
End Sub

Opening Workbooks

The ability to programmatically open a workbook is handled by the Workbooks collection. To support this, the Workbooks class

is equipped with a method named Open. Its syntax is:

Workbooks.Open(FileName,
UpdateLinks,
ReadOnly,
Format,
Password,
WriteResPassword,
IgnoreReadOnlyRecommended,
Origin,
Delimiter,
Editable,
Notify,
Converter,
AddToMru,
Local,
CorruptLoad)

FileName is the only required argument. When calling this method, you must provide the name of the file or its path.

Saving a Workbook

To visually save a workbook, you can click the Office Button and click Save. You can also press Ctrl + S. If the document was

saved already, it would be saved behind the scenes without your doing anything else.

To support the ability to programmatically save a workbook, the Workbook class is equipped with a method named Save. Its

syntax is:

Workbook.Save()

As you can see, this method takes no argument. If you click the Office Button and click Save or if you call the

Workbook.Save() method on a work that was not saved yet, you would be prompted to provide a name to the workbook.

To save a workbook to a different location, you can click the Office Button, position the mouse on Save As and select from

the presented options. You can also press F12. To assist you with programmatically saving a workbook, the Workbook class is

equipped with a method named SaveAs. Its syntax is:

Workbook.SaveAs(FileName,
FileFormat,
Password,
WriteResPassword,
ReadOnlyRecommended,
CreateBackup,
AccessMode,
ConflictResolution,
AddToMru,
TextCodepage,
TextVisualLayout,
Local)

The first argument is the only required one. It holds the name or path to the file. Therefore, you can provide only a name of

the file with extension when you call it. Here is an example:

Private Sub cmdNewWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Add

SchoolRecords.SaveAs "SchoolRecords.xlsx"
End Sub

If you provide only the name of a file when calling this method, the new workbook would be saved in the current directory or

in My Documents (Documents in Windows Vista). If you want, an alternative is to provide a complete path to the file.

Saving Workbooks

After working on a new workbook, you can save it. After programmatically creating a workbook, if you want to keep it when the

user closes Microsoft Excel or when the computer shuts down, you must save it. You and the user have the option of using the

Save As dialog box.

The Default File Location:-

To support the ability to programmatically change the default folder, the Application class is equipped with a property named

DefaultFilePath. Therefore, to programmatically specify the default folder, assign its string to the

Application.DefaultFilePath property. Here is an example:

Private Sub Exercise()

Application.DefaultFilePath = "C:\chidambaram\document and settings\my documents\"

End Sub

When this code has executed, the Default File Location of the Excel Options dialog box would be changed.

Creating a Workbook

A workbook is an object of type Workbook and it is part of the Workbooks collection. To support the ability to create a new

workbook, the Workbooks collection is equipped with a method named Add. Its syntax is:

Workbooks.Add(Template) As WorkbookYou start with the Workbooks class, a period, and the Add method. This method takes only

one argument but the argument is optional. This means that you can call the method without an argument and without

parentheses. Here is an example:

Private Sub cmdNewWorkbook_Click()
Workbooks.Add
End Sub

When the method is called like this, a new workbook would be created and presented to you. After creating a workbook, you may

want to change some of its characteristics. To prepare for this, notice that the Add() method returns a Workbook object.
Therefore, when creating a workbook, get a reference to it. To do this, assign the called method to a Workbook variable. Here

is an example:

Private Sub cmdNewWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Add
End Sub

After doing this, you can then use the new variable to change the properties of the workbook.

Workbooks Fundamentals

In the VBA language, a workbook is an object that belongs to a collection called Workbooks. Each workbook of the Workbooks

collection is an object of type Workbook, which is a class.

Each workbook of the Workbooks collection can be identified using the Item property. To programmatically refer to a workbook,

access the Item property and pass either the index or the file name of the workbook to it.

After referring to a workbook, if you want to perform an action on it, you must get a reference to it. To do this, declare a

Workbook variable and assign the calling Item() to it. This would be done as follows:

Private Sub cmdSelectWorkbook_Click()
Dim SchoolRecords As Workbook

Set SchoolRecords = Workbooks.Item(2)
End Sub