0

I have a VBA code that will send the users an email. I want the code to run automatically every single day. This code is written inside a button.

    For RowNum = 7 To Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row

        If VBA.Format((Sheet1.Cells(RowNum, "H").Value), "DDMM") = VBA.Format((Sheet1.Range("B1").Value), "DDMM") Then

            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
            .SentOnBehalfOfName = Sheet1.Range("B3").Value
            .CC = Sheet1.Range("B4").Value
            .To = Sheet1.Cells(RowNum, "I").Value
            .Subject = "Happy Birthday !"
            .Body = "Happy Birthday"

            .Display
            .Send
            End With
        End If

        Next RowNum

        Sheet1.Range("B1").Value = Format(VBA.DateValue(Sheet1.Range("b1").Value) + 1, "dd-mm")
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Osama Al-Hasan
  • 21
  • 5
  • 1
    You could use a `Timer` control to make it run once a day, but Excel will have to always be running, and eventually you will realize that Excel/VBA is not the right way to go about this. – braX Oct 17 '19 at 10:33
  • I totally agree with you. But based on the task on hand Excel was required. – Osama Al-Hasan Oct 17 '19 at 10:51
  • Alternatively you could put this in a `workbook_open` event and have it run when the workbook is opened. You could even have a hidden cell change to the current date and check for that to make sure it only runs once a day. But as above comment, this still relies on someone opening the sheet every day. VBA might not be the best way to go about this. – Plutian Oct 17 '19 at 10:53
  • 1
    You could maybe investigate transferring this code to a scheduled task running VBScript, which could do all of it, or just open the workbook and call the macro. – Nathan_Sav Oct 17 '19 at 10:54
  • see if this helps - https://stackoverflow.com/a/40144594/4539709 – 0m3r Oct 17 '19 at 21:55

0 Answers0