VBA and Excel

[Netraider]

An organisation I give some general IT advice to has asked me a quite a complex question (complex to me)...

They have a spreadsheet that lists equipment they issue to clients. Each item of equipment has its own line in the spreadsheet. What they would like to happen is the following.

If a particular cell is changed can an email be generated that is auto populated with information from the same line?

E.G. A piece of equipment has its status changed from "Available" to "Issued" VBA jumps in and presents an email addressed to stores with the subject containing the serial number and name of the item (Taken from cells in the document) and the body of the asking for address info.

I'm not asking for the solution to be posted, just yes/no its possible,and possibly a link to somewhere that can give more info.

I know there are better solutions (SharePoint etc) but excel is the only option available at the moment...

[eek]

Yes assuming outlook is sat on the machine and you don't mind the email coming from the person updating the spreadsheet.

[Netraider]

Yes assuming outlook is sat on the machine and you don't mind the email coming from the person updating the spreadsheet.

That is what I was hoping would happen... Is this something quite simple, or am I looking at squeezing big bucks out of this organisation?

[doodab]

It can certainly be done. You'll need to catch an event that will be generated when the cell in question is changed, munge the cells you need into some strings and invoke outlook to send the mail, which should be easy enough, I've used excel to drive other applications and called it "test automation" before.

Running VBA Code When Events Occur in Excel 2010 will get you started with events.

[eek]

That is what I was hoping would happen... Is this something quite simple, or am I looking at squeezing big bucks out of this organisation?

A google search or two will give you most of the core code you need.

You still need to modify it, test it, deploy it and then test it again in situ. so its probably 3 days work especially as everything in office is version dependant.

[wim121]

An organisation I give some general IT advice to has asked me a quite a complex question (complex to me)...

They have a spreadsheet that lists equipment they issue to clients. Each item of equipment has its own line in the spreadsheet. What they would like to happen is the following.

Why do people insist on using the wrong tools for the job?


Spreadsheets are great and certainly versatile. However in this situation, an access database would be far better. In there you can do everything you want, lay out forms for each record, make a switchboard so editing and adding equipment is very easy, etc etc.

Access is a far better tool for this job and part of the MS office suite.

[ChrisPackit]

Why do people insist on using the wrong tools for the job?

Well said! Drives me mad...

[ChrisPackit]

Yes assuming outlook is sat on the machine and you don't mind the email coming from the person updating the spreadsheet.

You can also use CDO instead of the MAPI object model & Outlook client. It's much more flexible than Extended MAPI and you can set the name of the Sender which doesn't have to be the person who's actually logged on. Can be a pain to set up though getting the schemas right.

[BoredBloke]

Why do people insist on using the wrong tools for the job?


Spreadsheets are great and certainly versatile. However in this situation, an access database would be far better. In there you can do everything you want, lay out forms for each record, make a switchboard so editing and adding equipment is very easy, etc etc.

Access is a far better tool for this job and part of the MS office suite.

Ofthe the reason is down to Access being included in Office Pro rather than Office Std and so does not tend to get deployed as often. Also there tends to be fewer people kicking about who can make a database than can throw together a spreadsheet. I've worked at loads of places where they have struggled to implement database functionality into a spreadsheet but simply will not accept a database! On the plus side it gives ample opportunity to keep on generating invoices! Grin and bank it!

it is possible to do and not difficult. I also would side with the Access solution as things can become a mess in Excel very easily!

[eek]

There is a large section of GE which prior to be bought ran on a set of interlinked Lotus 123 spreadsheets.

I remember my colleagues having serious fun trying to audit the location of assets.