Macro to email filtered section as attachment

[Tamara]

Hi, I would appreciate some assistance with a spreadsheet I need to email to
specific users.
I have a master sheet which details in the first column the State, 2nd
column the managers name, following columns store details and other
information. What I require is a macro to email each manager with their
filtered information. I've successfully used Ron de Bruines "Mail a row or
rows to each person in a range" to do this function (email addy's in a
separate page) but it inbeds the spreadsheet in the email and I require it to
be an attachment. I found a macro to save the managers info in separate
files but was unable to merge this function with an email function within the
same macro. Any help would be much appreciated!!!

 

[Daniel.C]

Hi,
Try the following macro :

Sub test()
Dim c As Range, myRange As Range
Set myRange = Range("_filterdatabase").Offset(1)
Set myRange = myRange.Resize(myRange.Rows.Count - 1, 1).Offset(, 1)
Set myRange = myRange.SpecialCells(xlCellTypeVisible)
Workbooks.Add 1
Set wb = ActiveWorkbook
With ThisWorkbook.Sheets("Sheet1")
For Each c In myRange
Range(.Cells(c.Row, 1), .Cells(c.Row, 256).End(xlToLeft)).Copy
wb.ActiveSheet.Paste
wb.SendMail c.Value & "@company.com", "Subject"
Next c
End With
wb.Close False
End Sub

The file I tested with is at the following address :

http://www.filedropper.com/tamara

Regards.
Daniel

 

[Ron de Bruin]

Hi Tamara

I will create a example for you this evening

I will post here when it is online

 

[Ron de Bruin]

Hi Tamara

Test the code on this new page for me
http://www.rondebruin.nl/mail/folder2/row2.htm

 

[Tamara]

Thanks Ron de Bruin - worked like a dream!!
Just another further question, I set up an auto filter on the spreadsheet
and after running the macro this filter disappears.
Is it possible for the filter to remain and for only the name I've filtered
against to be emailed opposed to all names on the sheet?

 

[Ron de Bruin]

I am not sure I understand what you want

It is better to close Autofilter before we start the macro(I do not do that in this example)
After the macro is ready it close Autofilter with this line

Ash.AutoFilterMode = False
If you remove that line you see the last filter

I not think you want that
Let me know and I try to help you

 

[Ron de Bruin]

I add also a example for OE and windows Mail users
http://www.rondebruin.nl/sendmail.htm