Macro Help

[TheBAnalyst]

I have written a macro that filters an excel sheet and emails the results to an email recipient - it uses "Send sheet" rather then send as an attachment. The problem I'm having is that when the email recipient replies to me, the sheet appears unfiltered and is causing confusion (I am sending individual emails to people requesting responses no specific lines only - when they click reply, the sheet unfilters and causes too many issues/questions)

The code is as follows:

Filter Macro
'
Application.DisplayAlerts = False
'
ActiveSheet.Range("$A$1:$Z$425").AutoFilter Field:=9, Criteria1:="<>"
ActiveSheet.Range("$A$1:$Z$425").AutoFilter Field:=7, Criteria1:= _
"Mail Recipient"


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = "XXXX - The attached are activities assigned to you. Please let me know if there are actions to close/update by midday today?."
.Item.To = "mail.recipient@work.co.uk"
.Item.Subject = " Actions"
.Item.Send
End With


can anyone help with an extra line or 5 of code that will prevent the user from unfiltering (or more importantly, stop it unfiltering automatically when they hit "reply" in outlook)

 

[TheBAnalyst]

Anyone?

 

[JackDanIce]

What if you copied the filtered data into a new sheet, then copy this temp filtered data (without the filters themselves) and then emailed that new sheet?

 

[TheBAnalyst]

What if you copied the filtered data into a new sheet, then copy this temp filtered data (without the filters themselves) and then emailed that new sheet?

I have around 20 emails to send with (different) filtered data, really trying to get away from the manual effort (filter, copy, paste into an email) as it takes up alot of time

 

[mse330]

How about if you copy the data temporary to a new workbook ? Try the below code

Sub Filter_Macro()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = ActiveWorkbook
With ActiveSheet.Range("A1").CurrentRegion
    .AutoFilter Field:=9, Criteria1:="<>"
    .AutoFilter Field:=7, Criteria1:="Mail Recipient"
    .SpecialCells(xlCellTypeVisible).Copy
End With
With Workbooks.Add.Sheets(1)
     ActiveWorkbook.EnvelopeVisible = True
     Set Wb2 = ActiveWorkbook
    .Range("A1").PasteSpecial xlPasteAll
    .MailEnvelope.Introduction = "XXXX - The attached are activities assigned to you. Please let me know if there are actions to close/update by midday today?."
    .MailEnvelope.Item.To = "mail.recipient@work.co.uk"
    .MailEnvelope.Item.Subject = " Actions"
    .MailEnvelope.Item.Send
End With
Wb2.Close False
Wb1.ActiveSheet.AutoFilterMode = False
End Sub

 

[JackDanIce]

I think you missed what I meant:
You can write code that creates a temporary sheet
Then you can use code to copy filtered data to this temp sheet and use your existing code to email that temp sheet out
Use code to clear the sheet
Apply new filter and copy new filtered results to that temp sheet.

Since you have lots of emails to send, wrap all of above up in a loop and iterate over the list of emails you need to send to