VBA & E-MAIL

[planetpj]

One more question one of the problems that I have come across the most is that I get e-mailed tons of DATA sent by my higher ups. The problem is that instead of using several workbooks for the data they use 1 (All the Time)!! In the workbook they have all of the schools in the district on seperate worksheets. My job is to change the data on the worksheets and then send the file back EXCEPT they don't want the individual school that I send the file to see the whole file. They just want them to see the individual worksheet for their school!!! A couple of methods that I use is to hide all of the other worksheets in the workbook or I cut and paste!! Does any one have any VBA Method that would take care of this problem!!!!!!

 

[johnsjem]

How many different schools are within the school district? If there aren't to many you could right seperate VBA code for each school and run the macro on the workbook before you send out the spreedsheet to the individuals. The below code shows all sheets being hidden besides sheet3.

Sheets(Array("Sheet1", "Sheet2", "Sheet4")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.Visible = False

 

[keithkemble]

Try this routine.

This routine hopefully is self explanatery and has options by chnaging the lines

This routine can be run from any sheet and you can either load up a blank template or a blank non formatted sheet.


Sub sendsheet1()
'Declare String
Dim sAddress As String
'Initialise the string with the address entered into box
sAddress = InputBox("Please enter the name of the recipient" & vbCr & "Recipient must be on the Reliance Email" & vbCr & "CONVENTION = firstname . surname@emailaddress", "Send To")

'If the user doesn't enter an address then exit the sub.
If sAddress = "" Then Exit Sub
' select the sheet within the spreadsheet
Sheets("site 1").Select
' selects the named range within sheet
Application.Goto Reference:="export1"
Selection.Copy
' uses either a template file, important if you have lots of formatting or adds work book if simple
Workbooks.Open FileName:="d:RSSL cost prosite1.xls"
' Workbooks.Add
' Selection.PasteSpecial Paste:=xlPasteAll ' pastes all formulas etc if reuired or use :-
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste values only

Application.CutCopyMode = False
ActiveWorkbook.SendMail Recipients:=sAddress & "@emailaddress.co.uk", Subject:="site 1 cost pro", ReturnReceipt:=False
MsgBox "Your email has been sent to" & vbCr & sAddress, , "MESSAGE"
ActiveWindow.Close ' close the template or added workbook and ask if you want to save
' reselects the sheet with the macro
Sheets("printing").Select
Range("G22").Select
End Sub

 

[planetpj]

Thanks guys for the insite! Keith I'm going to try the code now!! Sorry I replied back so late my server was down yesterday!!! I appreciate it as always!!God Bless!!!