Macro to email Several Sheets

[Howardc1001]

I have written a macro to email sheets "Sales1 to sheet "Sales3"


The sheets must be saved as the as the name of the subject in B1 on sheet "Email"


I get a run time error & the following code is highlighted


I have attached sample data to make it easier for someone to see what I am trying to achieve

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mgs73]

Please Login or Register  to view this content.

Results in this: 1/03/202009-Apr-20 13-05-07 which has slashes making it an invalid file name

Maybe this will work:

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the help. It sorted the run time error out

I made a few changes to me macro and need the following to be amending in my macro


1) When attaching to outlook , the email addresess are not displayed in To: (these are in F1:F3 on sheet "Email")
2) The attached file must state the name in B1 on sheet :"Email" + the current date
3) In the Body the names in E1 & E2 on sheet "Email" must appear after Hi



Your assistance is most appreciated

Please Login or Register  to view this content.

[mgs73]

You don't specify which worksheet the range is on, so it is using the active one, and the values in F1:F4 are blank.

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the help, macro working perfectly now

[Howardc1001]

When running the macro on live data , I found that where there were formula's and it had range names, I would get #name error

I want the data to appear as values only so it must be copied to the sheets to be attached as values only

I think that this part of the formula needs to be amended but not sure how to amend


It would be appreciated if you could kindly amend this

Please Login or Register  to view this content.

Kindly make the necessary change so that the sheets created to be attached are values only I.E no formulas

Full code below

Please Login or Register  to view this content.

[Howardc1001]

Hi mgs73


It would be appreciated if you can have a look at my post # 6 and amend the code to suit my requirement

[mgs73]

I don't see anything wrong with your code, and could not reproduce your error on my own workbook. Can you upload a workbook that demonstrates this issue?

[Howardc1001]

My apologies. Tested again & code does work