Excel autogenerating email based on criteria in the sheet.

[kc5sdy]

Please bare with me. My knowledge of Excel and what it can do only rivals the intelligence of dirt. I am in need of a lot if help.

I have a spreadsheet that shows the inventory I have in my store room. In the spreadsheet, I have the description, number of units on hand, number of units started with, part numbers, and a formula that is basically this... (original amount)-(amount on hand)/(box quantity for part)=Number of boxes to order.

In addition to this, I have conditional formatting for each part number. When the quantity gets to be 1/2 the original quantity, the entire line is highlighted in yellow. That the quantity drops below 1/2, it then goes to red.

Still with me so far? What I want to know is, would it be possible to have excel automatically generate an email with a particular subject line, and in the body state that part number xxxxxxx is at or below reorder level. x number of boxes need to be ordered ASAP.

Or something to that effect. Can this be done? If so, how? Keep in mind, that figuring out the conditional formatting was a stretch for my intelligence with Excel. I can send the spreadsheet to anyone that needs to see it in order to understand just what I am trying to explain.

Thanks in advance for any help.

 

[kc5sdy]

Re: Excel autogenerating email based on criteria in the shee

Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you.

That worked like a charm. I had a slight problem getting the code to run at first but I found where that problem was. This is exactly what I need. The code was a lot more than I imagined it would be. Now to see if I can understand any of it and see if I can reverse engineer it.

BTW... Did I say Thank you?

 

[dk]

Re: Excel autogenerating email based on criteria in the shee

Glad you got it working

Dan

 

[kc5sdy]

Re: Excel autogenerating email based on criteria in the shee

I really do appreciate that. This is going to make my life a lot easier. With luck, this just may be implemented at other locations if the boss type man likes it.

Now, to see if I can wire my partners chair up to shock him when he does not keep the inventory list updated....

Again, thank you for all your help.

 

[smnhicks]

Re: Excel autogenerating email based on criteria in the shee

I'd like to add my thanks to everyone who contributed to this thread. I've amended the code slightly to suit my product ordering spreadsheet.

One thing that would be helpful to me is the ability to add line returns to the body text of an e-mail. Basically, my e-mail says "Dear whoever, Please send the following product to the following address". It would make life easier for the guys sending out the orders if I could put the address on a different line.

Does anyone know if this is possible?

Thanks,

Simon

 

[Ekim]

Re: Excel autogenerating email based on criteria in the shee

Insert a standard module (VBA editor – Insert menu | Module), and copy/paste the following:

Public Const L1 = vbCrLf                      ' Single line feed
Public Const L2 = vbCrLf & vbCrLf          ' Double line feed

In the email macro (Sheet module):

"Dear whoever,” & L2 _
& “Please send the following product to the following address". & L2 _
& “23 James Street” & L1 _
& “Attadale” & L1 _
& “Perth” & L1 _
& “Western Australia 6156.”


By using a public constant to define “vbCrLf” (carriage return–linefeed combination), it makes in easier in my view to format a message.

HTH

Mike