Using Excel / VBA to automatically email forms - how?
 

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

--
Message posted from http://www.ExcelForum.com

Using Excel / VBA to automatically email forms - how?
 

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...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

--
Regards,
Tom Ogilvy


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

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

My idea is this: I want to set up a simple excel list of part numbers
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 the
specific part number and add to its total in their excel spreadsheet.
When a part is removed from the warehouse, they would subtract from
that specific part number's total. Seems easy for most, right? You'd
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 below
a certain quantity, this excel worksheet would then send an email to
the people that are responsible for ordering parts. This worksheet
would contain all the part numbers, and each part number would have
information with it such as Supplier info, costs, wait time, etc. This
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 emails
automatically when these minimum part quantities occur?

Any ideas suggestions are *much* appreciated!

Andrew


---
Message posted from http://www.ExcelForum.com/

Using Excel / VBA to automatically email forms - how?
 

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

--
Message posted from http://www.ExcelForum.com

Using Excel / VBA to automatically email forms - how?
 

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.

--
Regards,
Tom Ogilvy

funperro wrote in message
...
Tom -

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

Thanks -
Andrew


---
Message posted from http://www.ExcelForum.com/

Using Excel / VBA to automatically email forms - how?
 

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

--
Message posted from http://www.ExcelForum.com

Using Excel / VBA to automatically email forms - how?
 

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

Chip Pearson's page on Events.

--
Regards,
Tom Ogilvy

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

Any help is appreciated -
Andrew


---
Message posted from http://www.ExcelForum.com/

Using Excel / VBA to automatically email forms - how?
 

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(x lCellTypeConstants)
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

--
Message posted from http://www.ExcelForum.com