• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sending report basing upon the date

S

sasi251904

New Member
Hi All I am sasikanth, i an in need of a macro which should send an report basing upon the date lets take on every month 31st i want to send an excel sheet to the recipients via outlook. and again on 5th i need to send another report and again on 10.Can you please advise me on this. As iam badly in need of this macro
 
Hello sasi,


I understand that you need to send the report at every 5 days interval (like 5th, 10th, 15th, 20th, 25th, 30th/31st of every month). Is my understanding correct?


So code needs to read the date of a month, if it matches our condition it should send the e-mail.


We can use DatePart function to construct the logic under IF statement. Here is the logic.

[pre]
Code: 
Sub DtChkEmail()

Dim TempDate As Date

Dim TempDay As Integer

TempDate = Date

TempDay = DatePart("d", TempDate)

If TempDay = 30 Or TempDay = 31 Or TempDay = 5 Or TempDay = 10 Or TempDay = 15 Or TempDay = 20 Or TempDay = 25 Then

MsgBox "Today is the date to send the e-mail", vbOKOnly

'your code to send the e-mail will go here..

Else:

MsgBox "Today is not the scheduled date to send the e-mail", vbOKOnly

End If

End Sub
[/pre]

copy the code in a standard module and run it line by line by pressing F8.


In the above IF OR statement, if you put 9 somewhere, the first messagbox will be popped up as today is 2/9/2013.


In order to write the code for 'email sending part', please let us know how you would like to send the report? As an attachment or in the body of the e-mail?


Kaushik
 
Hi Kaushik,


You can reduce your OR argument part like below:

[pre]
Code: 
If Day(Date) Mod 5 = 0 Or Day(Date) = 31 Then
MsgBox "Mailing Date : " & Day(Date)
Else
MsgBox "Other Date : " & Day(Date)
End If
[/pre]
 
Hi Kaushik,


* Day function returns day of the month.

* Mod function, as the name suggests, returns the remainder after division.

* So
Code: 
Day(Date) Mod 5 = 0
will return true only for multiples of 5.


Shrivallabha
 
You must log in or register to reply here.
Back
Top