Formula and macro possibly

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:38 PM GMT

Question

QUESTION: Multi-part question.

In my spreadsheet for current inventory, i have it set as =if(x-y<0,x+y,x-y) Is there any way to add another if statement to say, =if(x-y=0,"",x-y) without it being false because of the "circular formula"? I need to to be blank if it is equal to zero, but also need the formula to work if its not. I tried having another cell's formula repopulate the cell with this info, but it did not work, but i may have been doing it incorrectly.

Also, with it being inventory, once it gets to a "low" amount, i need it to send a certain email address a warning e-mail. The code i came up with was

Sub Mail_Outlook_Express()

   Dim Recipient As String, Subj As String, HLink As String
   Dim Recipientcc As String, Recipientbcc As String
   Dim msg As String
   Recipient = ""
   Recipientcc = ""
   Recipientbcc = ""
   Subj = "Inventory Low"
   msg = "Just a reminder" & vbNewLine & vbNewLine & _
         "Inventory is dangerously low"
   msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
   HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc _
         & "&" & "bcc=" & Recipientbcc & "&"
   HLink = HLink & "subject=" & Subj & "&"
   HLink = HLink & "body=" & msg
   ActiveWorkbook.FollowHyperlink (HLink)
   Application.Wait (Now + TimeValue("0:00:02"))
   Application.SendKeys "%s"

End Sub


Now, what i cant figure out is how to create a macro to auto send this e-mail when a cell, or column is equal to or less than a certain amount. Or even if that email code is what i need for an auto e-mail. When it is run, it brings upand creates the message, but does not send it automatically.

Any help would be greatly appreciated.

ANSWER: well done for working out a way of creating a mail - follow a mail hyperlink will indeed generate a mail message, but as you've found, not send it - see http://www.rondebruin.nl/sendmail.htm for a full description of how to code emails

---------- FOLLOW-UP ----------

QUESTION: Thanks for responding so quickly Aidan, Unfortunately, it is not of much help. That website is the same site that showed me how to create and code that e-mail script. I am still having issues of how to automate it to run the script when a cell gets below 5000 units. The cell will reach the amount below 5000 when someone imputs an order reducing it to that amount. also creates a red highlight at 5000. can you help me with the macro and email script, or possibly direct me in the correct direction.


Answer

My apologies ¨C you will need to use the events on the worksheet- I think I would probably do Private Sub Worksheet_Calculate() If range(¡°YourCell¡±)<5000 then ¡®do the email ¨C does it need to test if one has already gone? End if End Sub

You can use format, conditional formatting to set the shading you want

My email if you want to discuss this further (which would allow attachments to be sent) is aidan.heritage@virgin.net

Advertisement

©2017 eLuminary LLC. All rights reserved.