Hello all, this is my first post here. Seeing how people are amazingly
good at excel, I definately could use some of your help.:)

Basically here is the situation, I have a list of items (ranges from
cellA1 to A143). I want to create an automatic e-mail when each item
gets to a certain number (for example resistors go down to 200, I need
an e-mail to remind me to purchase more). I managed to write a macro
with VBA that works fine when I refer one specific cell. Here is what I
had written:

Private Sub Worksheet_Calculate()

Flux = Range("S13").Value
If Flux 500 Then

Dim Address As String, Subject As String
Dim Body As String, Hyper As String

Address = "...(I had a valid e-mail in here)"
Subject = "The " & Range("S13") & " desk has a flux above $500mm"

Body = "The " & Range("A13") & " inventoryshows a flux of " &
Format(Range("D13").Value, "$##,###") &

Hyper = "mailto:" & Address & "?subject=" & Subject & "&body=" & Body
ActiveWorkbook.FollowHyperlink (Hyper)


End If
End Sub

So I want to know if somebody could help me set this up to look at the
range of cells and send me an e-mail for each individual item.
Basically what I need excel to do is look at every item in my inventory
and send me an email for each item individually (I want to see one item
per email). If anybody could help me or point me as to what to change I
would greatly appreciate it.

I am an extreme noob when it comes to VBA:( so thanks for any help!:)


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

Private Sub Worksheet_Calculate()
Dim Address As String, Subject As String
Dim Body As String, Hyper As String
Static Flag(1 to 143) as Boolean
for i = 1 to 143

Select case i
Case 1 ' widgets
' code for widgets
Case 2 ' deltas
' code for deltas
. . .
Case 13 ' code for flux
Flux = Range("S" & i).Value
If Flux 500 Then
if Flag(i) = False then
Flag(i) = True
Address = "...(I had a valid e-mail in here)"
Subject = "The " & Range("S" & i) & " desk has a flux above $500mm"
Body = "The " & Range("A" & i) & " inventoryshows a flux of " & _
Format(Range("D" & i).Value, "$##,###") &
Hyper = "mailto:" & Address & "?subject=" & Subject & "&body=" & Body
ActiveWorkbook.FollowHyperlink (Hyper)
end if
else
Flag(i) = False
end if

. . .
end Select
Next i

End If
End Sub

--
Regards,
Tom Ogilvy


"JBCIB" wrote:


Hello all, this is my first post here. Seeing how people are amazingly
good at excel, I definately could use some of your help.:)

Basically here is the situation, I have a list of items (ranges from
cellA1 to A143). I want to create an automatic e-mail when each item
gets to a certain number (for example resistors go down to 200, I need
an e-mail to remind me to purchase more). I managed to write a macro
with VBA that works fine when I refer one specific cell. Here is what I
had written:

Private Sub Worksheet_Calculate()

Flux = Range("S13").Value
If Flux 500 Then

Dim Address As String, Subject As String
Dim Body As String, Hyper As String

Address = "...(I had a valid e-mail in here)"
Subject = "The " & Range("S13") & " desk has a flux above $500mm"

Body = "The " & Range("A13") & " inventoryshows a flux of " &
Format(Range("D13").Value, "$##,###") &

Hyper = "mailto:" & Address & "?subject=" & Subject & "&body=" & Body
ActiveWorkbook.FollowHyperlink (Hyper)


End If
End Sub

So I want to know if somebody could help me set this up to look at the
range of cells and send me an e-mail for each individual item.
Basically what I need excel to do is look at every item in my inventory
and send me an email for each item individually (I want to see one item
per email). If anybody could help me or point me as to what to change I
would greatly appreciate it.

I am an extreme noob when it comes to VBA:( so thanks for any help!:)


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

Thanks for your reply!:) Just so I understand correctly, every time you
wrote case 1'widgets'.... I basically am listing out every item in my
inventory? Thanks again. I am going to try this now.


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

i loops from 1 to 143. Inside the loop, you use the case statement to write
code specific to that row

Case 1 ' for example row 1 is widgets
' code for widgets
Case 2 ' for example row 2 is blocks
' code for blocks

.. . .
Case 143 ' for example, row 143 is Pulleys
' code for pulleys

end Select

--
Regards,
Tom Ogilvy


"JBCIB" wrote:


Thanks for your reply!:) Just so I understand correctly, every time you
wrote case 1'widgets'.... I basically am listing out every item in my
inventory? Thanks again. I am going to try this now.


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

Thought so. Thanks for your help and clarifying that. I'll post later
how all this works out.:) :)


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

I keep getting an error when running it. It says:

End If without block if

Are we missing an If statement somewhere?


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

I keep getting an error when running it. It says:

End If without block if

Are we missing an If statement somewhere?


--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

In Tom's sample code, the last two lines:

End If
End Sub

should be:

Next i
End Sub

(just a typo)

JBCIB wrote:

I keep getting an error when running it. It says:

End If without block if

Are we missing an If statement somewhere?

--
JBCIB
------------------------------------------------------------------------
JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794
View this thread: http://www.excelforum.com/showthread...hreadid=535620

--

Dave Peterson