Mail every worksheet using A1 as address

[sknight22]

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

 

[SREEMOORTHYP]

MasterFils as source file
Workbooks.Open Filename:="Copy Paste your Source File Full Path with extension(.xls,.xlsx....) "

 

[Norie]

Hi,

Ok i have done that and the format changes from time to the below when i get to

.Cells.PasteSpecial xlPasteFormats

What happens if you remove that line?

 

[sknight22]

Hello,

I have removed that line and repeated the steps. The correct format remains until i get to "End With" (below) then it goes back to the wrong format

With wb.Sheets(1).UsedRange                .Cells.Copy
                .Cells.PasteSpecial xlPasteValues
            End With

 

[Norie]

Stephen

That doesn't make sense, the End With doesn't really do anything - all it does is close of the With..End With block.

 

[sknight22]

Morning,

Maybe it was the way I worded it.

While I was stepping through, everything looked as it should until I pressed F8 while ".Cells.PasteSpecial xlPasteValues" was highlighted. The highlighter moved to "EndWith" and the format was changed.

Does that mean the change occurs as a result of ".Cells.PasteSpecial xlPasteValues"?

Thanks

Stephen

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/sho...sheet-using-A1-as-address&p=380238#post380238

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sknight22]

Sorry, I honestly didn't realise or think

 

[sknight22]

Figured it out myself so you may as well close this!!