Send emails to specific recipients if the cell value changes in a given range

[Shweta155]

Hi All, Would really appreciate your help!!

I want to automate sending emails to specific recipients if the cell value changes in given range.

So, I have linked Workbook 1 - column A (rows 3-30) to workbook 2 . As the date is added in workbook 2 and reflected in workbook 1 (by simply-linking formula) - I want as the date changes in workbook 1 (empty cell in workbook 2 is reflecting 1/1/1900 as date in workbook 1) from 1/1/1900 to "new added date" a email is triggered to corresponding email ID in workbook column B (rows 3-30). !!Macro!!

I am on a clock here! can someone pls help!

Thank you so much in advance.

Shweta

 

[MCLIFTO8]

I don't understand what workbook has what data. Are you saying both workbooks are containing the same visual display of Name-Date but WB1 has the names and a formula for dates and WB 2 has dates with a formula to pull the names?

If this is the case....

The macro could technically be in either workbook but considering you're updating WB2 when you want the email sent, it should be put into WB2.
The macro needs to be self triggering, you need an "event" sub. If you name your macro accordingly Excel will know to trigger it automatically.
When you enter the date in column A you also need to get the value of the cell in column B so it can be entered into an email.
Then you pass the value into an email. I'm assuming you're using Outlook. It's possible to use google, Yahoo, etc with some googling.

Code
Code would look something like this, it's untested.

Go to Tools>References in the VBA editor and add “Microsoft Outlook 16.0 Object Library.”

Sub Worksheet_Change(ByVal Target As Range)

    Dim appOutlook As Outlook.Application
    Dim mEmail As Outlook.MailItem

    Set  appOutlook = New Outlook.Application
    Set mEmail = EmailApp.CreateItem(olMailItem)

    With mEmail
         .To = ThisWorksheet.Range("B" & Target.Row).Value2 'Target is a range of the cells that changed when the macro triggered, row is the row number of said range. 
         .CC = ""
         .BCC = ""
         .Subject = "Test"
         .HTMLBody = "This is a test email sent via Excel."
         .Send '.Display if you want final review
    End With
    Set mItem = Nothing
    Set appOutlook = Nothing
End Sub