1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'VBA Macros' started by Rodrigues, Oct 11, 2017.

  1. Rodrigues

    Rodrigues Member

    Messages:
    173
    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

    Attached Files:

  2. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    189
    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.
    Code (vb):
    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 likes this.
  3. Rodrigues

    Rodrigues Member

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

    Kenneth Hobson Active Member

    Messages:
    189
    Did you?
  5. Rodrigues

    Rodrigues Member

    Messages:
    173
    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

    Attached Files:

  6. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    189
    Did you add the reference to the Outlook object as I commented in the code?
  7. Rodrigues

    Rodrigues Member

    Messages:
    173
    I'm sorry, don't know, could you help, please?
  8. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    189
  9. Rodrigues

    Rodrigues Member

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

Share This Page