Basic examples for Outlook VBA

This post contains few basic examples for VBA in Microsoft Outlook. To get developer tab visible, File -> Option -> Customize Ribbon -> Developer.

Let see a simple example to send an email. Below is the basic code which will generate and shoot the email. In visual Basic Editor, use ThisOutlookSession or any other user modules.

Private Sub SendEmail()
Dim olObj, mlObj
Set olObj = CreateObject("Outlook.Application") 'declaring object
Set mlObj = olObj.CreateItem(0) 'declaring an email item
With mlObj
.To = "me@mydomain.com" 'Give your email
.Subject = "Testmail" ' Give the subject
.Body = "Testmail" ' Give the Body
.Send 'command to send the email
End With
Set olObj = Nothing 'Clear the memory
Set mlObj = Nothing
End Sub

Send automatic email using VBA

Here I have shared the simple code for sending an automatic email using Macros. It can be excuted in all primary products of the Microsoft. you can send emails to recipient from excel data too. Basically To address, subject and HTMLBody are the apex elements. assign those values and shoot the emails using below simple syntax

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "Macroraman@example.com" 'To address
.CC = ""
.BCC = ""
.Subject = "Assignment"
.HTMLBody = "Hi"
'.Attachments.Add ActiveWorkbook.FullName (for active book)
'.Attachments.Add ("D:\Book1.xls") (for external book)
'.Display (to see the message window)
'.Save (to save as Draft)
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

You may face one dificulty when you run the code due to your Outlook security setting. It may ask your acceptance through popup. to avoid such popups follow below steps

For older window versions
Tools menu -> Options -> Security tab
uncheck and save "Warn me when other applications try to send mail as me" check box.

For latest Window versions
File - > Options -> Trust Center -> Trust Center settings -> Programmatic Access
Uncheck and save warning option

In office/secured environment, If you are unable to change the security settings, you can use less equivalent code instead of ".Send"
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"

which means your are executing as manual operation

AppsScript - Macro - Javascript

Google Apps script and VBA Macro and Javascript are the powerful scripts which work on worksheets. Depending on the usage of the worksheet we can choose any of the above scripts. let say, If you need online automation then Appscript would be the best of the choice. For local automation Macro and Javascript play vital role. Many of us are not sure about similarities on these. In this series, I would like to share the similarities on these scripts. In this post let see how to read the spreadsheets

For reading the worksheet:
Google Apps Script for reading another spread sheet

  var myBook = spreadsheetApp.openById("0Aoh6e6FJ10dSGc"); // open id is the key string able to find in the spread sheet url 

var mySheet = myBook.getSheetByName("sheet1");   //name of the sheet
var myRange = mySheet.getRange(1,1).getValue();   // range represent first row first column
   

VBA Macro Script for reading another Worksheet

Dim myBook as Excel.workbook
set myBook = Getobject("C:\sample\book1.xls")
myRange =  myBook.worksheets(1).Cells(1,1).Value
myBook.Close 'make sure this needs to be closed, if it is declared early.

JavaScript for reading another Worksheet

var myBook = new ActiveXObject("Excel.Application").workbooks.open("c:\sample\book1.xls");
var mySheet = myBook.Sheets(1);
var myRange = mySheet.cells(1,1).Value;  //ActiveXObject is supported by IE only & other browsers are not supportable

  myRange is the Read value 


on simple way we can compare these as
SpreadsheetApp.openById("0Aoh6e6FJ10dSGc").getSheetByName("sheet1").getRange(1,1).getValue());

new ActiveXObject("Excel.Application").workbooks.open("c:\sample\book1.xls").Sheets(1).cells(1,1).Value;

Getobject("C:\sample\book1.xls").worksheets(1).Cells(1,1).Value