Send Email if Criteria is met in Excel to Outlook Recipent

[slohman]

I am using Excel 2007 and Outlook

If Sheet1 Column A has the word Special within that cell could be "Special Date" or "Special Item" (only need to find the word Special) and a user puts a persons name in Column M an email will be sent to that person telling him he needs to order Column A and what kind it is in Column C. eg Special Item - Curved Slide

The email addresses can be put in manual if need be but they are on Sheet 2

Sheet 2
Col 1 Col2
Peter peter@mail.com.au

Thanks

 

[iferror]

I think the best best way would be you uploading a file.

Are you open to help-columns? How many rows you have in Sheet1? hundreds? thousands?

 

[SirJB7]

Hi, slohman!
Get a look at this link:
http://www.rondebruin.nl/win/section1.htm
And go thru this:
http://www.rondebruin.nl/win/s1/outlook/mail.htm
Regards!

 

[slohman]

What do you mean by Are you open to help-Columns

The rows wouldn't ever go pass 1000.

 

[iferror]

help-columns are additional columns that you may add to help you out. In your case i am thinking a column where you lookup the email address on sheet2 whenever the name in sheet1 has a value, or a 'special' column where you have a flag TRUE/FALSE based on column A.
Do you want to have the mail sent away as soon as you enter a value in column M or upon clicking a button/firing a macro?

 

[slohman]

I would like it to run when a name lets say Peter is added into Column M and the word 'special' is in Column A so maybe and if statement.

 

[iferror]

i'm more a teach-how-to-fish kind of helper so i'm going to point you in the right direction first. Should you need further help, please do not hesitate to ask

I assume that you're using an help-column in column N that has a formula to look up for mail address in Sheet2

1) you need to exploit the Worksheet_Change event.

Something in the line of

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim lstRow As Long
  lstRow = Range("A" & Rows.Count).End(xlUp).Row

  Dim trgtRng As Range
  Set trgtRng = Range("M1", Range("M" & lstRow))

  If Intersect(Target, trgtRng) Is Nothing And Target.Value <> "" Then Exit Sub

  Application.EnableEvents = False

  If InStr(UCase(Range("A" & Target.Row).Value), "SPECIAL") >= 1 Then
     mail = Target.Offset(0, 1).Value
     If IsError(Target.Offset(0, 1).Value) Then mail = InputBox("INSERT MAIL")
     'actually you should check here if the mail adress is valid!
     SendOutMail(mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value)
  End If

  Application.EnableEvents = True
End Sub

2)add a module to the project and paste this code (needs a bit of tailoring from tyour side )

Sub SendOutMail(addr2mail,spcl01,spcl02)
  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 = addr2mail
  .CC = ""
  .BCC = ""
  .Subject = spcl01 & spcl02
  .Body = "Hi there, please buy" & spcl01 & spcl02
  End With
  On Error GoTo 0

  Set OutMail = Nothing
  Set OutApp = Nothing
End Sub

 

[slohman]

I have had no luck getting this to work could you please show me on my uploaded file. Thanks

 

[iferror]

Hi,
tested on a sample file (see image) and the only changes i made are:
Changed:

If Intersect(Target, trgtRng) Is Nothing And Target.Value <> "" Then Exit Sub

to:

If Intersect(Target, trgtRng) Is Nothing Or Target.Value = "" Then Exit Sub

and (sory should have mentioned that you need to call a sub)

SendOutMail(mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value)

to

Call SendOutMail(mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value)

 

[SirJB7]

and (sory should have mentioned that you need to call a sub)

SendOutMail(mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value)

to

Call SendOutMail(mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value)

@iferror
Hi!
You could avoid the call with this:

SendOutMail mail,Range("A" & Target.Row).Value,Range("B" & Target.Row).Value

Regards!

 

[iferror]

yup, right i sort of recalled it can be done!! Sure, with brakets vba expect to have a funciotn and a variable to assign the result to.

 

[SirJB7]

@iferror
Hi!
You can still use this too:

    Let A = "This is the last VB instruction released ;)"

instead of the old:

    A = "This is the last VB instruction released ;)"

where A is declared as string variable.

Regards!