Emailing in Excel

  • Thread starter Thread starter ccdubs
  • Start date Start date
C

ccdubs

Hi all,

Here's a question from a novice.

Is it possible to write a macro that will send an email to a specified email
address when a cell reaches a certain value?

Thanks for any help.
 
ccdubs,

Sure. If you have Outlook, you can do it using the code below. It
will email a message when the value in B6 become greater than one.
Cell B7 is used as a flag cell to keep the program from emailing more
than once.

Copy the first sub, right click on the sheet tab, select view code,,
then paste the code into the window that appears. Copy the second sub
into a normal code module. You need to set a reference to Outlook from
your project, and if you have Outlook 2000 with the security patch or
anything newer, then you will need to use some security workarounds or
your email won't get sent. Read more at:

http://www.slipstick.com/outlook/esecup.htm#autosec

One utility that you could use in that case, written by an Outlook
MVP, is at
http://www.dimastr.com/redemption/

HTH,
Bernie

Private Sub Worksheet_Calculate()
If Range("B6").Value > 1 And Range("B7").Value <> "Contacted" Then
Call EmailNow
Range("B7").Value = "Contacted"
End If
End Sub

Sub EmailNow()
Dim ol As Object, myItem As Object
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "(e-mail address removed)"
myItem.Subject = "Check that workbook..."
myItem.Body = "Hello Me, " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Could you check file.xls values?
" & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Thanks for doing that." & Chr(13)
& Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Me" & Chr(13)
myItem.Send
Set ol = Nothing
Set myItem = Nothing
End Sub
 
Many thanks Bernie, I will give this a try.

Bernie Deitrick said:
ccdubs,

Sure. If you have Outlook, you can do it using the code below. It
will email a message when the value in B6 become greater than one.
Cell B7 is used as a flag cell to keep the program from emailing more
than once.

Copy the first sub, right click on the sheet tab, select view code,,
then paste the code into the window that appears. Copy the second sub
into a normal code module. You need to set a reference to Outlook from
your project, and if you have Outlook 2000 with the security patch or
anything newer, then you will need to use some security workarounds or
your email won't get sent. Read more at:

http://www.slipstick.com/outlook/esecup.htm#autosec

One utility that you could use in that case, written by an Outlook
MVP, is at
http://www.dimastr.com/redemption/

HTH,
Bernie

Private Sub Worksheet_Calculate()
If Range("B6").Value > 1 And Range("B7").Value <> "Contacted" Then
Call EmailNow
Range("B7").Value = "Contacted"
End If
End Sub

Sub EmailNow()
Dim ol As Object, myItem As Object
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "(e-mail address removed)"
myItem.Subject = "Check that workbook..."
myItem.Body = "Hello Me, " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Could you check file.xls values?
" & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Thanks for doing that." & Chr(13)
& Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Me" & Chr(13)
myItem.Send
Set ol = Nothing
Set myItem = Nothing
End Sub
Click to expand...
 
Back
Top