Excel to send worksheets as PDF based on info in 1st worksheet

[lindafinlay]

Hi

I have an excel file which has several sheets within it. The first sheet is the contact sheet. The last sheet is my data sheet and all the sheets in between are the reports that I need to email every month. The common value in each of these sheets is the store# (1001 - 1005 in the attached excel example). You will see this code used in sheet one along with email addresses.

The store number is hidden in cells A6 on all the report sheets as the number was needed to do a Vlookup using the data sheet.

Now, what I would like is perhaps a button that I can press which will automatically email each report sheet to the corresponding email in the Contacts sheet. As some do not have an email, only a fax number, I also need the macro to skip over that document as I will need to print it and fax it manually.

Is this possible?

NB: the attached is an small example as there is confidential info in there. I will have about 50 report sheets in the real thing.

Thanks!

Linda

Statement of Monthly Turnover EXAMPLE.xlsx

[jaslake]

Hi Linda

Welcome to the Forum!

This Code is in the attached and appears to do as you require...let me know of issues.

Please Login or Register  to view this content.

[lindafinlay]

Oh it comes up with error: "Invalid Inside Procedure" and then highlights the first line of your code (Option Explicit)

[lindafinlay]

Sorry, my first reply didn't go through properly! Thanks for you help John but am getting the above error after I have created a button and called it Email_Form.

[lindafinlay]

Sorry - I should mention excel automatically adds the line "Sub Email_Form()" above your first line of code

[jaslake]

Hi Linda

This error would indicate you have 2 (or more) Option Explicit statements...get rid of all except the uppermost

it comes up with error: "Invalid Inside Procedure"

Make certain the remaining statement is the first line of Code.

Regarding this

excel automatically adds the line "Sub Email_Form()"

When you create the Button, create a Forms Control Button and assign the Macro test to that Button.

[lindafinlay]

Thanks John. I'm not quite understanding though. As far as I can see there is only one Option Explicit which is the very first line in your code. To create the macro button I thought I needed to keep the automatic line "Sub Email_Form()" for it to work. If I take it out, the button won't link to the code. But I thought you can't use an Option Explicit in a Sub code?

Here is the complete code (as you can see I haven't altered anything from your original except for the automatic Sub line):

Please Login or Register  to view this content.

[jaslake]

Hi Linda

Post the offending Workbook...I'll demonstrate what you need to do...this line does not belong in the Code.

Please Login or Register  to view this content.

[lindafinlay]

Statement of Monthly Turnover EXAMPLE.xlsmThanks John. Sorry - I'm new at this!!

[jaslake]

Hi Linda

I'm not sure what you did to get this line of Code into the Code

Please Login or Register  to view this content.

As you can see I've simply eliminated (commented out) that line of Code then, with Email Form Button I've assigned Macro "test" to that Button. To assign Code to the Email Form Button, right click on the Button ---> Assign Macro ----> Select "test" ----> OK.

Works for me...does it work for you?

[lindafinlay]

Ah! Great - yes that works. Thank you!
In addition to that, can I get the email to just send without the email being created and then me needing to hit the send button? And is it possible to also get it to automatically insert my default email signature from Outlook?

[jaslake]

Hi Linda...yes...these are both possible. To Send the Email change this line of Code

Please Login or Register  to view this content.

Regarding the Signature...that'll have to wait until tomorrow...have Grandson Duty in the AM and am off to bed.

[lindafinlay]

Ah! Sorry - I didn't see your line note there! Thanks for your help.

[lindafinlay]

Alos, I just noticed that now I changed the statement to "True" to auto send the email, it seems to only send the first sheet, and then it stops once it has sent that one. Do I need to change something else to keep it going? When the statement was False, it seemed to create all the emails.

Cheers

[lindafinlay]

Actually, disregard that last statement - I fixed it

[jaslake]

Hi Linda

To insert your Signature into the Emails replace your ENTIRE Code with this Code. You'll need to modify this line to the name of your Signature

Please Login or Register  to view this content.

This change may or may not work depending on your operating system and what version of Outlook your using. I'm running XP with Outlook 2007 and this works for me.

You don't need to do anything else (the Button Code remains as is).

Let me know of issues.

Please Login or Register  to view this content.

[lindafinlay]

Great! One last question - very basic - when typing the message body, how do I get a paragraph break?

Thanks for all your help!

[jaslake]

Hi Linda

Use Line Feeds (vbCrLf) like so (a single Line Feed for New Sentence...a Double Line Feed for a New Paragraph)

Please Login or Register  to view this content.

[lindafinlay]

Thank you! Perfect - everything works exactly how I wanted

[jaslake]

You're welcome...glad I could help.