Automatically Email worksheet when saved

[Agbrooker]

Hi all

I hope you can help me, I have managed to make my workbook automatically send an email when the workbook is saved using the code below, however the only problem is that the selection on the email is massize and spreads across 2-3 pages, meaning that it can not be printed, can I add something somewhere that will reduce the size of the selection in the email?

Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Sheets("Requisition").Range("A1:J32").Select '#
ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = "Hello, Spares Requisition Updated! - the workbook was Updated by " & Environ("USERNAME") & " at " & Format(Now(), "ddd dd mmm yy hh:mm")
.Item.To = "xxx@xxx.com;
.Item.Subject = "Spares Request update Number "
.Item.display
'.Item.send
End With


End Sub

 

[macattackpro]

Hi,

I may be way off base here but have you tried setting the print area in the workbook prior to saving it?

 

[Agbrooker]

Hi,

I may be way off base here but have you tried setting the print area in the workbook prior to saving it?

Thanks regrefully I tried this and it made no difference.

Regards

 

[macattackpro]

Hello again,

I tried your code with some test data and when I make the data stay within one page break it emails and looks just fine. When I introduce additional page breaks it appears to behave the way you describe. I recommend looking at the Page Break Preview and Page Layout views in the View ribbon to verify your sheet looks the way you want it emailed.

 

[Agbrooker]

Thanks again, the workbook is set up to be one page but the page preview on the email is just to big. Thanks for your help though.

Regards