Tech Support Guy banner
Status
Not open for further replies.

Excel - Auto Email based on cell value

7K views 8 replies 2 participants last post by  Keebellah 
#1 ·
Hi,

New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- 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.



Thoughts? and many many thanks in advance.
 
See less See more
1
#3 ·
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?
 
#4 ·
Thank you Keebellah!

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.

Does that make sense?
 
#5 ·
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
 
#6 ·
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
 
#7 ·
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.

I have no idea how you're updating your data
 
#8 ·
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.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top