I'm currently working on a spreadsheet for work,nothing too taxing - covers monthly labour & oil sales profit & loss. 32 worksheets within the workbook with allsorts of formula's flying backwards and forwards. I've only just started getting to grips with VB - got some command buttons to send you back to top of page and to month end reports etc.

My problem ? -- I fill this spreadsheet in every day and then have to email it to 6 people within the group, I'd love to be able to hit a button in excel and have it email the sheet to the 6 people - - can this be done ????

Any pointers gratefully received !!

It can indeed - I set it up in one of our jobs. I'll go and find the code and post it here for you...

Try this....

' Create email

Set App = CreateObject("Outlook.Application")

Set Itm = App.CreateItem(0)

With Itm

.Subject = "Whatever you want to call it"

.To = "[email protected]"

'.CC = "name"

.Body = "This is an automated process. Please email the sender if you wish to be removed from the distribution list."

.Attachments.Add ("C:\Temp\DocumentName.xls")

.send

End With

Set App = Nothing

Set Itm = Nothing

' Final Message

mess = MsgBox("Your email has been sent", vbInformation, "Email Sent")

Obviously, you need to add your recipients and choice of attachment file (if required).

Hope this helps.

why hit the button at all, do you really have to fill in the spreadsheet. could you not write macro's to do it for you and schedule it all to run at a certain time in the night/day. You could even texts the guys when its done, how cool would that be! (cost 10p per text)

Let me know if you would like some help with any of the above.

why hit the button at all' date=' do you really have to fill in the spreadsheet. could you not write macro's to do it for you and schedule it all to run at a certain time in the night/day. You could even texts the guys when its done, how cool would that be! (cost 10p per text)

Let me know if you would like some help with any of the above.[/quote']

Don't mean to hijack the thread, but out of interest how would you use a macro to schedule the process to run automatically?

Admittedly I'm only starting to get the hang of VB in Excel myself but I thought there would have to be some sort of manual intervention to invoke the process?

Also you may want to code the email addresses into a sheet rather than hard code them. It makes it easier to maintain if someone were to leave the company. I tend to make it as customisable as possible and just hide the sheet that contains all the "setting" as it were.

It will take a lot more code however

Also with the above email code, don't be alarmed if you have office 2003 and outlook says something is hijacking the application. you cant get rid of that message

Also with the above email code' date=' don't be alarmed if you have office 2003 and outlook says something is hijacking the application. you cant get rid of that message[/quote']

Ah, Office 2003 - we can all dream we're still using office 97 on windows nt

Despite promises of migration to XP, majority of our apps aren't compatible :thumbdwn:

Thanks for these replies guys - will try it out.

Can't automate the process as the sheet is a quality check and recording process - I'm basically checking jobcards and invoices that have been filled out to make sure that we recover the correct amount of labour and oils - so its a human type in job

Have tried the above code and I receive a run-time error "429" ActiveX component can't create object ?

I have outlook express installed on my PC and it runs OK if I select manually - am I missing something here ??

have you installed/selected the outlook object model in vb?

Don't mean to hijack the thread' date=' but out of interest how would you use a macro to schedule the process to run automatically?

Admittedly I'm only starting to get the hang of VB in Excel myself but I thought there would have to be some sort of manual intervention to invoke the process?[/quote']

use microsoft scheduler, youll need to set your password and what have you, under run you would have something like

C:\PROGRA~1\MICROS~1\Office\EXCEL.EXE "F:\dev\offline\COBI2005.xls" /x mcrAutoRun

the part up to excel.exe would open excel, then your filename, /x

specifies macro, then the macro name mcrautorun.

Schedule that to kick off at a set time and your done

I do similar stuff but take it a bit further, access could manage excel for you.

We get provided with a zip file at a point in the night, i schedule access to run a macro every 30 mins checking for the file, once its there i pass a shell command to unzip to a location and then run the db that inputs the data, modifies it, outputs excel files, calls macro's inside excel templates and output or uploads the daily reports sending texts out at various points in the process. If you want a hand with anything let me know.

have you installed/selected the outlook object model in vb?

That would be a "NO" - I have Outlook Express installed on my PC - but other than that just running with whats in Excel - I thought there might be something else that was needed.

I tried using the Application.ActivateMicrosoftApp code but it doesnt recognise Outlook either -- how do I go about installing the Outlook model ??

I really appreciate any help on this, the work I've done so far with the spreadsheet has filled a gap in the reports that our own system can produce - so its earning me mucho credito with the boss

its easy, enter the vb window in excel, i think its alt+f11

in the menu under tools go to referances, select the outlook library and click ok

Have tried the above code and I receive a run-time error "429" ActiveX component can't create object ?

I have outlook express installed on my PC and it runs OK if I select manually - am I missing something here ??

Only thing I can think of - and maybe you've tried it already - is editing the line

Set App = CreateObject("Outlook.Application")

to look for Outlook express rather than Outlook? Unsure of exactly how but will hazard a guess...

Set App = CreateObject("OutlookExpress.Application")?

I'll go and have a play with it and let you know if I have any success but as we don't use Outlook Express here it might not be possible for me to call an application that isn't installed!

The only other thing I can think of as a workaround - if you are wanting to avoid manually creating the e-mail - would be to either use a template in Outlook express (best if there is no attachment, smiply telling them "file updated") OR, like I did in a previous job, create a macro in Access which simply creates the mail using your default mail client (we ran with Lotus Notes at the time) and attaches a report or table from the database. Again, not sure how it would go but maybe that could be tweaked to suit.

use microsoft scheduler' date=' youll need to set your password and what have you, under run you would have something like

C:\PROGRA~1\MICROS~1\Office\EXCEL.EXE "F:\dev\offline\COBI2005.xls" /x mcrAutoRun

the part up to excel.exe would open excel, then your filename, /x

specifies macro, then the macro name mcrautorun.

Schedule that to kick off at a set time and your done

I do similar stuff but take it a bit further, access could manage excel for you.

We get provided with a zip file at a point in the night, i schedule access to run a macro every 30 mins checking for the file, once its there i pass a shell command to unzip to a location and then run the db that inputs the data, modifies it, outputs excel files, calls macro's inside excel templates and output or uploads the daily reports sending texts out at various points in the process. If you want a hand with anything let me know. [/quote']

Aha, suddenly it all becomes clear!

I can think of several places I could use that sort of principle, and would certainly mean that half the stuff i do first thing could be waiting for me when I get in in the morning!

Thanks for sharing that!!

Just one other thing - tell me to n@ff off if you want! - how do you check for the zip file having been created/delivered? Is that also done using Excel and scheduler?

Ok, I think I've nailed this and in a way that's easier than what I first suggested!!

The following code will send a blank e-mail with attachment only to a single recipient or distribution list (that is, subject line populated but no text in the 'body' of the e-mail, only a file attached):

Sub Mail()

' Send current Excel document via e-mail

ActiveWorkbook.SendMail "[email protected]", "Amend this to whatever your subject line should read"

' Final Message - this can be excluded if you wish

mess = MsgBox("Your email has been sent", vbInformation, "Email Sent")

End Sub

because in this code you're not actually specifying the mail prgram (unlike the previous example) it should pick up OE as your default mail client. When I tested it (albeit using Outlook and not OE) it would only let me add either a single recipient (i.e. [email protected]) or to a distribution list set-up within my address book (i.e job191_report_recipients).

Try it out and let me know how it goes.

Suppose I'd better go and do some proper work now!!