1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Auto Email based on cell value

Discussion in 'Business Applications' started by Weered, May 25, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Weered

    Weered Thread Starter

    Joined:
    May 25, 2015
    Messages:
    5
    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.

    [​IMG]


    Thoughts? and many many thanks in advance.
     
  2. Weered

    Weered Thread Starter

    Joined:
    May 25, 2015
    Messages:
    5
    I am too new to send him a PM so I hope he stumbles across this. He was active a few days ago.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    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. Weered

    Weered Thread Starter

    Joined:
    May 25, 2015
    Messages:
    5
    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. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    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. Weered

    Weered Thread Starter

    Joined:
    May 25, 2015
    Messages:
    5
    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. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    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. Weered

    Weered Thread Starter

    Joined:
    May 25, 2015
    Messages:
    5
    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.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1148802

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice