How to change the message subject line in VBA

[muheebrahman]

Hi,

I have the below code, i need to change the subject line to pick up columns in sheet 1 based on the dates.
Can someone help me to change this code and tell me what do i use instead.

Sub CheckForExpiryDates()

Dim Cell As Range
Dim ExpiryDate As Date
Dim Mail_Msg As String
Dim Mail_Subj As String
Dim Rng As Range
Dim RngEnd As Range

Mail_Subj = "Check the file"
Mail_Msg = "Hi, Please complete activity in subject line" & vbCrLf _
& "and comfirm once done."

Set Rng = Worksheets("Sheet1").Range("A2")
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))

For Each Cell In Rng.Cells
ExpiryDate = Cell.Offset(0, 2)
If DateDiff("d", Now(), ExpiryDate) <= 1 And Cell.Offset(0, 3) = "" Then
SendEmail Cell.Offset(0, 1), Mail_Subj, Mail_Msg
Cell.Offset(0, 3) = Now()
End If
Next Cell

End Sub

[muheebrahman]

Can anyone her please help me on the above ....

[codeslizer]

Its better you use an object of the application you use for sending the mails. I basically use Outlook so below code can be used to send out an email through VBA using the object of the Outlook app.

Please Login or Register  to view this content.

Just replace the matter per your cell addresses. It should work for you.

[muheebrahman]

Codeslizer still it does not tell me how to change the subject to pick up the information from sheet 1, i have 40 different task which needs a separate subjectline based on the dates.

[codeslizer]

Can you provide a sample work of yours?

Basically, I thought you just needed to change the subject lines for all your tasks. So if you have the address of the cells from where you need to take the subject. you can simply replace the following with your cell address.

.Subject = "Here's the subject : Time - " & Format(Now, "mm/dd/yyyy hh:mm:ss")

There are various ways to do it. But it depends on how your data is organized.

[muheebrahman]

Can you provide a sample work of yours?

Basically, I thought you just needed to change the subject lines for all your tasks. So if you have the address of the cells from where you need to take the subject. you can simply replace the following with your cell address.

.Subject = "Here's the subject : Time - " & Format(Now, "mm/dd/yyyy hh:mm:ss")

There are various ways to do it. But it depends on how your data is organized.

Attached is the sample file i am using, what i am trying to do here is based on the dates i am trying to send reminders with the subject line being the activity discroption in Col A

[codeslizer]

One Quick Question what do you fill in column E? The reason am asking is there's a calculation involved with that column in the following part of the code:

Please Login or Register  to view this content.

And to answer your question, you can simply, reassign subject text inside the loop as follows and remove Mail_Subj = "Check the file" from the beginning - as it would be an extra statement running around in the memory:

Please Login or Register  to view this content.

The range "Rng" in your code basically stores the filled range of column A containing the rows with Serial numbers 1 to 24.

[codeslizer]

Mmm.. I hope you know that the code works now.. Must have received an email.. :D

< Never knew it was out :p >

[muheebrahman]

There is nothing entered in Col E as there is this box in that

[muheebrahman]

so should i just replace the code and remove the below
Mail_Subj = Worksheets("Sheet1").Range("A1")
Mail_Msg = "Hi, Please complete activity in subject line" & vbCrLf _
& "and comfirm once done."

[codeslizer]

Nope for the code you gave - But for what you understood, Yes. Worksheets("Sheet1").Range("A1") returns the cell value of only A1. Please correct me if am wrong anywhere..

You wish to have mail subject as the contents in column B corresponding to the date on which it expired. Say if you have empty field at D2, then if you hit on "Check Date" the macro runs and fills D2 with today's date and sends out an email with the subject as in B2. And so goes for D5 and D6 where the fields were empty and filled in as today's date by the macro and sends out mails with corresponding subjects in B5 and B6

If that's the way you want - then here's the full code for that method "CheckForExpiryDates()"

Please Login or Register  to view this content.

[muheebrahman]

Col D is what i have entered manually, this is date by which the activity needs to be completed. so what it does it when i hit the button it checks for the date in col D then it is it less than 1 day then it will send email to the address with the subject line in col B. hope this one is clear sorry for the confusion.

[codeslizer]

it checks for the date in col D then it is it less than 1 day

Hey.. do you mean the date entered in the column D is less than a day compared to current time?

[muheebrahman]

For example Weekly TB Report Every Monday (draft) Germany to be excluded and Travis to be included send to TL's directly and cc to mgmt in col b2 should be completed by 29th Jul 2013. so the macro checks if 29th Jul 2013 is less then one day from today then it will send an email.

Ideally if the task needs to be completed by 31st July 2013 then the ,macro should send the reminder email today.

[codeslizer]

Oh.. gotcha!

Your code needed just a little tweak which I have done for you. Below is the code and attached is the updated file:

Please Login or Register  to view this content.

Basically the if condition was taking all the dates into consideration which were less than the expiry date you'd enter in column D. Say, if the date was 29th July, then the code would send email to even for that, which actually comes out of the one day window. Added another check : DateDiff("d", Now(), ExpiryDate) > 0 to make sure the expiry date is more than today's date.

Also, have added a few extra columns for your informational purposes like
1. [Formulated Column] Mail sent? - Tells you which all reports fall into that one-day expiry window.
2. [Macro-Column] Mail Sent At - After running the macro, tells you at what time did the mail was sent
3. [Formulated Column] Time to Expire - Tells the time left to reach the expiry time. It's updated whenever the macro is ran

Hope this is what you're looking for...

[muheebrahman]

Awwesome.. man you rock...thats more than what i expected... thanks buddy.. thank you so very much.

[codeslizer]

Heh.. Heh.. Glad to help. Don't forget to mark your thread as [Solved]

[muheebrahman]

Hey Codeslizer, one quick question would you know if it is possible to set those days as automation, these fall on specific days of the month. like 1st workingday of the month, 15th working day of the month and so on. is it possible to get an formula to automate these as well...

[codeslizer]

You mean you'd like to have Expiry Date set automatically to specific dates of the running month? I really didn't get you fully..
Please elaborate a little (with an example too if possible)..

[muheebrahman]

oh yes for example the weekly report needs to be sent every monday, and some report needs to be done every 15th of the month and few reports need to be done on the 25th of the month... so these days of the month are constant. is it possible to get a formula or code so that every month it takes the date according to these days...

One more question on the file once the col F is marked as sent is it possible to stop sending those emails again ?

[codeslizer]

Hey Muheeb, took some time as I was working on your file. What I have done is - I added two new columns

1. Report Cycle (Required) - This is to set if you'd like to update the report every Mondays, 15th Day, or 25th Day. Makes necessary changes per your selection. Please note, there's a code behind the sheet (macros) so Undos won't work (as changes made by macros don't support Undo) to the Expiry Dates if the changes are made using this option.
2. Activity Expired (Additional) - Highlights the reports which have already expired. I thought you'd need to know which all processes / activities have already expired so that you can make respective changes to their next update time.

Code changes : have been made in Worksheet - Sheet1 and to the general macro - CheckForExpiryDates()

1. Codes for Sheet1:

Please Login or Register  to view this content.

2. Codes for CheckForExpiryDates() : I have just added a counter variable to keep the count of activities expired for reporting purposes (message pop-up) and changes in IF condition to make sure not to look at activities which have already been reported, i.e. Sent as you asked:

is it possible to stop sending those emails again ?

Please Login or Register  to view this content.

Attached is the file. Try playing with it, and let me know for if this is not something you've been expecting.

[muheebrahman]

Sorry Codeslizer, left early yesterday.

This is amazing. seems like got bonus for everything i asked.. this sure will be a good thing for me to learn. will make a copy and playaround with it..

Thanks a ton mate.. u really are a life saver...

[codeslizer]

Cheers! Good to know it help you more than you expected!