auto email condition of cell date

F

flynpat46

New Member
Joined
Sep 24, 2018
Messages
11
Hello,
I have code for sending an email but I want to add a condition to it. What am I missing? If the date in A2 = todays date, send the email.

Sub auto_mail()
Dim OutApp As Object
Dim OutMail As Object


Dim r As Range
Dim cell As Range

Set r = Range("A2")

For Each cell In r

If cell.Value = Date Then



For i = 1 To 1
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = "email.com"


.Subject = "test"
.Body = "Attached from me"
.Attachments.Add ActiveWorkbook.FullName

.Send
End With
Next i
On Error GoTo 0
End If
Set OutMail = Nothing
Set OutApp = Nothing


End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
.
Code: 
Option Explicit


Sub auto_mail()
Dim OutApp As Object
Dim OutMail As Object
Dim r As Range


Set r = Range("A2")


If r.Value = Date Then
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    
    With OutMail
    .To = "email.com"
    .Subject = "test"
    .Body = "Attached from me"
    .Attachments.Add (Application.ActiveWorkbook.FullName)
    .Display
    '.Send
    End With
    
    On Error GoTo 0
    
End If


Set OutMail = Nothing
Set OutApp = Nothing




End Sub
 
Upvote 0
I need to add one more condition, if the users do not enter there initials on yesterdays row, send an email to them. I don't know how to look at yesterdays date and if that cell is empty, send the email to those users.
[TABLE="width: 366"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody>[TR]
[TD="width: 107, bgcolor: transparent"]Date[/TD]
[TD="width: 85, bgcolor: transparent"]user 1[/TD]
[TD="width: 74, bgcolor: transparent"]user 2[/TD]
[TD="width: 99, bgcolor: transparent"]user 3[/TD]
[TD="width: 123, bgcolor: transparent"]user 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/1/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]wmg[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/2/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/3/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/4/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/5/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/6/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pds[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/7/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/8/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/9/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/10/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/11/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/12/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/13/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/14/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/15/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
.
Paste this macro into a module :

Code: 
Option Explicit


Sub NoInitials()


Dim daterng As Range
    Dim DateCell As Range
    Dim WorkSht As Worksheet
    Dim dateStr As String
    Dim c As Variant    

    Application.ScreenUpdating = False
   
        ActiveSheet.Select
          
        Set daterng = Range("A:A")
        'Set daterng = WorkSht.UsedRange
       
        For Each DateCell In daterng
            DateCell.Activate
            ActiveCell.Select
            'On Error Resume Next
            dateStr = DateCell.Value
            
            If dateStr = Date Then
                DateCell.Select
                DateCell.Offset(0, 1).Select
        
                With Selection
                    Range(Selection, Selection.Offset(0, 3)).Select
                    For Each c In Selection
                        If c.Value = "" Then
                            MsgBox "Do something." '<-- replace this with a call to your macro
                            Range("A1").Select
                            Exit Sub
                        End If
                    Next
                Range("A1").Select
                Exit Sub
                End With
                
            End If
            
        Next
       
    Application.ScreenUpdating = True
    
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/dvhcVXJWyqG8SuGqyUsBh0zy9nObMZlcJOJhHAI5xrw
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Outlook File Attachment using VBA
2 3
Replies
22
Views
3K
RoryA
RoryA
R
  • Question Question
Mail merge to insert the picture in the mail body and schedule the email
Replies
0
Views
1K
rafeek4u
R
B
  • Question Question
Automating Outlook Using Custom Tags in Email Templates
Replies
0
Views
562
brncao
B
K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
385
Domenic
D
K
  • Question Question
Call cell value Macro
Replies
1
Views
201
karldugan
K

Forum statistics

Threads
1,223,740
Messages
6,174,223
Members
452,552
Latest member
Kleets

Share this page

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