Tech Support Guy banner
Status
Not open for further replies.

Automatic Outlook mail based on a column value in excel file

1K views 6 replies 2 participants last post by  jingle27 
#1 ·
Hello Forum member,

I am working on an excel file with hundreds of records and tens of columns. There is a column"L" which gives the date when the license key given in column "B" of a particular software is going to expire. I need to check the column and send email to Person X xxxx.yyyyy@abc.com 3 months in advance so that he can take poper action to get the license renewed. It is too much of a manual work every week.

I am looking for a macro that can send an email to person X stating that license key value given in coumn B is due to expiration in 3 months from today(based on value given in column L)

I have seem a similar thread in the forum giveb below but i need changes to it. Unfortunately, the thread is closed and i can not contact the person who gave the solution. I am not able to modify the code shared to match my requirement.:confused:
Automatic Outlook mail due date reminder based on Excel file

http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html

Can anyone please modify the code given in the above thread to match my requirement. Please!!Thank you!
 
See less See more
#3 ·
Hi, welcome to the forum.
Why can't you modify the code?
My guess it's not that difficult.
If you can add a sample file showing the layout you have and some dummy data will make it easier to help
Hello Hans,

Thank you very much for offering to help. :). I am a BA and do not know a word about VBA coding. I found the other post very similar to my case and so wanted to check if someone would be gracious enough to help me out.

I have attached a sample excel sheet.
So i want my outlook to send an email to Person X xxxx.yyyyy@abc.com, if the date is column L is less than or equal to 3 months from today. The email should state that the License value(key in column B) is due to expire on value given in Column L.

Also the other post mentioned that i can schedule the excel to open and run the marco on its own. Would you know how to do that too.

Thank you in advance.
 

Attachments

#6 ·
Well this should work.
I added the macro and the necessary procedures
Open the zip file and place both files in the same folder on your system
If you open the excel file as you normally would it will show the contents and you can run a macro manually
The vbs script which can be run by double clicking it will open excel run the macro, process the mail, save the file and close Excel.
You may probably have to 'tell' your system that this vbs script is trusted.
To make this automatic you will have to use the Windows scheduler or another scheduler program to process the file automatically at your set interval.
I added two columns to you sheet, One showing if the mail was sent and if True the column next to it with the time stamp when the mail was sent.
The macro will now only save the mail in your draft folder in Outlook, you will have to edit the macro to directly sent the mail by removing the comment sign (') in the line of code with 'Send and place a comment in front of Save.
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top