Excel autogenerating email based on criteria in the sheet.

kc5sdy

New Member
Joined
Jul 21, 2004
Messages
9
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Re: Excel autogenerating email based on criteria in the shee

I am still banging my head on this one trying to figure out if it can be done or not. I have seen similar posts to this but not quite what I am in need of.

I would appreciate it if I was at least pointed in the right direction. I don't want to be held by the hand unless it is that far over my head.

Is anyone even trying to help me on this or am I being left in the cold?
 
Upvote 0
Re: Excel autogenerating email based on criteria in the shee

Do you use Microsoft Outlook?
 
Upvote 0
Re: Excel autogenerating email based on criteria in the shee

Here is an explenation of what you should see. Line 2 and 5 should be yellow and line 4 should be red. This is the conditional formatting that I have put in. basically, the formula for that is
(Quantity on hand/(Original Quantity-Adjusted Threshold))=0.5 -Makes the line yellow-
(Quantity on hand/(Original Quantity-Adjusted Threshold))<0.5 -Makes the line red-

The idea is if it is yellow, it is an alert stating that a particular part is starting to run low. Red means that it is low and needs to be ordered.
The Adjusted Threshold works this way. If I have a part that I am constantly using, then I can enter a negative number to show how many units sooner I want the line to appear yellow or a positive number showing how many units later it should trigger for a part that is rarely used. This way, I can keep my frequently used items stocked and the rarely used items can be fewer is volume and free up more space for the more used items.

Where I am wanting the email to be generated is when the conditional formatting would make the line red.
Supply Inventory.xls
ABCDEFG
1Unit DescriptionItem DescriptionQuantity On HandOriginal QuantityPart NumberBox Units to OrderAdjusted Threshold
2WCP35/55Toner34406R1046(2ct) 6R1047(4ct)17-28
3WCP35/55Staples2021108R00493(3ct)0.333333333
4WCP35/55Fuser05109R636(1ct)5
5WCP35/55Xerographic Unit15109R608(1ct)13
Xerox Inventory
 
Upvote 0
Re: Excel autogenerating email based on criteria in the shee

Hi,

I've been playing around with this and got something that seems to work quite nicely. This code needs to have another column so that it can know whether or not an e-mail has already been sent (I'm assuming that once an e-mail is sent you don't want to keep on sending e-mails). Here is how your sheet will need to look:-
Supply Inventory.xls
ABCDEFGH
1Unit DescriptionItem DescriptionQuantity On HandOriginal QuantityPart NumberBox Units to OrderAdjusted ThresholdE-mail Sent
2WCP35/55Toner34406R1046(2ct) 6R1047(4ct)17-28
3WCP35/55Staples2022108R00493(3ct)0.666666667
4WCP35/55Fuser15109R636(1ct)4Yes
5WCP35/55Xerographic Unit15109R608(1ct)13
Xerox Inventory


The next step is to add the VBA code that will check for products that need reordering. Right click the worksheet tab and paste this code:-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oOutlookApp As Object, oOutlookMsg As Object


    'Don't need to run any code if these rows don't have conditional formatting
    If Me.Range("G" & Target.Row).FormatConditions.Count< 1 Then Exit Sub


    'Is the conditional formatting condition false?
    If Me.Range("C" & Target.Row).Value / (Me.Range("D" & Target.Row).Value - Me.Range("G" & Target.Row)) >= 0.5 Then
        Application.EnableEvents = False
        Me.Range("H" & Target.Row).Value = Null
        Application.EnableEvents = True
        Exit Sub
    End If

    'The formatting condition is true.  If we've already sent an e-mail we don't want to do it again
    If StrComp(Me.Range("H" & Target.Row).Value, "Yes", vbTextCompare) = 0 Then Exit Sub


    '****** CODE TO SEND E-MAIL HERE ******
    On Error Resume Next
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err.Number<> 0 Then Set oOutlookApp = CreateObject("Outlook.Application")


    Set oOutlookMsg = oOutlookApp.createitem(0)

    'Set the properties of the message.

    With oOutlookMsg
        .to = "someone@somewhere.com"
        .Subject = "Order action required"
        .body = "Part number " & Me.Range("E" & Target.Row).Value & " is at or below re-order level.  " & _
                Me.Range("F" & Target.Row).Value & " boxes need to be ordered ASAP."
        .display
    End With


    Application.EnableEvents = False
    Me.Range("H" & Target.Row).Value = "Yes"
    Application.EnableEvents = True


End Sub

Now if everything is working as I'd hope, you should get an email generated whenever you change a cell which causes a product to need reordering. Let me know how you get on ;)

Dan
 
Upvote 0
Re: Excel autogenerating email based on criteria in the shee

Dan,

Very nice. Works like a charm.

If I can make a suggestion – put the supplier email addresses in column J and change this line in the macro:

.to = someone@somewhere.com

to:

.to = Me.Range("J" & Target.Row).Value

Another suggestion is to convert the macro into a normal macro that collates like emails i.e. at the end of the day, one email is sent to each supplier for a range of products to be supplied. However, I think this is entering the realms of a full-blown project that quite frankly, you should receive a fee.

Regards,

Mike
 
Upvote 0
Re: Excel autogenerating email based on criteria in the shee

Thank you. I will give it a try and see what happens.

I don't need to send it to more than 3 people anyway, myself, my partner, and my supervisor. So, I don't need to go that far into it.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top