+ Reply to Thread
Results 1 to 10 of 10

Send automated email if value in cell is value

Hybrid View

DelKolio Send automated email if value... 07-16-2014, 08:00 AM
nathansav Re: Send automated email if... 07-16-2014, 08:01 AM
DelKolio Re: Send automated email if... 07-16-2014, 08:05 AM
nathansav Re: Send automated email if... 07-16-2014, 08:16 AM
DelKolio Re: Send automated email if... 07-16-2014, 08:20 AM
nathansav Re: Send automated email if... 07-16-2014, 08:45 AM
DelKolio Re: Send automated email if... 07-16-2014, 08:47 AM
nathansav Re: Send automated email if... 07-16-2014, 08:48 AM
DelKolio Re: Send automated email if... 07-16-2014, 08:50 AM
nathansav Re: Send automated email if... 07-16-2014, 08:53 AM
  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    5

    Send automated email if value in cell is value

    Hi,
    I have an excel sheet where automatically live data comes in.
    When the cells reach a certain value the value in cell R1 becomes 1.
    I want to make a macro in vba that sends me ONE email if the value becomes 1.
    I tried :
    Public Function MailS(mailtrigger As Double) As Double
    If mailtrigger = 1 Then
    Call makeandsendemail
    Application.Run "makeandsendemail"
    MailS = 1
    Else
    MailS = 1
    End If
    End Function
    But this code send millions of emails.
    Can someone help me?

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Send automated email if value in cell is value

    The code you've posted is no help, when is MailS called, what is MakeAndSendEmail?
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    5

    Re: Send automated email if value in cell is value

    The code above is a function. I put this somewhere in my sheet with mailtrigger = R1. If the mailtrigger is 1 the function fires the MakeAndSendEmail continiously until R1 is an other value again.

    The MakeAndSendEmail is a sub that sends me the email (this works already).
    Where I'm looking for is a code that fires the MakeAndSendEmail sub once when the R1 changes to 1.


    p.s. things as
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("A2") Then
    'Do some coding here
    End If
    End Sub

    don't work because I don't change cells actively in my sheet. (the sheet is running on the background)
    Last edited by DelKolio; 07-16-2014 at 08:08 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Send automated email if value in cell is value

    Are you changing R1 by VBA then? What is calling MailS?

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    5

    Re: Send automated email if value in cell is value

    R1 is the sum of other cells in the notebook.
    Excel is connected to an other program that gives the data.
    MailS is the name of the function I created, when I put this in my notebook it sends me mails when the mailtrigger = 1. Where my mailtrigger = R1.
    Problem is it doesn't stop.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Send automated email if value in cell is value

    Yes, what calls the function. I understand what you are trying to do and what a function is, i need to know whats calling it, for example

    if date=date then MailS for example or if a=1 and b=2 then Mails.

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    5

    Re: Send automated email if value in cell is value

    if R1 = 1 than mail one time

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Send automated email if value in cell is value

    Sorry i cant help you anymore, i fully understand that if R1=1 then call the sub,but how are you calling it. This is what will be making it happen lots

    All the best.

  9. #9
    Registered User
    Join Date
    07-16-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    5

    Re: Send automated email if value in cell is value

    I made a function for it in VBA which I put in a random cell in the worksheet. Code is above...
    I think I don't understand you...

  10. #10
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Send automated email if value in cell is value

    What is the VBA trigger to run MailS, what is checking if R1=1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Workflow with signatures (if cell=x then send email, if signed then send email)
    By Kate2811 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-25-2014, 05:37 AM
  2. How to send email from excel using VBA with Cell Range (Including Images) as Email Body
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2014, 05:06 AM
  3. Macro to send automated email reminders
    By Amber12 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-13-2014, 11:22 AM
  4. command button VBA to send automated email not workbook or worksheet
    By lpratt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-08-2012, 11:13 PM
  5. Automated email to send worksheet as report
    By wellseytd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2011, 10:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1