Automatically run a Macro from an If Statement in VBA

[Kyle.Robinson]

Hey guys, kinda new to excel and working with macro's. Here is what I have so far.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
If ThisWorkbook.Sheets("my text here").Range("C14").Value = "Success" Then

Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 31 'data in rows 2-31

' Get the email address
Email = Cells(r, 5)

' Message subject
Subj = "my text here"

' Compose the message
Msg = ""
Msg = Msg & "my text here " & Cells(r, 4) & "," & vbCrLf & vbCrLf
Msg = Msg & "my text here "

Msg = Msg & Cells(r, 6).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "my text here" & vbCrLf
Msg = Msg & "my text here"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End If
End Sub



I am trying to send an automated message any time the value in cell C14-C43 = "Success" or "Absent" or "Skip" to an email on the list on the Data worksheet.

So for instance I have:
D2-D31 |E2-E31 | F2-F31
Name | Email | Message

and I only want to have it send the email if the cell above equals one of the three, success, absent or skip.

I'd also like to have outlook mark the mail as high priority.

Help is much appreciated!

-kyle