Trigger needed

  • Hi


    Not sure this is in the correct forum, please move if needed. I have a spreadsheet with nested if statements and these if statements result in either a blank cell, OK or Due. I need a method of send an email to a given person whenever "DUE" appears and the email would need the name from column A and the module ref. from row 1. Is this possible to automate when the spread sheet is opened?

  • Re: Trigger needed


    It depends when the emails should be sent. The original post ended "...when the spreadsheet is opened", in which case you need the open event. But it may be more appropriate to send emails as changes happen. Either way, an example would help!

  • Re: Trigger needed


    Quote from Trevor_S;789555

    It depends when the emails should be sent. The original post ended "...when the spreadsheet is opened", in which case you need the open event. But it may be more appropriate to send emails as changes happen. Either way, an example would help!


    Hi all


    this is a small example of the spreadsheet and I have changed 1 cell to "DUE" for illustration. I would like the email/trigger to happen as soon as the spreadsheet opens as it opens.


    [TABLE="width: 586"]

    [tr]


    [td]

    Empolyee Number

    [/td]


    [td]

    Name

    [/td]


    [TD="align: center"]Eng. CMS/Mod 01[/TD]
    [TD="align: center"]Eng. CMS/Mod 02[/TD]

    [td]

    OK or DUE

    [/td]


    [TD="align: center"]Eng. CMS/Mod 03[/TD]

    [td]

    OK or DUE 03

    [/td]


    [/tr]


    [tr]


    [td]

    A069

    [/td]


    [td]

    fred1

    [/td]


    [TD="align: right"]21/03/2016[/TD]
    [TD="align: right"]28/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A054

    [/td]


    [td]

    fred2

    [/td]


    [TD="align: right"]04/04/2016[/TD]
    [TD="align: right"]26/01/2016[/TD]

    [td]

    DUE

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A084

    [/td]


    [td]

    fred3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A006

    [/td]


    [td]

    fred4

    [/td]


    [TD="align: right"]23/03/2016[/TD]
    [TD="align: right"]25/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A033

    [/td]


    [td]

    fred5

    [/td]


    [TD="align: right"]15/04/2016[/TD]
    [TD="align: right"]26/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A085

    [/td]


    [td]

    fred6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A036

    [/td]


    [td]

    fred7

    [/td]


    [td][/td]


    [TD="align: right"]26/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A053

    [/td]


    [td]

    fred8

    [/td]


    [TD="align: right"]14/03/2016[/TD]
    [TD="align: right"]26/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A020

    [/td]


    [td]

    fred9

    [/td]


    [TD="align: right"]04/04/2016[/TD]
    [TD="align: right"]20/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A082

    [/td]


    [td]

    fred10

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A056

    [/td]


    [td]

    fred11

    [/td]


    [TD="align: right"]22/03/2016[/TD]
    [TD="align: right"]28/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A051

    [/td]


    [td]

    fred12

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A074

    [/td]


    [td]

    fred13

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A026

    [/td]


    [td]

    fred14

    [/td]


    [TD="align: right"]14/03/2016[/TD]
    [TD="align: right"]25/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A088

    [/td]


    [td]

    fred15

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A048

    [/td]


    [td]

    fred16

    [/td]


    [TD="align: right"]10/12/2015[/TD]
    [TD="align: right"]28/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A083

    [/td]


    [td]

    fred17

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A075

    [/td]


    [td]

    fred18

    [/td]


    [TD="align: right"]23/03/2016[/TD]
    [TD="align: right"]21/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A073

    [/td]


    [td]

    fred19

    [/td]


    [TD="align: right"]14/03/2016[/TD]
    [TD="align: right"]27/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A077

    [/td]


    [td]

    fred20

    [/td]


    [TD="align: right"]30/03/2016[/TD]
    [TD="align: right"]01/02/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A044

    [/td]


    [td]

    fred21

    [/td]


    [TD="align: right"]04/04/2016[/TD]
    [TD="align: right"]27/01/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A066

    [/td]


    [td]

    fred22

    [/td]


    [TD="align: right"]18/04/2016[/TD]
    [TD="align: right"]01/02/2016[/TD]

    [td]

    OK

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Thanks in advance Karl

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]





    [/td]


    [/tr]


    [/TABLE]

  • Re: Trigger needed


    I'm assuming that the extract starts in A1, and that you want columns A:D plus the header row where column E shows as DUE when the workbook is open. I'm also assuming that this is the only sheet in the workbook, or at least will be the active sheet when the workbook is opened. The following macro should go in the ThisWorkbook section of the VBA editor.


    Edit: Note that the code is displaying differently from how I entered it! Where it shows a double "less than" or "greater than" symbol, replace it with a single symbol.

  • Re: Trigger needed


    Quote from Trevor_S;789769

    I'm assuming that the extract starts in A1, and that you want columns A:D plus the header row where column E shows as DUE when the workbook is open. I'm also assuming that this is the only sheet in the workbook, or at least will be the active sheet when the workbook is opened. The following macro should go in the ThisWorkbook section of the VBA editor.


    Edit: Note that the code is displaying differently from how I entered it! Where it shows a double "less than" or "greater than" symbol, replace it with a single symbol.



    Hi


    thanks for this. In the given coding, correct me if i am wrong, you are looking for "DUE" in "E:E". The proper spread sheet currently has "DUE" up to and inc. column "BS". Is it possible to just to change the "DUE" test from E:E to check the whole of the spreadsheet.


    Many thanks


    Karl

  • Re: Trigger needed


    You are right that its currently just checking column E. Its also only putting values from columns A to D into the email. From your extract, I'm assuming that columns C and D only make sense to be reported in the email if "DUE" was found in column E. Therefore could you clarify which column(s) should be reported in the email, and I'll amend the macro.

  • Re: Trigger needed


    Hi


    Sorry for the delay in reply bu the list where "due" could appear in a column is as follows, sorry for the amount and hope it does not cause an issue,


    E,G,I,K,M,O,Q,S,V,W,Y,AA,AC,AE,AG,AI,AK,AM,AO,AQ,AS,AV,AW,AY,BA,BC,BE,BG,BI,BK,BM,BO,BQ AND FINALLY BS.


    There are currently 35 modules hence this amount.


    thanks in advance

  • Re: Trigger needed


    Sorry for the delay ... it can be done (although the extra searching may slow it down a bit). But the other part of my question was what do you want to be displayed in the email? At the moment it checks column E and where DUE is found, it displays A:D in the email. But what if DUE was found in column AA? Presumably the contents of columns C and D would be irrelevant?


    If you only want columns A and B in the email, the amended code is below (note that there's the same issue with double << and >> symbols). But if you want any other columns displayed, let me know.


  • Re: Trigger needed


    Hi


    the content of the email will need to detail as follow


    1 the name from, for example, B2 downward.Also the module ref which can be taken from the preceding column, for example, the module ref for any "DUES" in column G is found in the cell ref F1.


    Then just a quick note to inform the person that receives the mail that the particular person named will be due reassessment for the given module from, in this example, F1.


    "Good Morning


    Please find below the staff that are due reassessment and the given module in question.
    EG "Joe Bloggs Module CMS ENG mod 3""


    Can I say thanks for all your efforts. Following they work you have completed I will be able to add the particular email address needed.


    One final question once completed where will I post this please I presume I would open VBA for the particular spreadsheet in the general screen that opens first.


    Any question please don't hesitate to contact me.Can I ask your level of experience and area of knowledge due to this excellent work.

  • Re: Trigger needed


    I have made the changes - here is the revised code:


    It should go into the ThisWorkbook section of the VBA Project. I have created and attached a spreadsheet based on your original sample, with the macro added in the correct place, in case it helps: forum.ozgrid.com/index.php?attachment/72179/
    Bear in mind that if you download and open it with macros enabled, it should try to send an email as soon as you open it. You should still get a message box notifying you of this, and you can just click the option to disallow so that nothing is sent ... or better still, disable macros in excel before opening it.


    I haven't been able to fully test it, as I don't have Outlook. It may error on the line .BodyFormat = olFormatHTML - if so, delete that line and it should work fine.
    A few other points to note:

    • As previously, replace the double greater than and less than symbols with single ones. The uploaded excel spreadsheet linked to above shows the symbols correctly.
    • I'm assuming that there will be no gaps in data in column A. The macro only searches until it gets to the bottom of a continuous block of data in column A, to prevent it searching thousands of blank rows.
    • I'm also assuming that either this is the only sheet in the workbook, or if not, it will be the active workbook at the time that it is opened. If this may not be the case, add a line like this directly below Private Sub Workbook_Open() in order to make it the active sheet:
      Code
      Sheets("Name Of Sheet Containing Data").Activate



    As for your question about me, I've just picked up what I know from having used Excel for over 20 years for accountancy work. There's always more to learn and better ways to do things. I've gained a lot over the years from searching various forums, and so now also try to answer what I can. In particular, I try to find questions that have gone unanswered for a while, as there's a risk that once they disappear from the New Posts lists, they don't get spotted so easily.


    Anyway, hope that this does what you need!

  • Re: Trigger needed


    Hi Sorry it has been a while but I have tried just down loading the sample you supplied but get the following


    "compile error:


    user-defined type not defined"


    with the following line highlighted


    Dim olApp As Outlook.Application


    Sorry to be a pain

  • Re: Trigger needed


    Try replacing these lines:

    Code
    Dim olApp As Outlook.Application 
     Dim objMail As Outlook.MailItem 
     Set olApp = Outlook.Application 
     Set objMail = olApp.CreateItem(olMailItem)


    With these lines:

    Code
    Dim olApp As Object
        Dim objMail As Object
        Set olApp = CreateObject("Outlook.Application")
        Set objMail = olApp.CreateItem(0)
  • Re: Trigger needed


    That normally only appears when it can't find a macro. Could you please upload a copy of the spreadsheet? You can blank out the data if you want, its just so I can see how/where the macro appears.

  • Re: Trigger needed


    HI


    I have just applied your VBA to one of the spreadsheets I plan to use it on but the email that is been sent does not quote any name


    "Good Morning
    Please find below the staff that are due reassessment and the given module in question.

    NameModule
    Alan Booth
    Alex Thorley
    Alex Walker
    Andrew Tranter
    Andrew Wilcock
    Andrew Wilcox
    Arthur Nixon
    Ben Boberts
    Bradley Smith
    Brain Bailey
    Brian Challinor
    Eng. CMS/Mod 04/b"







    This is a sample of the spread sheet and as you can see it brings the module but not the name




    [TABLE="width: 899"]
    [tr]


    [/TD]

    [/tr]


    [/TABLE]
    This is a copy of the VBA i have pasted in


    Private Sub Workbook_Open()
    If Application.WorksheetFunction.CountIf(ActiveSheet.Range("A:BS"), "DUE") > 0 Then
    mymsg = "<HTML><Body>Good Morning<p>Please find below the staff that are due reassessment and the given module in question."
    mymsg = mymsg & "<Table><TR>"
    mymsg = mymsg & "<TD><STRONG>Name</STRONG></TD>"
    mymsg = mymsg & "<TD><STRONG>Module</STRONG></TD>"
    mymsg = mymsg & "</TR>"
    LastRow = ActiveSheet.Range("A1").End(xlDown).Row
    For Each cell In ActiveSheet.Range("A1:BS" & LastRow)
    If cell.Value = "DUE" Then
    mymsg = mymsg & "<TR>"
    mymsg = mymsg & "<TD>" & ActiveSheet.Cells(cell.Row, 2).Value & "</TD>"
    mymsg = mymsg & "<TD>" & ActiveSheet.Cells(1, cell.Column - 1).Value & "</TD>"
    mymsg = mymsg & "</TR>"
    End If
    Next
    mymsg = mymsg & "</TABLE></Body></HTML>"

    Dim olApp As Object
    Dim objMail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set objMail = olApp.CreateItem(0)
    With objMail
    .HTMLBody = mymsg
    .To = "[email protected]" 'Put recipient email address here
    .Send
    End With
    End If
    End Sub


    Again sorry to be a pain and I can not thank you enough for all your help

  • Re: Trigger needed


    This is the line of code that should pick up the name:
    mymsg = mymsg & "<TD>" & ActiveSheet.Cells(cell.Row, 2).Value & "</TD>


    The 2 represents the number of the column that its looking for the name in - i.e. column B. Is that where the name is? If the name is in column A, change the 2 in this code line to a 1.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!