Advanced question about Excel?

Question

Here is what I want to happen. My inventory is recorded on an excel spreadsheet. Every day the spreadsheet is updated and saved. I want to write a macro that will automatically generate an email if the inventory falls below a certain set percentage. So basically, I want to have conditional formatting generate an email if the criterion is not met. Thank you in advance

Answer 1

Here is the method you can use to automatically send an email listing all SKU's that are less than 50% of Maximum Stock Levels when the workbook is closed.



First, add a new worksheet to your workbook and name it 'Low' (no quotes).



Then, change the email address '[e-mail]' in the code below to the actual email address that you wish to use.



If the worksheet containing your data is not named "Sheet1", change the two "Sheet1" references in the code to your actual sheet name, i.e. "Data", "Main", etc.



Then, copy the following function/macros, modified as required, to the clipboard:



Private Declare Function ShellExecute Lib "shell32.dll" _

Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _

ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long



Sub SendEMail()

Dim Email As String, Subj As String

Dim Msg As String, URL As String, LastRow

LastRow = Sheets("Low"). Range("A" & Rows.Count).End(xlUp).Row

Email = "[e-mail]"

Subj = "Low Inventory Alert"

Application.ScreenUpdating = False

For i = 2 To LastRow

SKUs = SKUs & Cells(i, "A").Value & " | "

Next

Msg = Left(SKUs, Len(SKUs) - 10)

Subj = Application.Substitute(Subj, " ", "%20")

Msg = Application.Substitute(Msg, " ", "%20")

Msg = Application.Substitute(Msg, vbCrLf, "%0D%0A")

URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Application.Wait (Now + TimeValue("0:00:02"))

Application.SendKeys "^~"

Sheets("Sheet1").Select

Sheets("Low").Visible = xlVeryHidden

End Sub



Sub Create_List()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Low").Range("A:A"). ClearContents

Sheets("Sheet1").Activate

Application.ScreenUpdating = False

For i = 1 To LastRow

If Cells(i, "C").Value < 0.5 Then

Cells(i, "A").Copy Destination:=Sheets("Low"). _

Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next

If Application.CountA(Sheets("Low"). Range("A:A")) = 0 Then

MsgBox "There are no SKUs below 50%", vbInformation, "No Alerts"

Exit Sub

Else

Sheets("Low").Visible = xlSheetVisible

Sheets("Low").Select

SendEMail

End If

End Sub





Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the function/macros into the editing area to the right.



Double click 'THIS WORKBOOK' in the Microsoft Excel Objects in the upper left quadrant.



Change the "Sheet1" reference below to your sheet name, i.e. "Data", then copy and paste the following event handler into the editing area to the right:



Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Sheet1").Activate

Create_List

End Sub



Close the VBE, thus returning to the worksheet.



Save the workbook.



Closing the workbook will trigger the creation and sending of the email containing the inventory alerts.



This has been thoroughly tested, and performs appropriately. However, advise if you encounter any problems/difficulties.

Answer 2

Garbo is correct. Without at least dummy information occupying actual cell locations and a realistic dummy address to email to, your problem can't be addressed.



Your problem will require VBA and the code requires real information in order to be written.

Answer 3

You might be better off using an inventory management software other than excel, which specialize in offering more useful features than what excel does. Depending on how many products your company has, you could probably find one for free.

Answer 4

Find for solution in www.youtube.com