Mail every worksheet with address in A1

[sknight]

Hello


I am using code from the below link to send multiple sheets to email addresses in cell a1.

http://www.rondebruin.nl/win/s1/outlook/amail5.htm

I have hit a snag though. My sheets are made up of filtered pivots and I don’t want the recipient to be able to take the filter off.

Could the code be amended to first copy and paste special the whole sheet then send?

Thanks

Stephen

[Roger Govier]

Hi

In Ron's code, immediately after
sh.copy
and before
Set wb = ActiveWorkbook

insert the following 3 lines of code and that should achieve what you want

    ActiveSheet.UsedRange.Copy
    ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteFormats

[Andy79]

Hi Roger

Thanks and its a great bit of code but it only works on the first sheet. Is there a way to replicate/loop it over all the sheets.

I'm so very nearly there.

Thanks
Andy