Using Excel / VBA to automatically email forms - how?

[funperro]

I am currently in the planning stages of a small project at work. Le
me explain the project quickly.. I'd like to get some input if
could:

At my work, we have a warehouse that houses all of our parts for ou
equipment. The problem is this: the guys that work in the warehous
do not have a system to monitor what goes in and out (except for
terrible paperwork 'system' they use). We don't know when we run ou
of parts until we actually run out - which is not funny, as you ca
imagine!

My idea is this: I want to set up a simple excel list of part number
that our inventory staff won't freak out on (ins and outs, etc.).
Basically, when a box of parts is received, I want them to look up th
specific part number and add to its total in their excel spreadsheet.
When a part is removed from the warehouse, they would subtract fro
that specific part number's total. Seems easy for most, right? You'
be surpised with our guys!

I would then like to have a second excel worksheet (locked, of course!
that would retrieve the part number totals. When these parts get belo
a certain quantity, this excel worksheet would then send an email t
the people that are responsible for ordering parts. This workshee
would contain all the part numbers, and each part number would hav
information with it such as Supplier info, costs, wait time, etc. Thi
email would provide all the information needed to make the order
nothing more.

Can I do this? Can I have excel somehow send out these email
automatically when these minimum part quantities occur?

Any ideas suggestions are *much* appreciated!

Andre

 

[Tom Ogilvy]

Ron de Bruin's site

http://www.rondebruin.nl/sendmail.htm
http://www.rondebruin.nl/index.html

an Article by Ron on MSDN:
http://msdn.microsoft.com/library/en-us/odc_xl2003_ta/html/odc_xlmail.asp

Dick Kusileka's site
http://www.dicks-clicks.com


Show the mechanics of sending an email.

You can use the change event in your code to trigger the determination of
when to send it.

This explains events (Chip Pearson's site)

http://www.cpearson.com/excel/events.htm

 

[funperro]

Tom -

Thanks for your quick reply. I glanced over the links and am stil
wondering if its possible to send email through excel - or is a clien
necessary?

Thanks -
Andre

 

[Tom Ogilvy]

Excel doesn't provide any specific capability for it, but if you look at
Ron's information on CDO, it doesn't necessarily need a client, but it does
need the underlying foundations such as smtp.

 

[funperro]

Ok. I have figured out how to make it send an email via CDO. It work
great! Now I have another question - I'm a beginner when it comes t
programm VBA, so please bear with me. I don't understand how t
actually have my VB app or macro or whatever you want to call it t
run. Right now, my program will only go through and send out an emai
if I hit alt-F11 and then click run. How do I make this macro run b
itself? Is there a way to make the macro run when closing the file?
How about when opening the file?

Any help is appreciated -
Andre

 

[Tom Ogilvy]

http://www.cpearson.com/excel/events.htm

Chip Pearson's page on Events.

 

[funperro]

Tom -

Thanks for the link.. I actually have read that page many many times.
Forgive me - but this is my first time working with events.. an
actually, the first time using VBA.

So far, the code I have come up with will run the macro when making
change to the worksheet. I would like this to run the macro only upo
closure of the workbook. Any suggestions on what I should change? B
the way, all of this code is placed in "Sheet1".

Thanks for *any* help anyone can and / or has provided!
Andrew


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
Call CDO_Send
Application.EnableEvents = True
End Sub


Sub CDO_Send()

Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Dim WB As Workbook
Dim WBname As String
Dim cell As Range

Application.ScreenUpdating = False

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "163.186.80.4"
.Item(cdoSMTPServerPort) = 25
.Update
End With
For Each cell I
Sheets("Sheet1").Columns("J").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 1).Value <> "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value
"yes" Then
With iMsg
Set .Configuration = iConf
.To = cell.Value
.From = """Inventario de Almacen"" <>"
.Subject = "Comprar Partes"
.TextBody = "Dear " & cell.Offset(0, -1).Value & vbNewLine
vbNewLine & _
"Part number needs to be ordered."
.Send
End With
End If
End If
Next cell

Set iMsg = Nothing
Set iConf = Nothing
Set WB = Nothing
Application.ScreenUpdating = True
End Su

 

[Tom Ogilvy]

the beforeclose event (in the thisworkbook module) fires when the workbook
is closed.