I put it like this : I have about 300 contracts with different dates for review or renewal. I have applied Your solution and no more headaches, I just wait for an email -)))) . Great stuff ! Mr Dinesh Kumar Takyar !!
Hi Dinesh,This is really very helpful, I have one more question? Do we have a way to keep the excel in sharepoint location and that triggers an email or I need to have this in the local desktop only?
Hi there. These have been incredibly useful and I have let people know they are available! One thing that I am probably doing wrong isit works perfectly up until the formatting part and getting the date but the send email part will not work for me. It states the first line of the code is incorrect? Am I doing something wrong as evidently it works well for everyone else first line of code is marked in yellow. Many thanks
hello sir, i follow your videos regularly.regarding automate email remainders , i cannot define variables like Outllook.application in this code . i cannot find them as auto drop down while defining variables. Could you please me out if i need to do any outlook confirmations settings before this code?
These links will help: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/ www.exceltrainingvideos.com/tag/automate-email-via-outlook-using-excel-vba/ Or search www.exceltrainingvideos.com
You can try using a VBScript as shown in these VBA tutorials: www.exceltrainingvideos.com/tag/run-macro-with-a-a-visual-basic-script/ www.exceltrainingvideos.com/tag/launch-vb-script-using-task-scheduler/
Hello Mr. Dinesh. I tried the code, but I'm getting an error 'Type Mismatch' for mydate1 = Cells(x, 4).Value. Could you pls advise what is wrong in the code. Thank you.
Thank you for this explanation. 1. I have used a version of this code but would like to know how to make the emails stop if the reminder column is already formatted red and has the text "yes" in it. Every time it opens it displays all the emails again. 2. how can i put multiple email in the "to line" in the email that it sends.
i entered the code in the example but I get the following error for line "mydate1=cells(x,6).value". The error I get is "runtime error 13, type missmatch". How do I go about solving this?
Hello Sir, thanks for this thorough explanation, my code comes with message "Compile Error" "User-defined type not defined" and highlighted this syntax (myApp As Outlook.Application). is there anything I can do to fix this one, bare in mind when I was writing this variable it wasnt automatically filling like your one? please help if you can. thanks a lot
Did you activate the relevant libraries from Tools -> References in the Visual basic Editor. Example: Microsoft Outlook Library. This tutorial will guide: www.exceltrainingvideos.com/get-data-from-outlook-address-book-automatically/
Sir, I'm trying same code (till 11:20 - not included email part) in my system, but when I hit Run - nothing happens in my Sheet1. I'm using MS Office 365 ProPlus. Any help would be appreciated.
Thanks so much for the video. When I tried, "Dim myApp as Outlook", I was not getting "Outlook" in the drop down. Still I wrote the code and when I tried to run, I have got a compile error. "User-defined type not defined" Appreciate your advise in this regard.
@@rinkideep hope this helps- While in the VB Editor, select Tools | References from the menu bar. Scroll through the list of available references until you find the one for Microsoft Outlook x.0 Object Library (where x will depend on what version of Outlook you have installed.) Check it, then close the dialog.
Hi sir, thanks a lot, the video is very helpful.. Do i need to run the macro everyday for reminders? Or if i run it for once i will be notified whenever the due date is nearby?
Thanks so much again. This is fixed and the macro is working fine.I went to VB - Tools - References - and checked in the Microsoft Outlook 14.0 Object Library
Hii sir, i'm getting "object variable or with block variable not set", can you please suggest me the correction which I have to make in order to compile the macros, thanku
Hello, This script continues to send an email after the due date has passed. Is there any way to only send an email if the date is in between 0 and 7 days ahead? Thank you
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/how-to-create-notifications-in-excel-with-vba-revisited/ It's a good idea to do such searches on your own. It is more satisfying and results in confidence building!
Sorry. Just a quick question. I see that outlook has been opened with the message and all. But was the actual email sent? If so, wouldn't outlook close the email editing window? I'm looking to implement this to something I'm working on and wish to see if the contents of this tutorial is exactly what I need to know.
Thank you for the video. Really Helpful. However, is it possible to reference the content of a cell in the body of the email? (.Body = cell(x, 5) or something).
If I have a first and last name in the same row, is there a way to include this name in the body of the email? Thank u for the videos they have been very useful.
Dinesh Great video ! Just one question. How can we do the same routine for several columns ? For example I to track expiry dates for several courses (each training subject / column) by employees (Rows) so is there a way for the VB look all (or an given number of columns) on the spreadsheet ?
Hi Dinesh, I have a compilation error, invalid use of property on '.To' and the sub is highlighted? I assume this is because I don't have a reference library imported. Could you please advise as to what libraries I need to tick from references? Many thanks in advance.
Thanks Sir for this useful video. I tried the same and it works fine too. But I have some questions. 1) Does the Excel VBA script run automatically? 2) Will the automated emails be sent even if I don't open the excel file? 3) It also asks for outlook email id password each time it sends email. Please help.
Hello Sir, I want to be able to only send a singular email to myself once the loop meets my condition of 20 days until the event is. How can I end the loop once this condition is met?
Dear Dr. Takyar, I need to alert 2 employees when they are behind on a project. 1. the project sponsor and 2. the account clerk; the project sponsor will change but the account clerk will remain constant. I must automatically generate email reminders when the project is 30, 60 and 90 days behind. Can you please help me? Thank you, Kelly
Sir, this is most helpful. I am getting an error. It says " The following features cannot be saved in macro-free workbooks" •VB project. How do I resolve this problem? Respectfully, Maria
Hello, i write reminder code by your exemple, but it doesn't work correct i guess, because it don't send me reminder automaticaly. In macros i run programe it's work fine, but when i close it and the reminders day come, i don't get no message(email). Could you help me with this problem?
HI, Please tell me to run the macros without opening of excel file on schedule dates. Ex: I need to send one report through outlook on every friday. I created autocreation of Report. But this report auto send to my manager without opening of file. Awaits for your reply.
No i didn’t. But i have an idea. Use vbs script to run this macro without opening file. Put that vbs file in startup folder on windows. So that it auto open and execute, when ever u login to windows.
Sir, very useful video. What if there is a column having job order numbers and we want to include that particular job order number in subject line as well. Please advise
Dear sir..I love your teaching way...need some help....I have one Excel workbook..in which I have put ..kill function..macro...to delete the file on mention date..but what I want is that ..2 or 3 days before the mentioned date..the VBA msg will started to show when the person open the workbook..for remainder or warning kind of thing.......
Dinesh excellent videos - presentation, clarity, quality and content. I was struggling with a problem - and now I should be able to achieve it using vba. I had two sheets - in one I'd keep date in A col and the closing NAV of a mutual fund in B. The latest value would always be in range: A2,B2. In another sheet - I'd calculate my current portfolio balance using the latest value. I tried using a formula that linked the NAV cell (using relative and direct address of the cells) but every time I added a row of the latest NAV the cells with older values would be pushed down to A3:B3 and the formula referencing that range would be updated as well. Using VBA I should be able to solve it. Thanks.
Hello Dinesh, Will this Macro check the non responded emails on outlook and then send a auto reminder based on number of days condition,, IF no is there a possibility to do this Looking forward for your valuable inputs. Thanks Ashraf
These links will guide: www.exceltrainingvideos.com/tag/automate-multiple-rows-insertion/ www.exceltrainingvideos.com/get-data-from-userform-into-table/
Hello Dinesh Kumar Sir,your video is very good,but I hav issue with second line,because I m using lotus notes.i add reference but still not cmg.Dim MyApp As lotus. but after that when I press "." application not cmg
Hi Sir Thanks for this great video. I also want to know that did you already prepared any video on reply to bulk emails with original arrangements. Please share me the link or else can you help me get the Code to prepare the same
@@Exceltrainingvideos Hi Sir, Thanks for sharing such valuable video. However I have query. I already have a macro which is displaying msg but it's not getting send can you help me in this
Dear Mr Takyar, I am trying to build an Automate Email reminder for my own. Everything works well except on the .Send command The outlook mail to send we use the command - .Send When i save and run the VBA code, it prompts an error as below: Run-time error "-2147467259 (80004005)': Outlook does not recognize one or more names. The error is pointing towards the .Send command. I am using MS Excel 2013. How can i get this fixed? Hope to hear from you soon.
Dear Mr Takyar, Thank you for responding to me. When i type .Send the dropdown box appears which allows me to click on the .Send command. But when i run this VBA script its still showing me the same error. If i put .Display it is working as per your video shown, but if i put the .Send its showing error. I have followed exactly as your video. I went to Tools > References > and activated Microsoft Office 15.0 Object Library Microsoft Outlook 15.0 Object Library Could you please advice me which tool exactly do i need to activate from the libraries?
Hello sir i found your videos very helpful, however i am using excel 2016 and having some trouble .... i am getting a "compile error: User -defined type not define. Sub datesexcelvba() Subject = " Action Alert” .Body = “This is a reminder for you to Pull samples in three days ” & vbCrLf & cells (x,3).value vbCrLf & “Kindly ignore if pulled” vbCrLf & “Info.”
Yes i got it working thank you. A reference was not enabled in tools. Also can i add a code to this for it to trigger outlook to follow up in 15 days or so. Again thank you.
hi, i was wondering. what if i wanted to add in the body of the email information from a customer. lets say a creditcard is due and it sends an email would it be posible for that email to have the customers info taken directly from the excel sheet?
hello sir, i used this vba code to send notifications for inventory items that are exiring in 60 days but i also want to send row details in email body can you please help me in this
@@Exceltrainingvideos this one is good but it only send selected cell. is there any way it automatically changes according to the date that is selected
Hello Sir, kindly help me with the vba code that looks at the first date and the expiry date of a product were if the product is almost expiring it sends a mail notification. Kind Regards, Audience Mudenda
Sir, The code works great if I have dates in all the rows, but there are a few that will have "N/A" written in them. Is there any way to skip those rows that have "N/A" written in the cell? I keep receiving errors and I have tried several ways to fix it. I know it is probably something simple, but I cannot figure it out.
Hi Sir, How would you add code for a personalized greeting in the email body, a personalized subject and personalized email reference number, for example I have added a column for all three but I am not sure how to insert the code. Kindly advise.
Hi Sir, I was able to find the answer to my question and have implemented this code at work. I have 2 questions 1-Is there a way to add an if then or else statement that allows you to clear the reminder column when days are not equal to 3. 2-Is there a way to add HTML, so that you body equals a cell you can style the email with preferred attributes. Thank you the original code worked perfectly!!!
hello dinesh sir I tried to run this VBA program with my excel file but facing some compile error that user defined type not defined. I just followed the codes you provided in this vedio. I want to know whether there are some more steps before or after the instructions in this vedio which has not been provided. regards satyender
Brother can any tutorial to get such type alert for such type due date on excel file as notification bar until see or open the file as like facebook messenger / text message on mobile .
Question - my code is working fine but problem is with if condition. During debug its not reading the if condition. If I commented on the condition, it's working fine. Please suggest.
Hi Amit, i too facing the same issue, it is only considering Lessthen formula, and when i am simply writing equals-to formula, it is not working, neither the result of Lessthen is commend correct, program is considering all the values, even if it is more than 3 days
@@Exceltrainingvideos No. I did tool-references--and give ticket to all outlook related library. Then click OK. but still saying the user defined type is not defined
Is there a way to add HTML to the message? My test didn't recognize it, but we're hoping to stylize it. Also, is there a way to send a unique email to every email address? If I'm trying to remind someone to send me their Invoice and I have a reference number to give them, it would be extremely awesome and convenient to have it automatically inserted into an email. IE: "To whom it may concern: Invoice ### is due."
Unfortunately, the link provided doesn't address any of the questions I had. I did figure out how to use HTML for the email on my own (.HTMLBody instead of .Body), but still can't send a unique email to each email address in the list. I want to refer to each customers' unique Invoice Number automatically. It can be in the body or the header, it doesn't matter, but it IS important to reference an Invoice#. Is there a way to do this? The set up of my sheet is as follows: A1 - CUSTOMER, B1 - FOLLOW UP DATE, C1 - REMINDER, D1 - EMAIL, E1 - INVOICE# Thank you in advance!
hi i learned lot from this video, can you please show me a macro which reminds me break time reminder, i.e- if i want to take breaks in office for three fix times every day i want to learn macro which popup's three times to take break reminder
Hi Dinesh, great guide. I run a sheet which contains a number of suppliers we use, and the date their agreement expires with us. I want to set a reminder to happen for each supplier, that when there's 3 months left till the agreement expires, myself and my manager get a notification listing the company name, and a message after that name to say the agreement has 3 months till expiry. The only problem is, I want this to happen without needing to open up the sheet and run a macro, just want it to happen behind the scenes... is this possible? let me know if it is
I entered the data just as you have on the email. I am not getting the red object of the reminder to show up. I assumed that it was based on the fact that the dates are from 2014 and its using todays date but I am still not getting the reminder to show up. Any ideas on why that is?
Dinesh Kumar Takyar I tried both ways with todays dates and the ones on the file. I understand that the code is written for "today date" but even after I changed to current dates I wasn't getting the reminder columns to work.
Alicia Greenberg You can download the original Excel file here and try it out: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Hi Sir, how can we personalize the email, for example adding a column for first name and a column for a possible message. For example using VBA replace code.
Yes, I have code that is personalized which I use daily, but I have to use two workbooks one with the data and the other one has the VBA code which I then enter the emails for that day manually based on the dates of the first workbook.. I would like to consolidate into one workbook and automate.
Search www.exceltrainingvideos.com or Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Hello Sir, I've followed the entire code you've suggested here but when I push the run button the following massage appears, pls suggest, "Compile Error: user defined type not defined."
Sir, thanks & I've figured it out by your help but afterwards faced an error which is "subscript out of range" & the debugger is showing yellow mark on below "lastrow =Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row" can you pls help on it. thanks, saif
Sir, I've checked & input it correctly nevertheless it shows the massage, I presumed it is my lacking to understand "Cells(Rows.Count,1).End(xlUp).Row" this portion of the code, pls help if possible. thanks, saif
Hi Sir, I've Managed to run the code but pls need to know does this work automatically ? or I have to press the run button each time I open the worksheet ? If possible let me be clear. Thanks, Saif
sorry to trouble you as per your instruction first part is working fine, problem is second part merge with outlook, first and second line giving error, please help me.
Dear Mr Takyar, .Subject = "Contract end date nearing" .Body = "Please be informed that there is a candidate Contract End date is approaching for." & vbCrLf & "From Admin" For the above .Body message i would like to put in as the message above but instead of being Candidate, i would actually prefer the actual name of the candidate which is in Column A do be inserted into the email message. Please help me? Sincerely, Prasath
Try this: .Body = "Please be informed that there is a " & Range("A2").value & "Contract End date is approaching for." & vbCrLf & "From Admin". Here I've assumed that you've a header in Range A1. For all values in column A, you'll have to create a loop. Alternatively do a search at exceltrainingvideos.com
How do you set Excel to send you an email reminder with Outlook when, instead of a due date, the value of the cell becomes greater than a certain value?
Dear Sir, when i do test run with VBA porgram, the program run perfetly. (no Error). But after i close and open the file, the program not running automatically. i need to run manually. Why? Couly you please explain?
@@Exceltrainingvideos Dear Sir, is it VBA version is 7.1.1080 doesn't regoinze ".Display" format? because my program straight away send email without display first. Please kindly advise.
sir mujhe ek confusion he confusion ye ke mai excel main ek active x list box select karta ho jis mai automatically saari sheets ki naam ajae or mai active x listbox mai se ek sheet ko select karo or oos mai ye pata chale ke oos sheet mai 2 pages he or mai os ko apne marzi ke mutabiq print kya ye ho sakta he plz es pe ek tutorial banae.
Sub PopulateListBox() Dim ws As Worksheet 'clear contents Sheets("Sheet1").ListBox1.Clear For Each ws In Worksheets Sheets("Sheet1").ListBox1.AddItem ws.Name Next End Sub Private Sub ListBox1_Click() With Sheets(Me.ListBox1.Value) 'for hidden sheet ' .Visible = True .Select .PrintPreview End With End Sub
sir how can i write a command in VBA Module to take range suppose if the value of the parameter is up-to 1000 than interest rate = 5.3 , if value is up-to 10000 then interest rate = 6.5 and so on ?
9 years later and this is still helpful, thank you sir!
6 years later and people are still using your code, thank you!
Great to hear! Please share the VBA tutorials with your friends also on social media.
I put it like this : I have about 300 contracts with different dates for review or renewal. I have applied Your solution and no more headaches, I just wait for an email -)))) . Great stuff ! Mr Dinesh Kumar Takyar !!
Great 👍 Please share with your friends.
Dear sir, I am new to VBA, thanks for the sharing. Please continue helping
Thank you, I will. Please share with your buddies also.
Thank you so much sir. God bless you and your family and generations!!
Thank you! I have been finding a good VBA class for a while. This is the best one!
Glad it was helpful! Please share the Excel tutorials with your friends too.
Just loved it.... Thanks Dinesh ❣️
hir sir, can i use this method for sending sms to phone
Hi Dinesh, wanted to know this will send the reminder automatically? or do we have to schedule this?
Hi Dinesh,This is really very helpful, I have one more question? Do we have a way to keep the excel in sharepoint location and that triggers an email or I need to have this in the local desktop only?
Very useful. Thank you so much for your creating this video.
Glad it was helpful! Please share this channel with your friends too in case they need Excel VBA solutions.
Hi there. These have been incredibly useful and I have let people know they are available! One thing that I am probably doing wrong isit works perfectly up until the formatting part and getting the date but the send email part will not work for me. It states the first line of the code is incorrect? Am I doing something wrong as evidently it works well for everyone else first line of code is marked in yellow. Many thanks
Just to let you know I solved it! It was my set up in Tools. This has been invaluable to me!! Many many thanks!!!!!!
Thnx sir... Wonderful lesson....
hello sir, i follow your videos regularly.regarding automate email remainders , i cannot define variables like Outllook.application in this code . i cannot find them as auto drop down while defining variables. Could you please me out if i need to do any outlook confirmations settings before this code?
These links will help: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/
www.exceltrainingvideos.com/tag/automate-email-via-outlook-using-excel-vba/
Or search www.exceltrainingvideos.com
Thank you so much for creating this video.. it helps me a lot... really.. super..
it works for me ..
How this code work automatically in the background? Do we need to run it manually?
You can try using a VBScript as shown in these VBA tutorials:
www.exceltrainingvideos.com/tag/run-macro-with-a-a-visual-basic-script/
www.exceltrainingvideos.com/tag/launch-vb-script-using-task-scheduler/
Hello Mr. Dinesh. I tried the code, but I'm getting an error 'Type Mismatch' for mydate1 = Cells(x, 4).Value. Could you pls advise what is wrong in the code. Thank you.
+Murali Pariyarakaran This link will help: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Thank you for this explanation.
1. I have used a version of this code but would like to know how to make the emails stop if the reminder column is already formatted red and has the text "yes" in it. Every time it opens it displays all the emails again.
2. how can i put multiple email in the "to line" in the email that it sends.
1. Use an IF condition
2.You can use a loop as shown in this link:
www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Amazing explanation, thank you!
Glad you enjoyed it!
great tutorial, very useful. Thank you.
i entered the code in the example but I get the following error for line "mydate1=cells(x,6).value". The error I get is "runtime error 13, type missmatch". How do I go about solving this?
Hello Sir, thanks for this thorough explanation, my code comes with message "Compile Error" "User-defined type not defined" and highlighted this syntax (myApp As Outlook.Application). is there anything I can do to fix this one, bare in mind when I was writing this variable it wasnt automatically filling like your one? please help if you can. thanks a lot
Did you activate the relevant libraries from Tools -> References in the Visual basic Editor. Example: Microsoft Outlook Library.
This tutorial will guide: www.exceltrainingvideos.com/get-data-from-outlook-address-book-automatically/
@@Exceltrainingvideos omg, you are legend Sir, thanks a lot. Very very helpful.
Awesome presentations.
Sir, I'm trying same code (till 11:20 - not included email part) in my system, but when I hit Run - nothing happens in my Sheet1. I'm using MS Office 365 ProPlus. Any help would be appreciated.
This link with a sample file for download will help:
www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Thanks so much for the video. When I tried, "Dim myApp as Outlook", I was not getting "Outlook" in the drop down. Still I wrote the code and when I tried to run, I have got a compile error. "User-defined type not defined" Appreciate your advise in this regard.
even i am getting the same error, have you got the answer, please share if yes
@@rinkideep hope this helps-
While in the VB Editor, select Tools | References from the menu bar. Scroll
through the list of available references until you find the one for
Microsoft Outlook x.0 Object Library (where x will depend on what version of
Outlook you have installed.) Check it, then close the dialog.
Hi sir, thanks a lot, the video is very helpful.. Do i need to run the macro everyday for reminders? Or if i run it for once i will be notified whenever the due date is nearby?
You can create a Vb Script to further automate the process to run at specific times. If you don't wish to code, you can use the task scheduler.
Thanks so much again. This is fixed and the macro is working fine.I went to VB - Tools - References - and checked in the Microsoft Outlook 14.0 Object Library
+Balaji Parthasarathy Great!
Hii sir, i'm getting "object variable or with block variable not set", can you please suggest me the correction which I have to make in order to compile the macros, thanku
Hello,
This script continues to send an email after the due date has passed. Is there any way to only send an email if the date is in between 0 and 7 days ahead?
Thank you
You can use specific dates.
Sir can u please share the VBA code again the link is not working
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/how-to-create-notifications-in-excel-with-vba-revisited/
It's a good idea to do such searches on your own. It is more satisfying and results in confidence building!
Sorry. Just a quick question. I see that outlook has been opened with the message and all. But was the actual email sent? If so, wouldn't outlook close the email editing window? I'm looking to implement this to something I'm working on and wish to see if the contents of this tutorial is exactly what I need to know.
This link with with a downloadable sample file will help: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Thank you for the video. Really Helpful. However, is it possible to reference the content of a cell in the body of the email? (.Body = cell(x, 5) or something).
Yes. Have a look at this link: www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
If I have a first and last name in the same row, is there a way to include this name in the body of the email? Thank u for the videos they have been very useful.
mechanicmike311 Yes. Just use the cell addresses.
Dinesh Great video ! Just one question. How can we do the same routine for several columns ? For example I to track expiry dates for several courses (each training subject / column) by employees (Rows) so is there a way for the VB look all (or an given number of columns) on the spreadsheet ?
Loop.
Hi Dinesh, I have a compilation error, invalid use of property on '.To' and the sub is highlighted? I assume this is because I don't have a reference library imported. Could you please advise as to what libraries I need to tick from references? Many thanks in advance.
Thanks Sir for this useful video.
I tried the same and it works fine too. But I have some questions.
1) Does the Excel VBA script run automatically?
2) Will the automated emails be sent even if I don't open the excel file?
3) It also asks for outlook email id password each time it sends email. Please help.
Same doubt from me also....
Hello Sir,
I want to be able to only send a singular email to myself once the loop meets my condition of 20 days until the event is. How can I end the loop once this condition is met?
Hi Dinesh
thank you very much for the tutorial.
if i want add one more day difference.
what is the code?
i mean 3day difference and onday.
Visit www.exceltrainingvideos.com There is a revised video on the subject which will help.
Dear Dr. Takyar,
I need to alert 2 employees when they are behind on a project. 1. the project sponsor and 2. the account clerk; the project sponsor will change but the account clerk will remain constant. I must automatically generate email reminders when the project is 30, 60 and 90 days behind. Can you please help me?
Thank you,
Kelly
Sir, this is most helpful. I am getting an error. It says " The following features cannot be saved in macro-free workbooks" •VB project.
How do I resolve this problem?
Respectfully,
Maria
Save your file as a macro-enabled file. The standard file in Excel has the name abc.xlsx. The macro-enabled file is abc.xlsm.
@@Exceltrainingvideos
Sir, thank you! My employer uses Gmail for communication. How do I make this work for gmail? I keep getting an error.
Thank you
Hello, i write reminder code by your exemple, but it doesn't work correct i guess, because it don't send me reminder automaticaly. In macros i run programe it's work fine, but when i close it and the reminders day come, i don't get no message(email). Could you help me with this problem?
Hi there, what code would I add to use the name of the person we are sending email to as the subject?
HI, Please tell me to run the macros without opening of excel file on schedule dates. Ex: I need to send one report through outlook on every friday. I created autocreation of Report. But this report auto send to my manager without opening of file. Awaits for your reply.
Have you got solution???.. If yes.. Please share with me.
No i didn’t. But i have an idea. Use vbs script to run this macro without opening file. Put that vbs file in startup folder on windows. So that it auto open and execute, when ever u login to windows.
Sir, very useful video. What if there is a column having job order numbers and we want to include that particular job order number in subject line as well. Please advise
Use the range or cells property to use the value.
Dear sir..I love your teaching way...need some help....I have one Excel workbook..in which I have put ..kill function..macro...to delete the file on mention date..but what I want is that ..2 or 3 days before the mentioned date..the VBA msg will started to show when the person open the workbook..for remainder or warning kind of thing.......
Use an IF condition like so:
IF mydate>-somedate and mydate
Dinesh excellent videos - presentation, clarity, quality and content.
I was struggling with a problem - and now I should be able to achieve it using vba.
I had two sheets - in one I'd keep date in A col and the closing NAV of a mutual fund in B. The latest value would always be in range: A2,B2. In another sheet - I'd calculate my current portfolio balance using the latest value. I tried using a formula that linked the NAV cell (using relative and direct address of the cells) but every time I added a row of the latest NAV the cells with older values would be pushed down to A3:B3 and the formula referencing that range would be updated as well. Using VBA I should be able to solve it. Thanks.
Hi Sir, is it possible to add Follow up flags in Excel from outlook for 'Mark Complete' or any other colored flags?
Yes, you can.
Hello Dinesh, Will this Macro check the non responded emails on outlook and then send a auto reminder based on number of days condition,, IF no is there a possibility to do this
Looking forward for your valuable inputs.
Thanks
Ashraf
Interesting and important question.
How can I add a value at every end of month to a table.. ? Let’s say I have a few subscription and I want keep track of the costs
These links will guide:
www.exceltrainingvideos.com/tag/automate-multiple-rows-insertion/
www.exceltrainingvideos.com/get-data-from-userform-into-table/
Will this automatically send a reminder without excel program being open on the computer?
Can you go for a jog without waking up? Also note, the computer cannot dream!
Hello Dinesh Kumar Sir,your video is very good,but I hav issue with second line,because I m using lotus notes.i add reference but still not cmg.Dim MyApp As lotus. but after that when I press "." application not cmg
Hi Sir
Thanks for this great video.
I also want to know that did you already prepared any video on reply to bulk emails with original arrangements. Please share me the link or else can you help me get the Code to prepare the same
This VBA tutorial will guide: www.exceltrainingvideos.com/tag/auto-acknowledge-receipt-of-mail-with-a-unique-tracking-number/
@@Exceltrainingvideos
Hi Sir,
Thanks for sharing such valuable video. However I have query.
I already have a macro which is displaying msg but it's not getting send can you help me in this
Hi sir,
Whenever the reminder is Yes,Email body should include the name.Can you suggest me the code.I'm entirely new for vba.Thank you in advance.
When i run VBA in the same program as guided noting happens in excel Sheet only the cells 9 & 10 appears . tell me how to execute further
This link will help:
www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Will this scrip run every time I open the file? Thanks
Dear Mr Takyar,
I am trying to build an Automate Email reminder for my own.
Everything works well except on the .Send command
The outlook mail to send we use the command - .Send
When i save and run the VBA code, it prompts an error as below:
Run-time error "-2147467259 (80004005)':
Outlook does not recognize one or more names.
The error is pointing towards the .Send command.
I am using MS Excel 2013. How can i get this fixed?
Hope to hear from you soon.
Check your code. Also check whether you've activated all the required libraries as shown in the video.
Dear Mr Takyar,
Thank you for responding to me.
When i type .Send the dropdown box appears which allows me to click on the .Send command.
But when i run this VBA script its still showing me the same error.
If i put .Display it is working as per your video shown, but if i put the .Send its showing error.
I have followed exactly as your video. I went to Tools > References > and activated
Microsoft Office 15.0 Object Library
Microsoft Outlook 15.0 Object Library
Could you please advice me which tool exactly do i need to activate from the libraries?
Check this link with a downloadable sample file:
www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Dinesh Kumar Takyar, can you please help me on a worksheet I am currently working on? Can I contact you via email?
Hello sir we can take more than one email id in a cell and send email.
th-cam.com/video/EW4CEGFZfVU/w-d-xo.html
Hello sir i found your videos very helpful, however i am using excel 2016 and having some trouble .... i am getting a "compile error: User -defined type not define.
Sub datesexcelvba()
Subject = " Action Alert”
.Body = “This is a reminder for you to Pull samples in three days ” & vbCrLf & cells (x,3).value vbCrLf & “Kindly ignore if pulled” vbCrLf & “Info.”
Check your code again.
Yes i got it working thank you. A reference was not enabled in tools. Also can i add a code to this for it to trigger outlook to follow up in 15 days or so.
Again thank you.
hi, i was wondering. what if i wanted to add in the body of the email information from a customer. lets say a creditcard is due and it sends an email would it be posible for that email to have the customers info taken directly from the excel sheet?
Yes. Just refer to the range(s) in your code:
hello sir, i used this vba code to send notifications for inventory items that are exiring in 60 days but i also want to send row details in email body can you please help me in this
This link will guide:www.exceltrainingvideos.com/tag/how-to-send-email-through-outlook-at-specific-time-using-vba/
@@Exceltrainingvideos this one is good but it only send selected cell. is there any way it automatically changes according to the date that is selected
Hello Sir, kindly help me with the vba code that looks at the first date and the expiry date of a product were if the product is almost expiring it sends a mail notification.
Kind Regards,
Audience Mudenda
Sir, The code works great if I have dates in all the rows, but there are a few that will have "N/A" written in them. Is there any way to skip those rows that have "N/A" written in the cell? I keep receiving errors and I have tried several ways to fix it. I know it is probably something simple, but I cannot figure it out.
Using an IF condition you can set N/A to blank.
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
sir, can we send auto emails through lotus notes?
www.rondebruin.nl/win/s1/notes/notes5.htm
Hi Sir,
How would you add code for a personalized greeting in the email body, a personalized subject and personalized email reference number, for example I have added a column for all three but I am not sure how to insert the code. Kindly advise.
Hi Sir,
I was able to find the answer to my question and have implemented this code at work. I have 2 questions 1-Is there a way to add an if then or else statement that allows you to clear the reminder column when days are not equal to 3. 2-Is there a way to add HTML, so that you body equals a cell you can style the email with preferred attributes. Thank you the original code worked perfectly!!!
hello dinesh sir
I tried to run this VBA program with my excel file but facing some compile error that user defined type not defined. I just followed the codes you provided in this vedio. I want to know whether there are some more steps before or after the instructions in this vedio which has not been provided.
regards
satyender
Visit www.exceltrainingvideos.com or www.familycomputerclub.com
Brother can any tutorial to get such type alert for such type due date on excel file as notification bar until see or open the file as like facebook messenger / text message on mobile .
This link will guide: www.exceltrainingvideos.com/how-to-perform-tasks-in-excel-at-specific-time/
Question - my code is working fine but problem is with if condition. During debug its not reading the if condition. If I commented on the condition, it's working fine. Please suggest.
Hi Amit, i too facing the same issue, it is only considering Lessthen formula, and when i am simply writing equals-to formula, it is not working, neither the result of Lessthen is commend correct, program is considering all the values, even if it is more than 3 days
hii sir, can we add specific credit card number which is going to expire in the subject of the mail which we are sending??
When I run this VBA, it comes out that: the user defined type is not defined. Which part I should define on your VBA code?
Have you activated the Microsoft Outlook Library from tools --> References.
@@Exceltrainingvideos No. I did tool-references--and give ticket to all outlook related library. Then click OK. but still saying the user defined type is not defined
I went there, and pick microsoft outlook 16.0 object library. then click OK. is that right?
Is there a way to add HTML to the message? My test didn't recognize it, but we're hoping to stylize it. Also, is there a way to send a unique email to every email address? If I'm trying to remind someone to send me their Invoice and I have a reference number to give them, it would be extremely awesome and convenient to have it automatically inserted into an email. IE: "To whom it may concern: Invoice ### is due."
+Amy Nelson This link might help: www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
I'll check it out, thanks for the prompt reply!
Unfortunately, the link provided doesn't address any of the questions I had.
I did figure out how to use HTML for the email on my own (.HTMLBody instead of .Body), but still can't send a unique email to each email address in the list.
I want to refer to each customers' unique Invoice Number automatically. It can be in the body or the header, it doesn't matter, but it IS important to reference an Invoice#. Is there a way to do this?
The set up of my sheet is as follows:
A1 - CUSTOMER, B1 - FOLLOW UP DATE, C1 - REMINDER, D1 - EMAIL, E1 - INVOICE#
Thank you in advance!
Hi Sir, I want to know how to copy range from excel and paste as image format(bitmap) in outlook body of email using VBA. Please help me
hi i learned lot from this video, can you please show me a macro which
reminds me break time reminder, i.e- if i want to take breaks in office
for three fix times every day i want to learn macro which popup's three
times to take break reminder
I have question, there is anyway tovsend a notification email while the Excel file closed??
Hi Dinesh, great guide.
I run a sheet which contains a number of suppliers we use, and the date their agreement expires with us.
I want to set a reminder to happen for each supplier, that when there's 3 months left till the agreement expires, myself and my manager get a notification listing the company name, and a message after that name to say the agreement has 3 months till expiry.
The only problem is, I want this to happen without needing to open up the sheet and run a macro, just want it to happen behind the scenes... is this possible?
let me know if it is
Thank you so much.
this actually got me promoted
Wow! Please share these Excel tutorials with your friends too.
Any of them ready to share my error and to sort immediately
I entered the data just as you have on the email. I am not getting the red object of the reminder to show up. I assumed that it was based on the fact that the dates are from 2014 and its using todays date but I am still not getting the reminder to show up. Any ideas on why that is?
Alicia Greenberg If you are working in 2015 how can you write the same dates?
Dinesh Kumar Takyar I tried both ways with todays dates and the ones on the file. I understand that the code is written for "today date" but even after I changed to current dates I wasn't getting the reminder columns to work.
Alicia Greenberg You can download the original Excel file here and try it out: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
Hi Dinesh,
How to use Notes instead of outlook in the code...
Haven't worked on this idea.
Hi Sir, how can we personalize the email, for example adding a column for first name and a column for a possible message. For example using VBA replace code.
Did you write some code?
Yes, I have code that is personalized which I use daily, but I have to use two workbooks one with the data and the other one has the VBA code which I then enter the emails for that day manually based on the dates of the first workbook.. I would like to consolidate into one workbook and automate.
Is this same formula for excel 2013 also ,please clarify
Search www.exceltrainingvideos.com or
Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
Dear Sir,
I need your help i have many line items in excel to send one customer please help how to do this
so can you share the code for automating the email on specific dates + different times of day on the same date?
This link will guide: www.exceltrainingvideos.com/tag/how-to-send-email-through-outlook-at-specific-time-using-vba/
Dear Sir
I want to use this same logic for lotus notes, how it is possible.
Pls reply me.
Hello Sir,
I've followed the entire code you've suggested here but when I push the run button the following massage appears, pls suggest,
"Compile Error: user defined type not defined."
+saif hasan Check you code line by line!
Sir, thanks & I've figured it out by your help but afterwards faced an error which is "subscript out of range" & the debugger is showing yellow mark on below
"lastrow =Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row"
can you pls help on it.
thanks,
saif
+saif hasan Check the name of your worksheets or exact number of worksheets.
Sir,
I've checked & input it correctly nevertheless it shows the massage, I presumed it is my lacking to understand "Cells(Rows.Count,1).End(xlUp).Row" this portion of the code, pls help if possible.
thanks,
saif
Hi Sir,
I've Managed to run the code but pls need to know does this work automatically ? or I have to press the run button each time I open the worksheet ? If possible let me be clear.
Thanks,
Saif
hello, Sir, I don't have office 365 is it possible that it's debugging for that its debbuging in the email Dim section please waiting for your reply
This link with a downloadable sample file will help: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
sir i followed your commends but i am getting user-defined type not defined error message how to fix it please help me to fix it thanks in advance.
At which line of code are you getting the error?
+Dinesh Kumar Takyar Sir In the second line where you have written
Dim myApp As OutLook.Application, mymail As OutLook.MailItem
sorry to trouble you as per your instruction first part is working fine,
problem is second part merge with outlook, first and second line giving error, please help me.
Dear sir this great work
and my issue
I not have outlook account ,so can do this notification from gmail.
This link will guide: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
dear sir kindly guide me.. when I inter 7 numbers in a cell than its auto change in 123-12-32 that formate .. that can possible?
I want that change. .. any one guide me plz
=LEFT(A1,3)&"-"&MID(A1,4,2)& "-"&RIGHT(A1,2)
www.exceltrainingvideos.com/text-handling-vba/
Dear Mr Takyar,
.Subject = "Contract end date nearing"
.Body = "Please be informed that there is a candidate Contract End date is approaching for." & vbCrLf & "From Admin"
For the above .Body message i would like to put in as the message above but instead of being Candidate, i would actually prefer the actual name of the candidate which is in Column A do be inserted into the email message.
Please help me?
Sincerely,
Prasath
Try this:
.Body = "Please be informed that there is a " & Range("A2").value & "Contract End date is approaching for." & vbCrLf & "From Admin".
Here I've assumed that you've a header in Range A1.
For all values in column A, you'll have to create a loop.
Alternatively do a search at exceltrainingvideos.com
How do you set Excel to send you an email reminder with Outlook when, instead of a due date, the value of the cell becomes greater than a certain value?
how to copy excel range and paste to outlook body with full format
please send me vba code of that
Dear Sir, when i do test run with VBA porgram, the program run perfetly. (no Error).
But after i close and open the file, the program not running automatically. i need to run manually. Why? Couly you please explain?
This link will help: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
A sample file is also available for download.
@@Exceltrainingvideos Dear Sir, is it VBA version is 7.1.1080 doesn't regoinze ".Display" format? because my program straight away send email without display first. Please kindly advise.
Thanks very helpful but stuck in
I want to use .send but showing "run time error '287':
Visit www.exceltrainingvideos.com to learn more.
Hello sir,How to send the email before 10 days ,15 days and 30 days of the due date ?
sir mujhe ek confusion he confusion ye ke mai excel main ek active x list box select karta ho jis mai automatically saari sheets ki naam ajae or mai active x listbox mai se ek sheet ko select karo or oos mai ye pata chale ke oos sheet mai 2 pages he or mai os ko apne marzi ke mutabiq print kya ye ho sakta he plz es pe ek tutorial banae.
Sub PopulateListBox()
Dim ws As Worksheet
'clear contents
Sheets("Sheet1").ListBox1.Clear
For Each ws In Worksheets
Sheets("Sheet1").ListBox1.AddItem ws.Name
Next
End Sub
Private Sub ListBox1_Click()
With Sheets(Me.ListBox1.Value)
'for hidden sheet
' .Visible = True
.Select
.PrintPreview
End With
End Sub
Sir how can we difine continues five days
Kindly tell me how to send mail automatically from two different sheets
What's the idea?
sir how can i write a command in VBA Module to take range suppose if the value of the parameter is up-to 1000 than interest rate = 5.3 , if value is up-to 10000 then interest rate = 6.5 and so on ?
+ubaid khan This link will help: www.exceltrainingvideos.com/sample-project-in-vba/
+Dinesh Kumar Takyar Thank you so much sir.
this link is really helpful ...
.
Why i am not getting outlook option after My App as?
Check your code again.