Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
Thanks for clarifying that. I don't know what I am doing, but it is not working. I must be making a mistake with the select cases. "Code for widgets" does that mean I write for example what row it is? Sorry for asking such a silly question, but I really have no clue at all here. Thanks again for your help. -- JBCIB ------------------------------------------------------------------------ JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794 View this thread: http://www.excelforum.com/showthread...hreadid=535620 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
Yep. That's exactly what you need to do.
There may be different ways to loop through those rows--but depending on what the email should say and what your flags/cutoffs are, you may need to code each option explicitly. An alternative... Put another column in your worksheet that creates the email text that you want. Use whatever worksheet formulas you need. Then add another column for you cutoff values (Column T??). Then you can just check the flag column (column S??) and create the email using that cell. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim myAddress As String Dim mySubject As String Dim myBody As String Dim myHyper As String Set wks = Worksheets("Sheet1") myAddress = " With wks Set myRng = .Range("A1:A143") For Each myCell In myRng.Cells If .Cells(myCell.Row, "S").Value < .Cells(myCell.Row, "T").Value Then 'do nothing, not at the cutoff Else mySubject = myCell.Value & "-- some nice text here" 'use that cell with the formula that creates the email text myBody = .Cells(myCell.Row, "U").Value myHyper = "mailto:" & myAddress _ & "?subject=" & mySubject _ & "&body=" & myBody ActiveWorkbook.FollowHyperlink myHyper End If Next myCell End With End Sub But somewhere, somehow you have to tell excel/vba what to do. And if you use this kind of technique, you can use a formula like: Body = "The " & Range("A13") & " inventoryshows a flux of " & Format(Range("D13").Value, "$##,###") & ="The " & a1 & " inventory shows a flux of " & text(a13,"$##,###) & "." And you could include other cells in formulas...and drag down. You can even modify each formula so that each returns something slightly different. JBCIB wrote: Thanks for clarifying that. I don't know what I am doing, but it is not working. I must be making a mistake with the select cases. "Code for widgets" does that mean I write for example what row it is? Sorry for asking such a silly question, but I really have no clue at all here. Thanks again for your help. -- 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
I'll give this a try as well David. Thanks:) -- JBCIB ------------------------------------------------------------------------ JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794 View this thread: http://www.excelforum.com/showthread...hreadid=535620 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending automatic e-mail from Xl
That worked great David! thanks again!:) :) -- JBCIB ------------------------------------------------------------------------ JBCIB's Profile: http://www.excelforum.com/member.php...o&userid=33794 View this thread: http://www.excelforum.com/showthread...hreadid=535620 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) | |||
Sending E-mail | Excel Worksheet Functions | |||
Help sending mail... | Excel Programming | |||
Automatic CC Entry in Sending Mail | Excel Programming |