can excel do this???

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
336
Office Version
  1. 2021
Platform
  1. Windows
I was just wondering if its possible for excel to email me each time a particular worksheet changes?

is this possible?

More specificially.... each time a new entry to the J column appears, email me the details of last cell in column J, K, L, M, N, O, P, Q, and R


i.e if a new entry appears in J33, then email me entry for J33, K33, L33, M33, N33, O33, P33, Q33, and R33


Can excel do this?

thanks guys :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Yes and no. You could certainly configure a worksheet_change event to fire off such an e-mail.... but, without taking the time to circumvent the security features, sending an e-mail through VBA will require the user to OK the transmission. So, if the user didn't diligently click that button, your plan wouldn't work.
 
Upvote 0
Jpowell,
Yes, it would be possible to setup a macro to do this, but the user of the sheet would be able to disable the macro's which would stop the macro from running. Don't know if that woiuld be a problem or not. If it is, you would need to take a look at security certificates as well, and setup a valid certificate on each users PC.

Let me know, and we can put something together.

Cal
 
Upvote 0
Hello,

Put this code into the relevant sheet code window


Code:
Dim OLD_VALUE As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(OLD_VALUE) Then
    MY_ROW = Range("J65536").End(xlUp).Row
    Dim olMailItem As Variant
    Set out = CreateObject("Outlook.Application")
        With out.CreateItem(olMailItem)
            strEmail = "ENTER ADDRESS" 'ENTER YOUR ADDRESS
            .Recipients.Add strEmail
            .Subject = "SPREADSHEET CHANGES"
            .Body = Range("J" & MY_ROW).Value & Range("K" & MY_ROW).Value & Range("L" & MY_ROW).Value & Range("M" & MY_ROW).Value _
                & Range("N" & MY_ROW).Value & Range("O" & MY_ROW).Value & Range("P" & MY_ROW).Value _
                & Range("Q" & MY_ROW).Value & Range("R" & MY_ROW).Value
          .Send
        End With
End If
 
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OLD_VALUE = Target.Value
End Sub

This doesn't do it automatically, as there is a warning message, but I think I have seen code on this site to disable the message, its just a question of finding it.

Is this a start?
 
Upvote 0
Oaktree,
There are a few ways around the security of outlook(Don't use Outlook, use the STMP server), but I wouldn't send confidental information via them. So, if it's confidental information, you will have some issues, otherwise I can get around it.

Cal
 
Upvote 0
thanks for the swift response guys :)

no the information is not confidential or anything like that.....

its basically a sheet which updates during the day on my home pc, and I'd like the data to be sent to my work email address, so I know it's updating properly. This would save me having to keep "VNC-ing" into my home pc to check its updating.

Ideally, not having to click on any kind of confirmation would be good.

thanks
 
Upvote 0
Code:
Sub Mail_Small_Text_CDO()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "111.111.111.111" 'Need your STMP address here
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    strbody = "Hey There," & vbNewLine & vbNewLine & _
              "Test 1" & vbNewLine & _
              "Test 2" & vbNewLine & _
              "Test 3" & vbNewLine & _
              "Test 4"
 
    With iMsg
        Set .Configuration = iConf
        .To = "Test@Test.com"
        .CC = ""
        .BCC = ""
        .From = """TestUser"" <TestUser@test.com>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub

This code will send your email without the security message popping up.

HTH
Cal
 
Upvote 0
There's also a COM library that you can install to get a similar effect (of bypassing the security). It's called Outlook Redemption, or something like that.

I hope this helps.

Regards,

jerry
 
Upvote 0
jerrykern,
Just as an FYI, I also have worked with redemption, and the above code is much easier to work with then it.(Although redemption lets you do all kinds of other things too) So if you are just sending a message I would suggest my code. Anything more complicated then you may want to look at redemption.

Cal
 
Upvote 0
that's great guys...I look forward to testing this out...thanks alot :)
 
Upvote 0

Forum statistics

Threads
1,214,944
Messages
6,122,391
Members
449,080
Latest member
Armadillos

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
Back
Top