How to send a row data from excel on an email if conditions

January 30, 2018 at 08:36:17
Specs: Windows 7
Hello,

Please, I wondering if you can help me to create a macro that help me to send an email from excel if one condition is made.
I like to send the data between "A3 : I3" to the email located at "J3" if the letter at "I3" is "N".
The trigger to send or not is located at the column K.
If a type on the column K the number "0" do not send the email...if I type the number "1" send the email (it means that it is the first time)... if I type the number "2" send the email (it means that it is the second time)... if I type the number "3" send the email (it means that is the third time and the last one) .
If I see the number 3 I will not send more emails to that person.

Number Employee Dept Report Date Approval Completion Improvement Complete Mail Trigger
1 Jake IT Tom Jan1 Y Jan15 New Board N tom@abc 2
2 Ed IT Rob Jan1 Y Jan 30 Clips Y rob@abc 0

Thank you,



See More: How to send a row data from excel on an email if conditions

Report •

#1
January 30, 2018 at 09:58:07
1 - Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

2 - If I understand your requirements correctly, there are actually 2 triggers, correct? The number 1, 2 or 3 has to be entered in Column K, then I3 needs to be checked for an "N".

Do I have that right?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 30, 2018 at 13:19:15
A B C D E F G H I J K
1 Nu. Em. De. Re. Da. Ap. Co. Im. Co. Mail Trigger
2 1 Jake IT Tom Jan1 Y Jan15 New Board N tom@abc 2
3 2 Ed PR Rob Jan1 Y Jan 30 Clips Y rob@abc 0
4 3 Mike IT Tom Jan1 Y Jan15 New Desk N tim@abc 1
3 7 Eli PR Rob Jan1 Y Jan 30 Laptop Y eli@abc 0
5 5 Mike IT Tom Jan1 Y Jan15 Glasses N tim@abc 3
6 9 Eli PR Rob Jan1 Y Jan 30 Bins N eli@abc 1
7

Yes you are right...the trigger is column "I" and "K".... in this case for example I want to send the email with the information on the complete row to the each person/email that on the column K are different from "0". The number 1,2 and 3 are my control to know how many time I sent the email. Once I type the number on column K and the confirmation of completion with the letter "Y" at the column "I" is ready an email reminding to finish that task has to be send. Only the row information.

Thank you..


Report •

#3
January 30, 2018 at 13:31:39
It does not appear that you followed the data formatting instructions in the How To link.

You need to place your data between the pre tags. There a link for the pre tags below the words Post Reply. Your data should look something like this when the pre tags are used.

     A        B          C
1   Tom      Bill      Fred
2   Dog      Cat       Bird

You should be able to edit your previous response and then preview it to help you line up the data in columns.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
January 31, 2018 at 05:06:14
   A   B       C     D     E      F     G        H            I    J         K
1  Nu. Em.     De.   Re.   Da.    Ap.   Co.      Im.          Co.  Mail      Trigger
2  1   Jake    IT    Tom   Jan1   Y     Jan15    New Board    N    tom@abc   2
3  2   Ed      PR    Rob   Jan1   Y     Jan 30   Clips        Y    rob@abc   0
4  3   Mike    IT    Tom   Jan1   Y     Jan15    New Desk     N    tim@abc   1
3  7   Eli     PR    Rob   Jan1   Y     Jan 30   Laptop       Y    eli@abc   0
5  5   Mike    IT    Tom   Jan1   Y     Jan15    Glasses      N    tim@abc   3
6  9   Eli     PR    Rob   Jan1   Y     Jan 30   Bins         N    eli@abc   1
7

EDIT - Content from Response #2 added by DerbyDad03 for clarity

Yes you are right...the trigger is column "I" and "K".... in this case for example I want to send the email with the information on the complete row to the each person/email that on the column K are different from "0". The number 1,2 and 3 are my control to know how many time I sent the email. Once I type the number on column K and the confirmation of completion with the letter "Y" at the column "I" is ready an email reminding to finish that task has to be send. Only the row information.


Report •

#5
January 31, 2018 at 05:07:02
Thank you for your help...!

Report •

#6
January 31, 2018 at 08:50:42
I'm still a little confused, but I am going to offer some code anyway. You (or I) can always modify it if it does not fit your needs.

It is not clear to me what your actually trigger should be. Let's first make sure that we are using the term "trigger" in the same manner.

I consider a "trigger" to be the thing that causes something to happen. e.g. You make a change to the spreadsheet which triggers the code. The change is the trigger. Once the code is triggered, the code then checks to see if certain criteria have been met to determine if further action should be taken.

For example, with the code shown below, any change to the spreadsheet will trigger the code. The code will then check for the following criteria:

1 - Was the change made to Column K?
2 - If yes, was the number entered in Column K greater than 0 and is there an "N" in Column I of the same row?
3 - If yes, then build and send the email; if not, then don't so anything.

So, as you can see, there is a difference between "trigger" and "criteria".

That said, if the trigger and criteria noted above is what you are looking for, then the following code should meet your needs.

A few points related to the code:

1 - You did not mention any thing about a subject line. In the code below I used this instruction:

 .Subject = "Generic Subject Line"

Replace the text between the quotes with whatever text you want the Subject line to show.

2 - You said that you wanted to include Column I in the email. That is your Y or N column. It's just my opinion but that seems kind of redundant. The only time the recipient would see the contents of Column I is when the task is not complete, so what's the point of including the "N" in the email? Doesn't the user already know that the task is not complete based on the fact that (s)he received the email?

3 - The code is currently set to Display the email, but not to Send it. This is done for testing purposes or if you want to add something to the email once it is created. If you want the code to automatically send the email, remove the comment marker (') from in front of .Send and put it in front of .Display


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rngCol As Long
    Dim tmpBody As String
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    
'Determine if a change was made to Column K
    If Target.Column = 11 Then
    
'Determine if number entered was > 0 and Column I = "N"
      If Target > 0 And Cells(Target.Row, "I") = "N" Then
         
'If yes, then build email Body string
       For rngCol = 1 To 9
         tmpBody = tmpBody & Cells(Target.Row, rngCol) & " "
       Next
'Send email
          Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = Cells(Target.Row, 10)
                .CC = ""
                .BCC = ""
                .Subject = "Generic Subject Line"
                .Body = tmpBody
                .Display
                '.Send
                
            End With
            Set OutMail = Nothing
      End If
    End If
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
February 1, 2018 at 07:27:54
Thank you for your assistance on the macro. I copy the code to the file but I don't know if there is something that I could be doing wrong. Once I type the numbers on the file it is not sending or displaying the email. I removed the marker (') from display to send. Please can you give me an advise.
Thank you.

Report •

#8
February 1, 2018 at 13:37:32
First, I would not remove the comment marker form the .Send instruction until you are sure that the code is doing what you want it to do. If you do, it's going to send the email before you have had a chance to review it. If it were me, I would test the heck out the process before allowing the emails to actually be sent.

Second, you say that you copied "the code to the file" but you don't say where you copied it to.

The proper procedure is as follows:

1 - Right click on the sheet tab for the sheet in which the data resides.
2 - Choose View Code
3 - Paste the code into the pane that opens
4 - Save the file with a .xlsm file extension

The code is known as a Worksheet_Change macro, which means that it is used for monitoring a specific Worksheet. Therefore, it must be stored in the VBA Module for that specific sheet.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
February 2, 2018 at 09:15:16
Thank you very much for your instructions and help...! It is very awesome. thanks.

Report •

Ask Question