Two VBA, One Sheet

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
I am currently running this VBA on a worksheet so that data cannot be deleted once it is entered:
VBA Code:
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:
VBA Code:
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
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.
 
Last edited:

rtroehrig

New Member
Joined
May 2, 2018
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
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

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
For example you could rewrite your Xmail body as such:

VBA Code:
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,694
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top