Filter cells and emial filter result

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
69
Hey,

I am hoping you could help me, I want to filter a worksheet on the name of a supplier(column C) and on todays date (Column T) and then email them columns C:T from that filtered information could anyone help?

Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Do you want to send mail by outlook?
Do you want to generate a file with the filtered information and send the file?
What name will the file have?
What mail?
What is the subject?
What is the body of the mail?
 
Last edited:
Upvote 0
Thanks for he reply.

Yes send by Outlook
Yes please
The name of the email and the file would be todays date and the supplier name
The body of the email, would just be "Hi XXX, Please see attached. Regards XXX"

I hope that helps.
 
Upvote 0
It is not clear.
To which email?

In which row are your headings?
In which row do your data start?
Do you want the headings in the file?



You could put an example of how your data is on the sheet.
That is, on the sheet you have several suppliers and you want a file for each supplier?

I attached an advance so you can start testing.

Code:
Sub create_multiple_emails()
  Dim wb As Workbook, sh As Worksheet, c As Range, m As Range
  Dim sBody As String, wFile As String
  Dim dam As Object, dict As Object
  '
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  '
  Set sh = ActiveSheet
  Set dict = CreateObject("scripting.dictionary")
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  sh.Range("A1").AutoFilter Field:=20, Criteria1:=xlFilterToday, Operator:=xlFilterDynamic
  For Each c In sh.Range("C2", sh.Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    If Not dict.exists(c.Value) Then
      dict(c.Value) = dict(c.Value)
      sh.Range("A1").AutoFilter 3, c
      Set wb = Workbooks.Add
      sh.AutoFilter.Range.EntireRow.Copy Range("A1")
      wFile = ThisWorkbook.Path & "\" & Format(Date, "dd-mm-yyyy") & " " & c.Value & ".xlsx"
      wb.SaveAs wFile
      wb.Close False
      Set dam = CreateObject("Outlook.Application").CreateItem(0)
      '
      '[COLOR=#ff0000]Mail Information[/COLOR]
      dam.To = "[COLOR=#ff0000]email@mail.com[/COLOR]"
      dam.Subject = [COLOR=#ff0000]"Subject ?????[/COLOR]"
      dam.Body = "[COLOR=#ff0000]Hi XXX, Please see attached. Regards XXX[/COLOR]"
      dam.Attachments.Add wFile
      dam.Display 'use .Send to send
    End If
  Next
  sh.ShowAllData
  MsgBox "Emails sent"
End Sub
 
Upvote 0
Thanks, sorry if I was not clear.

I have uploaded a demo of what the file looks like here;

http://s000.tinyupload.com/index.php?file_id=02581266865533953545


To which email? - This will change based on supplier

In which row are your headings? 1
In which row do your data start? 2
Do you want the headings in the file? Yes please

I would like it to not send if there is no data for that day as well please.
 
Upvote 0
Do you have data with today's date?
Can you put a sample of your data?

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top