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
You need a On Workbook Open macro: https://msdn.microsoft.com/en-…rary/office/ff196215.aspx .
If you could provide a bit more information (e.g. what column contains the if formula, what details - cell references - you need brought into the email) I can do a sample macro.
Re: Trigger needed
I would say a worksheet_change or calculate event, not the open event. Attach an example workbook.
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;789555It 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"]
Empolyee Number
[/td]Name
[/td]
[TD="align: center"]Eng. CMS/Mod 01[/TD]
[TD="align: center"]Eng. CMS/Mod 02[/TD]
OK or DUE
[/td]
[TD="align: center"]Eng. CMS/Mod 03[/TD]
OK or DUE 03
[/td]A069
[/td]fred1
[/td]
[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]28/01/2016[/TD]
OK
[/td]A054
[/td]fred2
[/td]
[TD="align: right"]04/04/2016[/TD]
[TD="align: right"]26/01/2016[/TD]
DUE
[/td]A084
[/td]fred3
[/td]A006
[/td]fred4
[/td]
[TD="align: right"]23/03/2016[/TD]
[TD="align: right"]25/01/2016[/TD]
OK
[/td]A033
[/td]fred5
[/td]
[TD="align: right"]15/04/2016[/TD]
[TD="align: right"]26/01/2016[/TD]
OK
[/td]A085
[/td]fred6
[/td]A036
[/td]fred7
[/td]
[TD="align: right"]26/01/2016[/TD]
OK
[/td]A053
[/td]fred8
[/td]
[TD="align: right"]14/03/2016[/TD]
[TD="align: right"]26/01/2016[/TD]
OK
[/td]A020
[/td]fred9
[/td]
[TD="align: right"]04/04/2016[/TD]
[TD="align: right"]20/01/2016[/TD]
OK
[/td]A082
[/td]fred10
[/td]A056
[/td]fred11
[/td]
[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]28/01/2016[/TD]
OK
[/td]A051
[/td]fred12
[/td]A074
[/td]fred13
[/td]A026
[/td]fred14
[/td]
[TD="align: right"]14/03/2016[/TD]
[TD="align: right"]25/01/2016[/TD]
OK
[/td]A088
[/td]fred15
[/td]A048
[/td]fred16
[/td]
[TD="align: right"]10/12/2015[/TD]
[TD="align: right"]28/01/2016[/TD]
OK
[/td]A083
[/td]fred17
[/td]A075
[/td]fred18
[/td]
[TD="align: right"]23/03/2016[/TD]
[TD="align: right"]21/01/2016[/TD]
OK
[/td]A073
[/td]fred19
[/td]
[TD="align: right"]14/03/2016[/TD]
[TD="align: right"]27/01/2016[/TD]
OK
[/td]A077
[/td]fred20
[/td]
[TD="align: right"]30/03/2016[/TD]
[TD="align: right"]01/02/2016[/TD]
OK
[/td]A044
[/td]fred21
[/td]
[TD="align: right"]04/04/2016[/TD]
[TD="align: right"]27/01/2016[/TD]
OK
[/td]A066
[/td]fred22
[/td]
[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]01/02/2016[/TD]
OK
[/td]Thanks in advance Karl
[/td]
[/TABLE]
Re: Trigger needed
Attach an example workbook
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.
[Code]
Private Sub Workbook_Open()
If Application.WorksheetFunction.COUNTIF(ActiveSheet.Range("E:E"), "DUE") > 0 Then
mymsg = "≪HTML≫≪Body≫Text to appear before table"
mymsg = mymsg & "≪Table≫≪TR≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 1).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 2).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(1, 3).Value, "dd/mm/yyyy") & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(1, 4).Value, "dd/mm/yyyy") & "≪/TD≫"
mymsg = mymsg & "≪/TR≫"
For n = 2 to ActiveSheet.Range("A1").End(xlDown).Row
If ActiveSheet.Range("E" & n).Value = "DUE" Then
mymsg = mymsg & "≪TR≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(n, 1).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(n, 2).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(n, 3).Value, "dd/mm/yyyy") & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(n, 4).Value, "dd/mm/yyyy") & "≪/TD≫"
mymsg = mymsg & "≪/TR≫"
End If
Next
mymsg = mymsg & "≪/TABLE≫≪/Body≫≪/HTML≫"
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
.BodyFormat = olFormatHTML
.HTMLBody = mymsg
.To = "[email protected]" 'Put recipient email address here
.Send
End With
End If
End SubEdit: 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;789769I'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.
CodeDisplay More[Code] Private Sub Workbook_Open() If Application.WorksheetFunction.COUNTIF(ActiveSheet.Range("E:E"), "DUE") > 0 Then mymsg = "≪HTML≫≪Body≫Text to appear before table" mymsg = mymsg & "≪Table≫≪TR≫" mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 1).Value & "≪/TD≫" mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 2).Value & "≪/TD≫" mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(1, 3).Value, "dd/mm/yyyy") & "≪/TD≫" mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(1, 4).Value, "dd/mm/yyyy") & "≪/TD≫" mymsg = mymsg & "≪/TR≫" For n = 2 to ActiveSheet.Range("A1").End(xlDown).Row If ActiveSheet.Range("E" & n).Value = "DUE" Then mymsg = mymsg & "≪TR≫" mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(n, 1).Value & "≪/TD≫" mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(n, 2).Value & "≪/TD≫" mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(n, 3).Value, "dd/mm/yyyy") & "≪/TD≫" mymsg = mymsg & "≪TD≫" & Format(ActiveSheet.Cells(n, 4).Value, "dd/mm/yyyy") & "≪/TD≫" mymsg = mymsg & "≪/TR≫" End If Next mymsg = mymsg & "≪/TABLE≫≪/Body≫≪/HTML≫" Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application Set objMail = olApp.CreateItem(olMailItem) With objMail .BodyFormat = olFormatHTML .HTMLBody = mymsg .To = "[email protected]" 'Put recipient email address here .Send End With End If End Sub
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.
Private Sub Workbook_Open()
If Application.WorksheetFunction.COUNTIF(ActiveSheet.Range("E:BS"), "DUE") > 0 Then
mymsg = "≪HTML≫≪Body≫Text to appear before table"
mymsg = mymsg & "≪Table≫≪TR≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 1).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(1, 2).Value & "≪/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, 1).Value & "≪/TD≫"
mymsg = mymsg & "≪TD≫" & ActiveSheet.Cells(cell.Row, 2).Value & "≪/TD≫"
mymsg = mymsg & "≪/TR≫"
End If
Next
mymsg = mymsg & "≪/TABLE≫≪/Body≫≪/HTML≫"
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
.BodyFormat = olFormatHTML
.HTMLBody = mymsg
.To = "[email protected]" 'Put recipient email address here
.Send
End With
End If
End SubRe: 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:
Private Sub Workbook_Open()
If Application.WorksheetFunction.COUNTIF(ActiveSheet.Range("E: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 Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
.BodyFormat = olFormatHTML
.HTMLBody = mymsg
.To = "[email protected]" 'Put recipient email address here
.Send
End With
End If
End Sub
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 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
HI Simon
Progress but now getting the call out box below
[ATTACH=CONFIG]72414[/ATTACH]
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
This has now work THANKS again
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.
| Name | Module |
| 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"] |
[/TD]
[/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.
Don’t have an account yet? Register yourself now and be a part of our community!