Name:
Location: Bangalore, Karnataka, India

Sunday, May 25, 2008

Sending an email from Microsoft excel-sheet based on certain cell condition

1. Open excelsheet.
2. a) Go to Tools->Macro->Visual Basic Editor (or Type Alt-F11)
b) In Visual Basic Editor, go to Tools->References and check (select) "Microsoft Outlook 11.0 Library"
3. Find out "Microsoft Excel Objects" in left panel
4. Double-click on the sheet you want to set an alert for.
5. Lets say you want to set an alert for cell E7 if value is less than 0, use below code.

Private Sub Worksheet_Calculate()
'from within any vba module, choose Tools, References,
'and make sure the Microsoft Outlook x.x Object Library
'is checked
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
If Range("e7") < 0 Then
Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.Subject = "Alert from Excel"
.Body = "Cell E7 is less than 0"
.To = "yourID@yahoo.com"
.Send
End With
objOutlook.Quit
End If
End Sub

6. Also, you may need to go to Tools->Macro->Security and set security level to "low" to test it if it doesnt work out with "high" security level.
Hope this will work out.

1 Comments:

Anonymous Anonymous said...

Nice Job
It is useful for Outlook User who have problem with outlook mail services.

10:08 PM  

Post a Comment

<< Home