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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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:

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
69
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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
 

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
69

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Did you try the macro of post #4 ?
 

5h1v

Board Regular
Joined
Oct 11, 2012
Messages
69

ADVERTISEMENT

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

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
Top