The following macro i borrowed from somewhere else. I modified it to fit
my needs but I need to add a feature to it. Namely, this macro filters
an Excel file and sends filtered part to a person in a file by using my
email. I would like this macro to add a generic message to the email
with the attached file. How can I add an argument that would
insert/paste a text in the body of email message.
Please post your suggestions or email me at

Thanks.


Sub Distribute()
'Public Sub SendItAll()
' Clear out any old data on Report
Sheets("Contract Expiration Report").Select
Range("A1").CurrentRegion.ClearContents
' Sort data by region
Sheets("Data").Select
Range("A1").CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Header:=xlYes
' Process each record on Distribution
Sheets("CST Distribution List").Select
FinalRow = Range("A15000").End(xlUp).Row
For i = 2 To FinalRow
Sheets("CST Distribution List").Select
RegionToGet = Range("A" & i).Value
Recipient = Range("B" & i).Value
' Clear out any old data on Report
Sheets("Contract Expiration Report").Select
Range("A1").CurrentRegion.ClearContents
' Get records from Data
Sheets("Data").Select
Range("A1").CurrentRegion.Select
' Turn on AutoFilter, if it is not on
If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
' Filter the data to just this region
Selection.AutoFilter Field:=1, Criteria1:=RegionToGet
' Select only the visible cells and copy to Report
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Destination:=Sheets("Contract Expiration
Report").Range("A1")
' Turn off the Autofilter
Selection.AutoFilter
' Copy the Report sheet to a new book and e-mail
Sheets("Contract Expiration Report").Copy
Application.Dialogs(xlDialogSendMail).Show _
*arg1:=Recipient, _
arg2:="Contract pricing expiration report for " &
RegionToGet*
ActiveWorkbook.Close SaveChanges:=False
Next i
End Sub


--
stric
------------------------------------------------------------------------
stric's Profile: http://www.excelforum.com/member.php...o&userid=25230
View this thread: http://www.excelforum.com/showthread...hreadid=387195

The help for xlDialogSendMail seems to suggest that there is no body
argument, just recipient, subject and return_receipt.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"stric" wrote in
message ...

The following macro i borrowed from somewhere else. I modified it to fit
my needs but I need to add a feature to it. Namely, this macro filters
an Excel file and sends filtered part to a person in a file by using my
email. I would like this macro to add a generic message to the email
with the attached file. How can I add an argument that would
insert/paste a text in the body of email message.
Please post your suggestions or email me at

Thanks.


Sub Distribute()
'Public Sub SendItAll()
' Clear out any old data on Report
Sheets("Contract Expiration Report").Select
Range("A1").CurrentRegion.ClearContents
' Sort data by region
Sheets("Data").Select
Range("A1").CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Header:=xlYes
' Process each record on Distribution
Sheets("CST Distribution List").Select
FinalRow = Range("A15000").End(xlUp).Row
For i = 2 To FinalRow
Sheets("CST Distribution List").Select
RegionToGet = Range("A" & i).Value
Recipient = Range("B" & i).Value
' Clear out any old data on Report
Sheets("Contract Expiration Report").Select
Range("A1").CurrentRegion.ClearContents
' Get records from Data
Sheets("Data").Select
Range("A1").CurrentRegion.Select
' Turn on AutoFilter, if it is not on
If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
' Filter the data to just this region
Selection.AutoFilter Field:=1, Criteria1:=RegionToGet
' Select only the visible cells and copy to Report
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Destination:=Sheets("Contract Expiration
Report").Range("A1")
' Turn off the Autofilter
Selection.AutoFilter
' Copy the Report sheet to a new book and e-mail
Sheets("Contract Expiration Report").Copy
Application.Dialogs(xlDialogSendMail).Show _
*arg1:=Recipient, _
arg2:="Contract pricing expiration report for " &
RegionToGet*
ActiveWorkbook.Close SaveChanges:=False
Next i
End Sub


--
stric
------------------------------------------------------------------------
stric's Profile:
http://www.excelforum.com/member.php...o&userid=25230
View this thread: http://www.excelforum.com/showthread...hreadid=387195