How to send email reminder automatically from Excel Worksheet using VBA
HTML-код
- Опубликовано: 27 апр 2014
- Our Excel training videos on RUclips cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
For VBA details:www.exceltrainingvideos.com/h...
In this video you learn how to use the speak cells feature in VBA and how to send an email reminder automatically from an Excel Worksheet using VBA.
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
Thank you for this video Dinesh! It's really a big help for me who has forgotten how to do coding and the best thing is that your code is working!
Mr. Takyar it is really enjoyable to be your student. Thanks for every information piece that you download to web.
Hi
Thank you so much for this fantastic video. I was trying to find something that would be more of an effective way to send reminders out to students when they have assignments due and what I have just learnt from your video was just what I needed. You are an awesome teacher (your instructions were so easy to follow) and your knowledge is outstanding.
I have just sent email reminders to all my students...wow sooooo awesome. Thank you.
Great piece of knowledge.. thank you very much for this..
We are very thankful... May God bless upon u and ur family....
Dr. Takyar, your video tutorials are so easy to follow that I have now stopped watching video tutorials from others. Thanks heaps. You are awesome.
Thanks
Wonderful video sir. Life saver
Thanks a lot it was very useful
This code is working so well for me, thank you! I would like to add some more information in the body of the email, can you help me link information in cells of the same row that the email address is taken from? I would like to add the due date for the check up, and also the name of the contract that is due. All this information can be found in cells in my workbook.
Hi Dinesh,
Thank you for the very helpful video. I seem to get all the codes right but some reason it is only sending to the first email and not to the rest in the list which have the send reminder in it too. Please help.
Hi, thank you VERY much for your tutorials, they are excellent! !!! I was wondering if it was possible to set up a reminder from the workbook even if the workbook is closed, based on a column called REMIND and if the date in that column matches today's date... that Excel will generate an Outlook email for any entries that match the date?
Interesting question.
Sir, It was excellent video, Sir how can we send separate email to each users with values in that row. It is very useful for us. Kindly advice us & provide us programme.
I am waiting for your valuable suggestion.
Love the video, thanks for the information. Is there a way you could do this to send out multiple emails?
Thanks,
It was excellent video and explanation, How can we send separate email to each users with values in that row.
Hello, Did you find the solution to this? I am also struggling with the same problem
Hi Dinesh, thank you for sharing your great knowledge with your videos. One question please, I have a shared excel worksheet that is used by several people at the same time, how can I send an email at 4:00PM each day just once. My concern is that there will be more than one user with the worksheet open at 4:00 o'clock but I only want the email to be send once. Thank you.
Dear sir first i want to say thank you..I am watching ur all videos..It wil helps me to develop my carrier..I request u to put video for how to send mail automatically through lotus notes using macros.. waiting for ur video sir thank you so much..
I also want to know on lotus notes
Hello Dinesh,
Its a very helpful video, but please can you explain how we can include the data of credit card (column 1) and due date (column 2) of a person to whom we are sending the mail, in the form of table with the notification/reminder to an outlook mail.
Thanks,
Bhasker
Hello Dinesh. Thanks for sharing your knowledge.
Please is it posible to add an attachement to the mail intended for the Receipient.
www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
Dinesh Kumar Takyar
A lot of thks sir, I would like to ask about other subject how I can send automatic SMS from excel as reminder like the mail reminder
does this application works in MS Project as well?
Hi Dinesh I would like to ask do you have a tutorial how to send email reminder from excel file to outlook without using a vba button ....meaning it would send automatically with a certain criteria not using a button.Thanks
Dear Mr Dinesh first thank you for your hard work doing this explanations on youtube, i tried the codes but it keep telling me to give a name to send to, when debug keep pointing and .send , could you help me on this ?
Same Problem here
@@chinmoyjena7065 Did you find any solution to this?
Hi sir,
Our company using office365 versions of outlook for mail sending.
can we used auto mail sending in office 365 mail version
very informative. how about to get this formula easily
www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
Do i need to open excellsheet to send automatic reminder? is there a way if i dont open excellsheet and if the due dates comes it send reminder automatically?
were you able to find your answer ?
Again, thank you Dinesh.... I need to re-phrase my question/s:
Is it possible to set-up (program) excel and outlook to team up to automatically send reminder emails without any human triggering based solely on dates and an email address'? If so, any chance you might already have an example video you can point me to?
I am curious about this functionality as well.
Mr. Takyar,
The tutorial was awesome. I do have 1 question. I have a debug window that keeps popping up with the below line, any ideas?
.BCC = MailDest
.Subject = "Maintence Contract Renewal"
.Body = "Reminder: Contract expires in 30 days. Please renew."
.Send (PROBLEM IS THIS LINE)
End With
Hi Mr Dinesh, thank you for your excellent video. From you video, may I know how can I add the individual credit card detail into the MS Outlook's Subject field for those with "Send Reminder" remarks? Thank you very much!
This link will help: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/
hello dinesh, is the a code to activate the whole workbook. as in having the code work for every sheet in the entire workbook.
thnx. in advance
So you would have to open the worksheet, click the "Send Reminder" button for the macro to run.
Could you make it automatic, at a set time, daily for example, without having to open the worksheet?
Did you find an answer for this question? i am looking for the same thing
Hi, I've been looking for the same too. Mind to share if you have found a solution?@@gusey81
I'm looking for exactly the same thing
@Mitali Kaur well, i found a solution by using Google's spreadsheets in stead of Microsoft's Excel... With Google spreadsheet it is possible
Hello Dinesh, thanks for your knowledge sharing sessions. can we send mails through lotusnotes using VBA.. if it is possible how to write its code?
+Ashish Singh Have a look at this link: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
Hi Dinesh Thank you for this. I have set a formula wherein IF Cellx = TODAY() then "Send Reminder". Therefore, I would like the email sent upon opening the spreadsheet. How do I do this so that I dont need a command button to send? Thanks
Hi, Dinesh,
I'm getting a compile error on this line:
"for iCounter = 1 to worksheetFunction.CountA(columns15))"
Any ideas? I'm using Excel 2003 and have limited VBA knowledge.
Thanks,
hello sir
comment we have typed in excel row & column. This data should go as a body of a texture. Please help me on this
Its Impressive.
can i get a sample data sheet for the same?
Thank in advance.
Good evening sir
I am watching your all RUclips videos which one is too useful for me. For send reminder mail from Excel through out can be any other email. Like I have a two email in the same company one is only for me another one is group email, so I want to use my group email. How to do that.
Hi regarding the accounting program, i just want to know how to use button by just by clicking the item in the menu on the chart and automatic the price will come up
comment we have typed in excel row & column. This data should go as a body of a texture through outlook. Please help me on this
Hello sir,
thanks for this great video and sharing knowledge, everything works well for me, except one issue. the email is sending to the first row only. please advice how to send to multiple people based on the remarks column
You'l have to use a looping process to send email to all the people in your worksheet.
I got the same problem.. what do you use for looping ? Still use For and Next or Do While and Loop?
Hi Dinesh
I wish to know how to use macro to send the same email to different people which based on their unique ID and using lotus note.
For example
I have 3 overdue invoice A123, B123 and C123. A123 is a customer in America, B123 is a customer is Brazil and C123 is a customer in Chile. How do I send the same email which is reminding they to pay to different email account.
Can this send to multiple emails instead of just one (i.e. a group).
hello, thank you for the video. I am curious however as to how to loop this action every 24 hours (if I kept the file open) as opposed to having it auto generate when the workbook opens. Thanks
+erin baxman You can use a Timer. This link might help: www.exceltrainingvideos.com/timer-in-excel-using-vba/.
Thank you sir for the video. Sir, could you please help to take the subject line automatically from excel too?
+Deva Danamaiah Bhoompaga Just write in the loop:
mySubject=cell address and then access it like '.BCC' or '.Body':
www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
@@Exceltrainingvideos sir, .. can you explain with the loop?.. example the subject line is in second column .. thanks sir
hi bro how did u get the mail reminder icon when clicking the designer mode? i tried several times not successful. And is it possible instead of outlook send to Gmail?
hey did you figure this out ?
Hi Dinesh,
I am trying to create code that would auto send an email to two separate email address depending on the 2 different options in the a column called "location" the email would then send if the column called eta was todays date, can you assist.
Many thanks in advance.
Thanks for your wonderful toturial, cam you also share the code
Dhiraj Kumar Please check this link out for complete VBA code:
www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
Great Video Mr. Dinesh. Is it possible to send the Email via Excel in Lotus Notes? I hve seen your article of Gmail. Can you share for Lotus Notes also?
I haven't worked with Lotus Notes.
I create one payment reminder notification in excel and also add mail ID for send mail reminder notification, now I want to know how to remove highlight mark from excel sheet automatically and expired mark also can you suggest me for that.
Hi sir thanks for support , sir one thing is that i want to know how i set mail body through selection of excel cells range
Your answer is in your question!
Dr Takyar! Thanks for your wonderful toturial! I have one (maybe 2 :) ) small problem. The issue starts when one person possess more than one card (so there are 2 indentical mails), only one reminder is sent. My next question is how to instert the card number into .Body? Thanks!
DonDadullo Can you explain the 'card' part? Thanks.
Dinesh Kumar Takyar Dr. Takyar, Card stands for credit card number.
1-Is this possible to send the credit card number as email subject line so that the recipient should know that for which credit card this email meant for.
2-Is this possible, If we have a worksheet with large amount of data and we want information from more than one cell of same row to be sent over email to the recipient.
Alpha Juliet I would share a scenario.
if 50 people are working in my team and i need notification for their insurance expiry to be sent to my email address then can instruct excel and vba to send individual email notification but how would i know that this email stands for Neeraj, Naveen, Alex, Seema etc.
Thanks in anticipation.
Hello Sir...Great Video. How do I get the Mail reminder button? I am using Excel 2007. Thank you in Advance
hey did you figure this out ?
I have the same question. how do i get the mail reminder button?
Make sure you have added developer mode to the ribbon. After you have it, navigate there and there should be an insert button. Click on that will give you a list of form controls where you can create a mail reminder button
Great Video, i have developer ,i find in insert button but no have reminder button, how can get it?
There should be a drop-down menu when you click the insert button. One of the control options under "form controls" is "button". Select the button and put it somewhere on your worksheet. This button can be renamed to display any text you like, including "Reminder"
Thanks for the Tuto, I have a question, is it possible that the Send email reminder work even if excel worsheet is close ? Or do we have to always have excel open ? If yes, how can we force excel to be open every time we open the computer ?
Search www.exceltrainingvideos.com
I make it reminder and send maill also, onec i got the payment how to remove highlight mark from excle autometecally
I want to copy the excel range to send an email respect to the current date.
Example:
The table range is A1:D20
Column A for Date.
Now I want to copy the range A5:D9 to send an email body, A5 to A9 cell value are current date.
Hi sir....
I am having two excel sheets in (My D:\ drive) and the first excel sheets contains some 15 spread sheets like(Sheet1 as 12345 and Sheet2 as 56897 etc)...and my second excel contains the account numbers in a single sheet (first column)...
#1 has multiple sheets with information on them separated by tabs and the each tabs contains different account numbers.
And #1 each tabs contains the information about 0-30 xdays(each account) balance and 30-90 days balance ets....
#2 has a list of account numbers which is available in #1.
the requirement is need to copy account number in #2.and find the same in #1.then need to identify the particular text "0-30 days balance" copy the "Payable receivable cell value and copy the same in #2.
please help me on this
Hello I want to send remainder in old mail. What we already send last time .for example i was send one mail last week by using automation but i wasn't received reply so I have to send remainder on that same old mail
Dr I have tried look for Notification using Vba utube link. Have you removed it? I'm baffled on how you made that button :/
Sir, I need to know, how to send the reminder message to the mobile numbers automatically based on expiration date
Thanks in advance. I have an Excel application and wish to send email from within the VBA using SMTP.
I am using Excel for Mac 2016, and therefore the Microsoft CDO method is not an option as far as I can determine.
I do not wish to use Outlook, Mac Mail, or other email client, but rather wish to send directly from within the application.
Yes, you can do this using your smtp server with authentication (user-name, password).
If I want to include value of column 1. What should I do?
Can you send the email if youre company is using Lotus notes ?
Hello, im getting error "Runtime error 438 object doesn't support this properSet OutLookMailItem = OutLookApp.CreateIteam(0)ty or method" on line
Sir Thanks
Sir, is it possible to send with Column 1 content in subject or more than one column contents ... suppose ... in subject: (Credit Card No with other important information) instead of FYI
also let me know how to send without click on send button ..... work every Sunday to send this email to all ....... until status change
I would like to know if there is a way to send in the subject of the email, a reference from the worksheet? For example if I could include an invoice number to the subject of the email or in this case the credit card number in the subject?
+ldrz8323 Use a reference to the relevant cell.
is there away using Excel too highlight new information automatically in a database that is updated periodically by outside source?
We can use the worksheet 'change' event.
Dear Sir,
Thanks for nice tutorials on VBA.
Can U pls help me to send email reminder through Lotus Notes.
Have a look at this link: @view/698365-email-lotus-notes-spreadsheet-with-vba-.html
Dinesh Kumar Takyar Dear Sir, Thanks for instant reply. Can you pls paste the link, since i am not able to get thro' below mention-
@view/698365-email-lotus-notes-spreadsheet-with-vba-.html
SIr, Can you please brief about the code if i want to mention the specific cell values in the body of the mail. Eg. all Credit card numbers in this case shuld appear in the mail body.
You can use the cell addresses.
Thanks for this training video; one question : If I would like to write to mail body as " Your credit card no : ........................................... payment is due" ; In that case how can I add the credit card number cell information to " .body = " function?In this sample we are writing only a solid sentence.. but I would like to add some more informations which can be read from excel file also.Thanks for help from now on.
You can refer to a range in the worksheet and use a concatenation (&).
Respected Sir,
I did not understand the command button part.
When it was created. I request you to explain,
Thanks in advance.
First of all, thank you Dinesh.... I'm not sure how the grey button "Mail Reminder" came to be from between the "How to create notifications or reminders in Excel" video and the follow up "How to send email reminder automatically from Excel Worksheet using VBA" video. Two questions: 1). Is that button necessary in order to send emails automatically? Do I absolutely need to "the speak out reminder" function in order to send emails automatically?
1) No. It's just for convenience.
2). Absolutely not.
DInesh your tutorial was very helpful, but I was wondering if there is a way to send the reminders to all the due/expired accounts without clicking on the mail reminder button. In other words, I want excel to automatically trigger an email every time an account is due. Your help would highly be appreciated!
Nauman Khan You can place the code in the workbook_open procedure to do that. Have a look at this link to get an idea: www.exceltrainingvideos.com/how-to-auto-run-vba-project-when-excel-workbook-is-opened/
i tried the codes but it keep telling me to give a name to send to, when debug keep pointing and .send , could you help me on this ?
I am having the same issue
@@mwphillips2 Did you find any solution to it?
What if my MS outlook is not active? How can I go ahead sir?
does this only work on outlook as I am looking to do this but using Lotus Notes email instead?
+John Kenna This link might provide a solution: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
Hi Dinesh. I tried i in excel 2013 and it doesnt recognise the .bcc etc....
Dear Sir,
Can you please make a video like this for doing this exact process for making a meeting invite from VBA instead of a mail?
Will work on this idea.
hello sir how to contact you. I want to send remainder mail automatically to particular persons when something is enter in particular column. If suppose I have A,B,C,D columns so if something is entered in column D(ex: In D1,D2......) it should automatically trigger a mail to specific mail ids. How to do this please help me
HI Dinesh, super video. thank you very much. One question I have is, I could not initiate the emails through my Gmail account. could you provide how the code would. Thank you again
Riff Muthalif Have a look at the link:@send-email-using-gmail-with-excel-vba/
Dinesh Kumar Takyar Hi Sir, i tried to modify this code a bit its very useful thank you. While i am compiling my code all goes well but an error occurred at line " .Bcc " object variable or with block variable not set. Please help me to get it resolve. Thanks a lot again.
Dear Sir, is it possible to add more than one email address for every credit card?
Yes, you can
Everything works, but if there is no one to email I am getting a runtime error. how would I go about clearing that up?
I have the same question.
Do i need to open excellsheet to send automatic reminder? is there a way if i dont open excellsheet and if the due dates comes it send reminder automatically?
This link will help: www.exceltrainingvideos.com/run-macro-automatically-without-opening-excel-file/
Or search www.exceltrainingvideos.com
Hi Dinesh i wish to know how can I create a email reminder on excel for the Birthday
This link will help: www.exceltrainingvideos.com/tag/send-emails-automatically-via-outlook-using-excel-vba/
HI I am trying to figure out 2 things. 1 - how to have the Credit card data imputed as part of the email body message.
2 - how to include more than 1 email strain for the same notification.
Hi thanks, I followed all the steps until send reminder. Where can I find mail reminder on excel 2016? I cannot find it.
What are you talking about my dear?
when i insert the code its giving me a variable not defined as the error
Sir the below mentioned code for send reminder is not working. When I am clicking on the button the error "Outlook does not recognize name and more" is popping up. The error stopped on .Send and gets Yellow color. Kindly help
Sub SendReminderMail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String
Set OutLookApp = CreateObject("OutLook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(16))
If MailDest = "" And Cells(iCounter, 16).Offset(0, -3) = "Notification" Then
MailDest = Cells(iCounter, 16).Value
ElseIf MailDest "" And Cells(iCounter, 16).Offset(0, -3) = "Notification" Then
MailDest = MailDest & ";" & Cells(iCounter, 16).Value
End If
Next iCounter
.CC = MailDest
.Subject = "FYI"
.Body = "Agreement Expiring"
.Send
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Dear Sir,
Can you kindly help me to make one reminder sending excel sheet for me.
Please send me a reply.
This is an amazing work.
This is what you learned just now. For more help in Excel VBA on this topic you can search my channel using the keyword 'reminder' or 'send reminder'.
Hi,
Is there the source code or the original excel sheet on this tutorial available somewhere for download?
www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
Sir I have project of making a reminder through email(outlook) for list of tools for calibration along with numbers having a particular date for calibration so how to do coding in order to send email notification on that day itself ..please help me as early as possible
I can't get this to email any address in column 4. I had set the mail destination to my outlook email. There was an error saying "I need to know at least person to send this to" when MailDest="" like you did in the video. Is there something I'm doing wrong?
got the same problem here....
How did you get the mail reminder button on your spreadsheet to send the email?
Pam Guidash Developer+Insert (Command button)and Design mode+ and put the codes you wrote in the command button sheet in visual basic.
How to write autoresponder emails?
I use a Mac and I have 2016 excel. Under developer tab there is not a design mode. Is there any other way I can do it?
This link will help: www.rondebruin.nl/mac/setupmacoffice2016.htm
THANK YOU
how can upload excel file daily on email automatically?
Hi DineshWhen the button is clicked a runtime error pops up: "Outlook does not recognize one or more names". I have ensured that my Outlook references are correct in the VBA window but still no luck. Also, the email address is in the Outlook address book. ANy help would be greatly appreciated.
For the people wondering about the "Outlook does not recognize one or more names" error, make sure that your column heading is not the same word as your trigger word, for example trigger word to send the email was "Reminder" and my column heading was also "Reminder" as soon as i changed my column heading the error went away
HI, i am watching your videos from long time but however i need a help, i need to develop a excel/ VBA like we have a data which contains email id and birthday and work anniversy dates when ever we click on run it should automatically collect today birthday and anniversy people and send an automated email to them can any one help me
This link will help. You don't need to record the macro: www.exceltrainingvideos.com/tag/how-to-send-pesonalized-emails-automatically-using-excel-data-in-ms-word/
I have an excel file in which multiple sheets are present. From "Sheet1", I have to look for a character "d" in column "M". If "d" is present in column "M", then I have to copy the row contents (cells A-H) where the character is found to a new sheet "Sheet2" and send the reminder to mail address with the contents of "Sheet2" through Microsoft Outlook
Search www.exceltrainingvideos.com
By the way, what code have tried?
Sir please help me out.
Hi, i was trying to run the program. A RUN TIME ERROR Appears it always highlight the .send portion can you help with this. Thank you in advance😅
I have the same problem. Did you find any solution Monalie?
Share your code right here.
Mr. @@Exceltrainingvideos after trying out a few times I got it right. Thankyou.
But if you could please make a follow-up video on automating this whole process without even opening the excel sheet, it would be of tremendous value to lots of people.
how to developed reminder button
sir how to deselect today and yesterday date in filter pls help
Good question. Will work on it. In the meantime this link will provide some help: www.exceltrainingvideos.com/tag/copy-data-to-another-sheet-based-on-dates-with-vba/
If you search my website, you'll find more solutions on using dates in Excel VBA.
i have a record on sheet1 and i want to copy this record to sheet 2 with match the student record and find the April fee and paste the April cell of April and find the may fee and paste the may cell accordinglly
View the following link with sample file for practice:www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
You can also watch the video on my channel: ruclips.net/video/AzhQ5KiNybk/видео.html