Announcement

Collapse
No announcement yet.

E-Mail trigger from a cell in Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • E-Mail trigger from a cell in Excel

    Can't wait to get some help! I have checked some other forums on this topic, but they differ enough from my needs and I have limited enough knowledge of macros that I'm having difficulty.

    I would like, if conditions are met within a cell in an Excel file (I have already set up the cell with conditions using an IF formula), for an e-mail to be sent (no attachment needed) to 3 people.

    Once that is done, I will need this trigger to happen when the same conditions are met in other cells (they are also formatted with an IF formula).

    I think I have a start (and feedback will let me know this), but I am having trouble duplicating that macro to the other cells.

    Thanks!!!!!

  • #2
    Re: E-Mail trigger from a cell in Excel

    http://forums.techguy.org/business-a...cro-excel.html

    As they said, its rather hard to get it to Email out automaticlly, due ot secruity.

    You might be able to use BLAT or even get it to create a event in the event log, and get that to send you the email.

    I am not a big Excel user, but it might help if we could have abit of background on what you would like the result to be.

    Wofen
    Good to be back....

    Comment


    • #3
      Re: E-Mail trigger from a cell in Excel

      Can you post your macro so we can look at it
      I assume Excel 2007 but what email client are you using, and what mail server?
      Tom Jones
      MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
      PhD, MSc, FIAP, MIITT
      IT Trainer / Consultant
      Ossian Ltd
      Scotland

      ** Remember to give credit where credit is due and leave reputation points where appropriate **

      Comment


      • #4
        Re: E-Mail trigger from a cell in Excel

        I have a spreadsheet that is already conditionally formatted to change certain cells to red if conditions are met - these are employee performance thresholds, and the "red" feature is designed to alert supervisors when an employee reaches a certain threshold (such as poor attendance). I created another row with cells that change to "yes" if the first cell has its criteria met and turns to red. What I want to happen is that when that cell goes to "yes", an e-mail is automatically sent to the supervisor. This way supervisors won't have to remember to check the form say, every day, in order to keep up with employee performance. They will receive an e-mail and then know that action must be taken. The overall purpose is to keep supervisors informed in a timely manner so that poor performance doesn't go unnoticed for extended periods of time.

        Comment


        • #5
          Re: E-Mail trigger from a cell in Excel

          Yes, but what is the macro you are using?

          If you just want to see how to trigger it, look at a link here:
          http://www.google.co.uk/search?hl=en...meta=&aq=f&oq=
          Tom Jones
          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
          PhD, MSc, FIAP, MIITT
          IT Trainer / Consultant
          Ossian Ltd
          Scotland

          ** Remember to give credit where credit is due and leave reputation points where appropriate **

          Comment


          • #6
            Re: E-Mail trigger from a cell in Excel

            Sub PAT_Email_Trigger()
            '
            ' PAT_Email_Trigger Macro
            '
            '
            Application.Dialogs(xlDialogSendMail).Show
            End Sub

            Comment


            • #7
              Re: E-Mail trigger from a cell in Excel

              I've been working some more on this. How does this look for the sheet code (VB):
              Private Sub Worksheet_Calculate()
              Dim FormulaRange As Range
              Dim NotSentMsg As String
              Dim MyMsg As String
              Dim SentMsg As String
              Dim MyLimit As Double

              'Above the MyLimit value it will run the macro
              MyLimit = 1
              'Set the range with the Formula that you want to check
              Set FormulaRange = Me.Range("BN5")
              On Error GoTo EndMacro:
              For Each FormulaCell In FormulaRange.Cells
              With FormulaCell
              If IsNumeric(.Value) = False Then
              MyMsg = "Not numeric"
              Else
              If .Value > MyLimit Then
              MyMsg = SentMsg
              If .Offset(0, 1).Value = NotSentMsg Then
              Call Mail_with_outlook1
              End If
              Else
              MyMsg = NotSentMsg
              End If
              End If
              Application.EnableEvents = False
              .Offset(0, 1).Value = MyMsg
              Application.EnableEvents = True
              End With
              Next FormulaCell
              ExitMacro:
              Exit Sub
              EndMacro:
              Application.EnableEvents = True
              MsgBox "Some Error occurred." _
              & vbLf & Err.Number _
              & vbLf & Err.Description
              End Sub

              and in the Module code:
              Option Explicit
              Sub Mail_small_Text_Outlook()
              'Working in Office 2000-2010
              Dim OutApp As Object
              Dim OutMail As Object
              Dim strbody As String
              Set OutApp = CreateObject("Outlook.Application")
              Set OutMail = OutApp.CreateItem(0)
              strbody = "A has reached the following performance threshold" & vbNewLine & vbNewLine & _
              "Errors" & vbNewLine & _
              "Please return the completed PAT Assessment Action Form to X within 4 business days"
              On Error Resume Next
              With OutMail
              .To = "[email protected]"
              .CC = ""
              .BCC = ""
              .Subject = "A has reached the following performance threshold"
              .Body = strbody
              'You can add a file like this
              '.Attachments.Add ("M:\PSC\PSC Ops\QA Tracking\FY11 QA Tracking\201010\201010_PSC_QAmeasures_FAR.xls")
              .Display 'or use .Send
              End With
              On Error GoTo 0
              Set OutMail = Nothing
              Set OutApp = Nothing
              End Sub

              Do I need both of these? I'm trying to figure this out following various internet "instructions" with a pretty limited knowledge of macros. LOL. I just know that I macro is needed to achieve what I want.

              The Errors column is calculated automatically, and the column to the right is an IF statement to change depending on what's in the errors column. I want an e-mail to be sent with the column to the right of errors column goes to "Yes".
              Last edited by mbridget1; 15th October 2010, 20:59.

              Comment

              Working...
              X