Filter cells and emial filter result

[5h1v]

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

 

[DanteAmor]

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?

 

[5h1v]

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.

 

[DanteAmor]

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.

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

 

[5h1v]

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.

 

[DanteAmor]

Did you try the macro of post #4 ?

 

[5h1v]

I have and the email is generated but the attached file is blank

 

[DanteAmor]

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.

 

[5h1v]

I have put some fake values in and uploaded it here;
http://s000.tinyupload.com/index.php?file_id=42230483278500529027

Thanks again for the help

 

[DanteAmor]

File ID is not correct. File doesn't exist.

----------------------------
Try whit my file test:

https://www.dropbox.com/s/71ss1lxn2ziyddk/email.xlsm?dl=0