VBA Code to send email based on cell not working
Results 1 to 9 of 9

Thread: VBA Code to send email based on cell not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to send email based on cell not working

    Can any please help I have been on this all day and can not get it to do what I want it to do.
    I have managed to obtain this VBA Code to send an automated email when the cell valve of M12 is greater than 200. Now this code works fine when I manually type a value in M12 greater than 200 and hit the enter key but for some reason because I have an If & And function formula in M12 when the formula populates M12 with a figure greater than 200 it just does not work
    Please can any one help it is so frustrating

    thank you


    Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("M12"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
    Call Mail_small_Text_Outlook
    End If
    End Sub

    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = Range("M18").Value & ";" & Range("S7").Value & ";" & Range("S11").Value
    .CC = ""
    .BCC = ""
    .Subject = "Pre Start Warning Alert re " & Range("P3").Value
    .Body = "This is an Pre Start Warning Alert to note you that we have started on site with No Pre-Start logged."
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,792
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Can you post the formula you have in M12?

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    This event is activated when a cell is modified. In the case of the formulas, the cell is not modified, the formula remains the same, what changed was the result of the formula.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    One option is to use the calculate event, this event is activated every time a formula is updated.
    Try this:

    Remove the change event and put the following:
    Code:
    Private Sub Worksheet_Calculate()
        If IsNumeric(Range("M12").Value) And Range("M12").Value > 200 Then
            Call Mail_small_Text_Outlook
        End If
    End Sub
    Regards Dante Amor

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,792
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Hi Dante, good catch.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    This is the if and function in m12. So when m12 = 201 from the function below the email should be displayed from the above VBA Code. But it does not work. It only works when I manually type over the formula with a number >200
    =If(And(M11
    M9= today’s date
    M10=pre start meeting date
    M11= start on site date

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Dante
    thank you for this I will try it when I get to work on Monday and let you know how I got on

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Quote Originally Posted by Domenic View Post
    Hi Dante, good catch.
    Thanks Domenic, I hope it works for the OP.
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Dante
    i have copied your code into my worksheet and it works perfectly, thank you so much

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Code to send email based on cell not working

    Quote Originally Posted by Malcolm torishi View Post
    Dante
    i have copied your code into my worksheet and it works perfectly, thank you so much
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •