Multiple e-mailing

[Fairport]

A report needs to be distributed to area sales managers every month by e-mail.
I have found code (courtesy of Ron de Bruin http://www.rondebruin.nl/mail/folder1/mail4.htm) that will select the data that needs to be sent, and modified it to suit my spreadsheet.

Please Login or Register  to view this content.

1. Is there a way to loop the code and send e-mails to each sales manager?
2. As the sales team operate Office 2003 on their laptops as opposed to our 2007 is there a way to avoid the compatibility check every time we send an e-mail and also is there a way to avoid the “allow-deny” box too?

I have already suggested that they download MS compatability file, but 1 sales guy has 'downed his laptop' so want to try and avoid a similar event with the others.

Thanks in advance

[Greg M]

Hi there,

I've read your post very quickly so this is just a knee-jerk reaction which might or might not be useful.

First point:

Please Login or Register  to view this content.

Doesn't this code deliver the same result regardless of the Excel version in use?

Second point:
Why not always save your file in Excel 2003 format? That way your sales team won't need to use the MS Office Compatability file, but machines running Excel 2007 will be able to open the Excel 2003 files.

Third point:
One way of overcoming the "Enable/Disable Macros" prompt is to digitally sign your workbook and instruct your users to check the "Always trust projects from this source" box when it appears. From what I can tell from your code, this would mean digitally signing your Workbook Template "xlWBATWorksheet".

As I said at the start, this is just an initial reaction, but I hope it might be of some assistance to you.

Please let me know how you get on.

Regards,

Greg M

[Fairport]

Thanks for your reply.
To answer your first point, the original code

Please Login or Register  to view this content.

looked at what version of Office is running, in my case 2007 so it saved the file as ".xlsx", which is not the version the sales team is running with. I changed the code to

Please Login or Register  to view this content.

to force it to save to 2003 regardless. I'm sure there is a another way to code this but with my limited knowledge I experimented and this worked.

Your second point is what I achieved above, but that gave me another problem because when it saves the file it comes up with the compatability check box which is one of my inquiries as how to avoid or disable the box that appears.

On the third point it not disabling/enabling macros that I'm looking at. When (after allowing a save in 2003 point 2 above) it then asks you if you want to send an e-mail, press "allow" to send, "Deny" to cancel and "Help". It is this box I want to avoid or disable.

What I would like to achieve is the operator to click button 1 and all the reports are e-mailed to all the sales managers with no further input from them.

Thanks again for you help.
Fairport

[Greg M]

Hi again,

Thanks for your feedback, and sorry for the total lack of help from this side.

Almost all of the work I do uses Excel 2000, so that's why I'm less familiar with aspects like compatability checks etc.

The more I see of the "improvements" in the latest Office suites, the more I'm convinced that if something isn't broken, it shouldn't be fixed.

Sorry once again,

Greg M

[Fairport]

Thanks Greg M for trying.

[Charlize]

For the looping through the salesmen. Maybe the displayalerts will hide the unwanted messagebox. Not sure cause I can't test it now.

Please Login or Register  to view this content.

Charlize

[Fairport]

Thank you Charlize, that seems to have done the trick after tweeking here and there to fit the spreadsheet it works a treat.