automated email

[skylinekiller]

On my worksheet I ahve a cell which turns colors when the training dat
gets within 90 days, 30 days, and over 90 days. My question is, I wa
wondering if it's possible that once that cell falls with in the 9
days before training I want Excel to automaticall send an email or som
type of response. Is this possible

 

[DNF Karran]

Will leave you create the trigger for this but to send the mail:

Sub NewEmail()

Dim myOlApp
Dim myItem
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)

With myItem
.Subject = "A"
.To = "(e-mail address removed)"
.Body = "Hello," & Chr(13) & "Duncan here"
.Display
.Send
End With

End Sub

You will need to add a reference from excel to the MS Outlook 9.
object library before this code will run.

Dunca

 

[skylinekiller]

I have attached my file. When ever the training drops below 90 day
(blue), I want it to send an email out.

Where do I input the info you gave me

Attachment filename: training help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52987

 

[skylinekiller]

I created that in the macro, but How can I program it to send out a
email or emails when the cells fall in the blue

 

[DNF Karran]

The complete code in the attched is as follows.

The CheckMail sub will examine a worksheet function based check to se
if the reminders are up to date. If they are not if will cycle throug
the items due to see if they are behind or not. If they are it wil
call the NewEmail sub and send an e-mail. The user can check the mai
and if they are not happy, feed back to the check mail sub through
message box.

In the attached example the trigger for the CHeckMail sub is th
Workbook_open event- the calculate event was considered but is trippe
by the macro itself.

Duncan

Option Explicit
Option Private Module

Dim rngCurr As Range, boMailSent As Boolean

Public Sub CheckMail()

If Range("ItemsDue").Value <> 0 Then
For Each rngCurr In Range("Days_left")

If rngCurr.Value < 30 Then
If rngCurr.Offset(0, 2).Value = Empty Then
NewEmail (30)
If boMailSent = True Then Range("I"
rngCurr.Row).Value = "Sent " & Now()
End If

ElseIf rngCurr.Value < 90 Then
If rngCurr.Offset(0, 1).Value = Empty Then
NewEmail (90)
If boMailSent = True Then Range("H"
rngCurr.Row).Value = "Sent " & Now()
End If
End If

Next
End If

End Sub

Private Sub NewEmail(inDueDays As Integer)

Dim myOlApp
Dim myItem
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)

boMailSent = False

With myItem
.Subject = "Training due in " & inDueDays
.To = Range("B" & rngCurr.Row).Value
.body = "Dear" & Range("A" & rngCurr.Row).Value & Chr(13) & Chr(13
& "Your " & Range("C" & rngCurr.Row).Value & " training is due in "
inDueDays & " days."
.Display
End With

If MsgBox("Please confirm message details.", vbOKCancel, "Mail Send")
vbOK Then
boMailSent = True
myItem.send
End If

End Su

Attachment filename: training help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52992

 

[skylinekiller]

I was trying to use the code that you made. It appeared to work, bu
when I skipped lines to seperate the names and training, it wouldn'
run the macro. If I want to add another email address, can I just ad
it below in column "B"? How can I change what email address it send
to? Are columns H & I needed? I appreciate you time effort an
knowledge spent on helping me. Thank you

Attachment filename: vba by karrentraining helpemail.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=53039