VBA Code to Send Only Today's Entries

[Justinian]

I have a basic spreadsheet to track when vendor visit our property. It is a running list (as seen below) and I want to install a macro that will e-mail only the contents of the rows with today's date to an assigned recipient.

For example, using the data below, I would like the macro to open an Outlook e-mail and send the contents of rows 3 and 4 since they are the only rows with today's date. I know how to run a macro to e-mail the entire workbook or worksheet but not the individual rows of data. How would I do this?

Outside Contractor - Daily Log.xlsm
	A	B	C
1	OC Name	Date	Unit Code
2	Vendor A	06/29/20	95-96
3	Vendor B	06/30/20	105-45
4	Vendor C	06/30/20	105-97
5
6
Sheet1

 

[DanteAmor]

Forget the above, I do not know how to explain to you that you filter the data.

Try this new macro.

Sub Send_Today_Entries()
  Dim sh1 As Worksheet, sh2 As Worksheet, wb2 As Workbook
  Dim sName As String, i As Long, j As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set sh1 = Sheets("Sheet1")
  
  Set wb2 = Workbooks.Add
  Set sh2 = wb2.Sheets(1)
  sh1.Rows(1).Copy sh2.Range("A1")
  j = 2
  For i = 2 To sh1.Range("B" & Rows.Count).End(3).Row
    If sh1.Range("B" & i).Value = Date Then
      sh1.Rows(i).Copy sh2.Rows(j)
      j = j + 1
    End If
  Next
  
  With ThisWorkbook
    sName = .Path & "\" & Left(.Name, InStrRev(.Name, ".") - 1) & ".xlsx"
    wb2.SaveAs sName
    wb2.Close False
  End With
      
  With CreateObject("outlook.application").createitem(0)
    .To = "email@gmail.com"
    .Subject = "Subject"
    .body = "Body"
    .Attachments.Add sName
    .display 'use .Send to send
  End With
  
  Application.ScreenUpdating = True
End Sub

 

[Justinian]

I understand what you want. Here is the code:

Sub Macro1()

' Macro1 Macro
Selection.AutoFilter
Range("C3").Select
Selection.AutoFilter
Selection.AutoFilter
Range("B5").Select
ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=2, Criteria1:=1, Operator _
:=11, Criteria2:=0, SubField:=0
End Sub

 

[Justinian]

The new code you just wrote attaches the workbook to the e-mail but it does not put the rows with today's date into the e-mail body, which is what I am trying to do.

 

[DanteAmor]

but it does not put the rows with today's date into the e-mail body

You didn't mention that you wanted the data in the body of the email.

To put a range of cells in the body of the email you need a different macro. check the following link.

History | Ron de Bruin

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[Justinian]

I mentioned it in post #1.

 

[Justinian]

The mail range macro works but it does not automatically highlight today's dates. I can record a macro to filter out only today's date but I do not know where to put it inside the Ron Bruin macro.

 

[DanteAmor]

I mentioned it in post #1.

Could you comment on where you mention you want the rows in the body of the email?

I have a basic spreadsheet to track when vendor visit our property. It is a running list (as seen below) and I want to install a macro that will e-mail only the contents of the rows with today's date to an assigned recipient.

For example, using the data below, I would like the macro to open an Outlook e-mail and send the contents of rows 3 and 4 since they are the only rows with today's date. I know how to run a macro to e-mail the entire workbook or worksheet but not the individual rows of data. How would I do this?

 

[Justinian]

I have a basic spreadsheet to track when vendor visit our property. It is a running list (as seen below) and I want to install a macro that will e-mail only the contents of the rows with today's date to an assigned recipient.

For example, using the data below, I would like the macro to open an Outlook e-mail and send the contents of rows 3 and 4 since they are the only rows with today's date. I know how to run a macro to e-mail the entire workbook or worksheet but not the individual rows of data. How would I do this?

 

[DanteAmor]

I would like the macro to open an Outlook e-mail and send the contents of rows 3 and 4 since they are the only rows with today's date.

Sorry, but I do not see that there expresses that you want the content of the cells within the body of the email.
But maybe I am wrong.

 

[Justinian]

It says it right here:

send the contents of rows 3 and 4