if the days between today and the renewal is same or = 0

October 12, 2017 at 12:36:37
Specs: Windows 7, Corei3/4gb
This is Excellent. Requesting you to please share where i will change the vba code if the days between today and the renewal/expiry is 0, because i need renewal date = today then mail send automatically.

Service Renewal Date Days till exp Status
Contract1 10/14/2017 1 Upcoming
Contract2 10/13/2017 0 Warning
Contract3 10/16/2017 3 Upcoming
Contract4 10/13/2017 0 Warning

Regards,
Tomal

message edited by Tomal


See More: if the days between today and the renewal is same or = 0

Reply ↓  Report •

#1
October 12, 2017 at 17:40:36
What is excellent? What code do you want to change?

We have no idea what you are referring to. If you found some code in another thread, then please post a link to that thread.

message edited by DerbyDad03


Reply ↓  Report •

#2
October 12, 2017 at 23:55:48
I have below vba code..Actually need to mail send automatically if below Renewal date and today date is same or = o.
Requesting you to please share where i will change the vba code if the days between today and the renewal/expiry is 0, because i need renewal date = today then mail send automatically.
Service Renewal Date Days till exp Status
Contract1 10/14/2017 1(b2-today() Upcoming
Contract2 10/13/2017 0(b3-today() Warning
Contract3 10/16/2017 3(b4-today() Upcoming
Contract4 10/13/2017 0(b5-today() Warning

VBA Coding :

Dim uRange
Dim lRange
Dim BCell As Range
Dim EmailString As String

Sub GetExpirations()

Set uRange = Sheet1.Range("C2")
Set lRange = Sheet1.Range("C" & Rows.Count).End(xlUp)
EmailString = Empty

For Each BCell In Range(uRange, lRange)

If BCell <= 3 Then

EmailString = EmailString & BCell.Offset(0, -2) & " is due to expire in " & BCell & " days" & vbCrLf

End If

Next BCell

SendMail EmailString

End Sub

Sub SendMail(iBody As String)

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

'If ActiveWorkbook.Saved = True Then

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = iBody

On Error Resume Next
With OutMail
.To = "rare.03.ts@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Services due to expire soon"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

'End If
End Sub


Reply ↓  Report •

#3
October 13, 2017 at 02:53:54
That code looks VERY familiar! although I don't use that approach anymore.


Contract1 10/14/2017 1 Upcoming
Contract2 10/13/2017 0 Warning
Contract3 10/16/2017 3 Upcoming
Contract4 10/13/2017 0 Warning

Which columns/rows do these go into? you need to provide a lot more detail before anyone can help....


Reply ↓  Report •

Related Solutions

#4
October 13, 2017 at 05:31:23
Hi:

Greetings!

Yes,That is why i said excellent.As per vba code,it's mailing advance <=3 days but my requirement is little different ,No need to send advance mail but if below dates match with today/current date,then vba will send the mail today/current date at 10:30am automatically.

So,Ms outlook will send the mail from excel only below two rows because today()/current date = 10/13/2017.If below dates match with today/current date then Vba will send the mail automatically on 10/13/2017 /current date at 10: 30 am.

As per my requirement, vba will send the mail from excel for below two rows only.

Contract2 10/13/2017 0 Warning (Contract 2 is due to expire in today)
Contract4 10/13/2017 0 Warning (Contract 4 is due to expire in today)

Therefore, Requesting you to please arrange to change the above VBA code for working with my above requirement(Current date match and mail send auto) .

Regards,
Tomal

message edited by Tomal


Reply ↓  Report •

Ask Question