VBA Code to Send Only Today's Entries

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1OC NameDateUnit Code
2Vendor A06/29/2095-96
3Vendor B06/30/20105-45
4Vendor C06/30/20105-97
5
6
Sheet1
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this

VBA Code:
Sub Send_Today_Entries()
  Dim sh As Worksheet, wb2 As Workbook
  Dim sName As String, lr As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set sh = Sheets("Sheet1")
  
  sh.Range("A1:C" & Range("B" & Rows.Count).End(3).Row).AutoFilter 2, Date
  If sh.Range("B" & Rows.Count).End(3).Row > 1 Then
    Set wb2 = Workbooks.Add
    sh.AutoFilter.Range.Copy Range("A1")
    
    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
    
  End If
  sh.Range("A1").AutoFilter
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Only dates are in column B; column C has unit codes (like 95-96 or 106-48).
 
Upvote 0
You can record a macro, select your data, filter column B, date filters, select Today.
Stop the macro, check the generated code, copy and paste it here.
 
Upvote 0
I already have the data in column B sorted, I just need code to select only today's date and paste that data into an e-mail.

Sub Macro1()

' Macro1 Macro
Range("A2:C6").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:C6")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A3:C4").Select
End Sub
 
Upvote 0
I am sorry for my bad english.
But you must filter by date, what you did was sort the data.
You could create the macro again.
 
Upvote 0
The dates are already sorted; I do not need to sort the dates. Also, the macro I recorded is selecting a static range, not a dynamic one.
 
Upvote 0
I don't want you to sort the dates, I want you to use the autofilter, just to do a test.

select your data

1593572909306.png


Date Filters, select Today

1593572855346.png




Stop the macro, check the generated code, copy and paste it here.
 
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