Monthly Reminded by VBA

Hi Andrew,

Thank you for the forum question.

If you copy the code below to your personal macro work - ThisWorkbook module it should do what you want.

Please have a look at my comments. You will need to add the reminder dates in the array function in the order first month then day and then year. You can add as many date you want separated by commas and the dates must be inside #. You need of course also to amend the email details and the path to the workbook you want to send.

Private Sub Workbook_Open()
Dim ReminderDates


'type the dates in the in the array. You have to do it the "American" way. First month then day.
ReminderDates = Array(#8/1/2014#, #12/31/2014#, #7/31/2014#)

For Each i In ReminderDates
If i = Date Then Call SendWorkBook
Next

End Sub

Sub SendWorkBook()

Dim OutApp As Object

Dim OutMail As Object

'change to the right path
Workbooks.Open ("C:\Users\jens\Documents\Excel Advanced VBA\Send Email\email.xlsm")

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "xxxx@xxxx.com" 'change the email
.CC = ""
.BCC = ""
.Subject = " Monthly report for month " & Format(Date, "mmmm") 'change the subject
.Body = "Hello World!" & vbCrLf & "New line text" 'change the body text
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

ActiveWorkbook.Close

End Sub


I Hope this will do the job. Please let me know if you get any problems.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector