- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.
I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.
One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.
Hi, welcome to the forum,
I understand what you require and I think I can help too.
I'm sure Zack will see this post too, but in any case I've helped several Excel and email questions too.
What are the trigger values and in which fields?
Let's make it as simple as possible. I can expand on it after the fact.
Lets say, putting all other triggers and calculations aside, that when all the ideal conditions are met a simple IF formula will display a number 1 in a cell. Alternates between 0 and 1. 1 would be the email trigger. Lets say that happens in cell A1.
So A1=1
This triggers the macro to email the contents of cell A2; which will also be variable data... not really relevant
Once that email is sent, In cell A3 the macro will place a number 1 indicating the email was sent and not to send another email until cell A1 resets and returns to 0 again. So when Cell A1=0 then cell A3=0. I can write the formula to reset cell A3 but the macro will have to put the 1 in it confirming the email was sent.
This all makes sense, but do you already have a macro?
All you need to do is add the action to change the value in A3 from 0 to 1
This is code I use, all you need to do is pass the parameters
Code:
Function MailData(mSubject As String, mMessage As String, Sendto As String, _
Optional CCto As String, Optional BCCto As String, Optional fAttach As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .Cc = CCto
If Len(Trim(BCCto)) > 0 Then
.Bcc = BCCto
Else
.Bcc = ""
End If
.Body = getMailMessageText & Chr(13) & Chr(13)
If Not IsMissing(fAttach) Then .Attachments.Add (fAttach) ' Must be complete path'and filename if you require an attachment to be included
.Save ' This property is used when you want to saves mail to the Concept folder
' .Display ' This property is used when you want to display before sending
.Send ' This property is used if you want to send without verification
End With
Set app = Nothing
Set Itm = Nothing
End Function
You will need to add the part that changes A3 value to 1 when it's complete
Yours look a bit cleaner. I got one working i took apart from another template. The issue now is for it to constantly be looking for changes and executing every minute or so. I have code for that somewhere.
Thoughts?
Code:
Sub email()
Dim i As Integer
Dim lRow As Integer
Dim toList As String
Dim eSubject As String
Dim eBody As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To lRow
If Cells(i, 10) > 0 And Cells(i, 11) = "No" And Cells(i, 13) <> "Yes" Then
Set outApp = CreateObject("Outlook.Application")
Set OutMail = outApp.CreateItem(0)
toList = Cells(i, 4) 'gets the recipient from col D
eSubject = "Test"
eBody = "Hi " & Cells(i, 5) & vbCrLf & vbCrLf & vbCrLf & _
"The RSI for currency pair" & Cells(i, 5) & " Is" & vbCrLf & vbCrLf & _
"RSI: " & Cells(i, 6) & vbCrLf
On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.bodyformat = 1
'.Display ' ********* Creates draft emails. Comment this out when you are ready
.Send '********** UN-comment this when you are ready to go live
End With
Cells(i, 13) = "Yes" 'Marks the row as "email sent in Column M"
On Error GoTo 0
Set OutMail = Nothing
Set outApp = Nothing
End If
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
Is there a macro that updates the data?
In that case how do you trigger that one?
You could use Application.Ontime function which can be used to schedule an event every x minutes and at the same time the macro to send the email is triggered and will only do so if the conditions are met.
The data is updated just from 21 data connections in the excel functionality.
I have a working fix!
In the worksheet (email) code I placed:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Application.Run "'Trade Tool.xls'!email"
'your code here
End If
End Sub
Then I altered a macro or two to hack this together.
Code:
Sub email()
Dim i As Integer
Dim lRow As Integer
Dim toList As String
Dim eSubject As String
Dim eBody As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To lRow
If Cells(i, 5) > 0 And Cells(i, 7) = "No" Then
Set outApp = CreateObject("Outlook.Application")
Set OutMail = outApp.CreateItem(0)
toList = Cells(i, 6) 'gets the recipient from col D
eSubject = "Test"
eBody = "Hi " & Cells(i, 4) & vbCrLf & vbCrLf & vbCrLf & _
"The RSI for currency pair" & Cells(i, 1) & " Is" & vbCrLf & vbCrLf & _
"RSI: " & Cells(i, 2) & vbCrLf
On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.bodyformat = 1
'.Display ' ********* Creates draft emails. Comment this out when you are ready
.Send '********** UN-comment this when you are ready to go live
End With
Cells(i, 7) = "Yes" 'Marks the row as "email sent in Column M"
On Error GoTo 0
Set OutMail = Nothing
Set outApp = Nothing
ElseIf Cells(i, 5) = 0 Then
Cells(i, 7) = "No"
End If
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
Application.OnTime Now + TimeValue("00:00:30"), "email"
End Sub
So when I trigger it by altering cell B2 it cycles every minute and works perfect. Resets itself based on values and everything.
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!