Send email when ON HOLD word is found on a cell and copy the entire row on the email body

[Rodrigues]

Hi All
I'm wondering if someone can help me please.
I'm looking for a code to trigger an email, when on column CB state the word ON HOLD (example on cell CB4).
CB4 downwards have a conditional formula to show or not the ON HOLD.
Would like the email to be sent out as soon the word is shown and on the email body the entire row.
Thanks in advance.
Regards
R

 

[Kenneth Hobson]

I know of 2 ways that sort of does that. They both work better for fewer columns.

Attachment is the best way to maintain format.

Right click the sheet's tab, View Code, and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, b As Range, Word As Object
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  Dim sig$
 
  Set r = Intersect(Target, Range("BR4", Cells(Rows.Count, "BR").End(xlUp)))
  If r Is Nothing Then Exit Sub
  If r.Count <> 1 Then Exit Sub
  If Not IsNumeric(r) And Not r.Value > 0 Then Exit Sub
 
  Set olApp = New Outlook.Application
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail
    .Display
    sig = .HTMLBody
    .To = "ken@gmail.com"
    .Subject = "ON HOLD - Ref: " & Cells(r.Row, "A")
    Set b = Intersect(r.EntireRow, ActiveSheet.UsedRange)
   
    'http://www.rondebruin.nl/win/s1/outlook/bmail2.htm
    '.HTMLBody = RangetoHTML(b) & sig
   
    .GetInspector.Display
    Set Word = .GetInspector.WordEditor
   
    b.Copy
    Word.Range(0, 0).Paste
    Application.CutCopyMode = False
   
    .Display
    '.Send
  End With
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

 

[Rodrigues]

Kenneth
Thanks for your help, after changed the email address, added a number on BR4 but nothing happened. Could you please help?
Regards
R

 

[Kenneth Hobson]

Did you?

Right click the sheet's tab, View Code, and paste.

 

[Rodrigues]

Oh, was copying to the wrong place.
No I have copied to Sheet1, got an error "User defined type not defined and after click OK, highlight at yellow the very first line of code. Private Sub Worksheet.... and blue background olApp As Outlook.Application
Have attached file.
Thanks again
R

 

[Kenneth Hobson]

Did you add the reference to the Outlook object as I commented in the code?

 

[Rodrigues]

I'm sorry, don't know, could you help, please?

 

[Kenneth Hobson]

I did in the comment. In the Visual Basic Editor (VBE), click the menu items, Tools and then References, and then the object, Microsoft Outlook xx.0 Object Library. xx is your version number for the Suite.

If this is not clear enough, see: https://msdn.microsoft.com/en-us/vb...cles/check-or-add-an-object-library-reference

 

[Rodrigues]

Go it, thank you so much.
Exactly what I was looking for.
Regards
R