Two VBA, One Sheet

[rtroehrig]

I am currently running this VBA on a worksheet so that data cannot be deleted once it is entered:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Target.Worksheet.Unprotect Password:="efficiency"
    Target.Locked = True
    Target.Worksheet.Protect Password:="efficiency"
End Sub

Now it has been requested to have an email automatically sent out anytime the cell in a certain column contains data. I think I can accomplish this with this VBA:

Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), 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 = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

What I can't figure out is how to have them both run on the same sheet. I've never really had to use more than one VBA code at a time.

 

[JoeMo]

The change event macro should be a sheet module in the relevant sheet. The mail macro can be a standard module in the workbook the sheet event code is in.

 

[RustEE2020]

You will have to put them both in the same routine, and use the intersect method to determine which part of the code to run. Then after the code you want runs you jump to the end of the subroutine and avoid all other code.

Edit:
Joe is right make just the email part of the second sub a public sub in a module or at least separate from the change event. Then use the intersect code you have inside the change event to determine if you need to call the email subroutine. Then you just have the code from the first sub execute before or after that but make it non reliant on the intersect check. Also you may want to make that locking code a public sub as well so you can use it on other pages easier or even other projects.

 

[rtroehrig]

Thanks you both.

I just realized the email code isn't actually going to do what I want it to do anyway. I want it to automatically send all data in the row if the contents of column H is <0 or >0. All this code is going to do is send a preset notification out.

Back to the drawing board...

 

[RustEE2020]

I am sorry what data are you trying to send? You can write code in that subroutine to build the body based on information from a workbook or worksheet. It has been awhile since I made my email sub but what do you think is wrong with yours?

 

[RustEE2020]

For example you could rewrite your Xmail body as such:

    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "I would like to inform you that we have sold, " & _
              ActiveWorkbook.Worksheets(1).Range("A1").Text & _
              " units of product X!" & _
              vbNewLine & _
              "This is line 2"

This will put a small message together to show the recipient data from cell A1 on sheet1 of the currently active workbook. However this is a very simple example and you can get a lot more focused with a subroutine so think about the data you need to send and how you may need to format it. Is the data always going to be the same length? If not you will probably need to make the email body builder dynamic,(not that you shouldn't anyway). Is the data always going to be the same type? If not do you need to use a Case statement in order to determine how you build the body of your email. Will you need to send data from another sheet or even workbook than where the code is executed from, maybe you will need to pass a range, worksheet or workbook to the subroutine.