Mail every worksheet with address in A1

sknight · #1 05-15-2018, 01:00 AM

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 · #2 05-23-2018, 08:51 AM

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

Code:

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

Andy79 · #3 06-07-2018, 10:23 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I mail merge just part of one worksheet	tomseeley	Excel	0	01-25-2014 12:10 PM
How to forward all e-mails from one address to five (5) other e-mail address?	adi2012	Outlook	1	09-09-2012 06:41 PM
Word mail merge from a protected Excel worksheet	Platform	Mail Merge	10	09-28-2011 05:12 AM
cannot receive my own e mail and from same address	vb55	Outlook	0	03-13-2009 11:38 AM
mail receipent with an excel worksheet attachment	jburke@holidayinnislip.co	Office	1	11-14-2005 08:55 AM