I am creating a VBA Macro that will fire emails to my distribution list every Wednesday.

I was wondering if there was a way to add a dynamic custom date that updates every week when I open the distribution list?

The part I want to add my custom date is here: emailItem.Subject = "Training Report - DD-MM-YYYY"

I want the date to be the last Sunday of every week.

Many thanks for your help!

Here is my code:

Option Explicit

Sub Send_Email_With_Attachment()

Dim emailApplication As Object
Dim emailItem As Object


Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

'Now build the email.

emailItem.To = Range("A2").Value

emailItem.CC = Range("B2").Value

emailItem.Subject = "Training Report - DD-MM-YYYY"

CodePudding user response：

EDIT i might have misunderstood you. If you need the last sunday instead of the next sunday you just need to replace the nextSunday part of the code with this:

Dim lastSunday as Date
lastSunday = DateAdd("d", 1 - Weekday(Now), Now)

Function Weekday returns an integer representing day of the week for supplied date parameter. The values it returns are as follows:

1. for sunday or (vbSunday)
2. for monday or (vbMonday)
3. for tuesday of (vbTuesday)

and so on. So for today (23.11.2021) which is tuesday function will return number 3.

Function DateAdd adds in our case because of the first parameter "d" number of days to provided date.

The last function we used is Format which returns a string representation based on provided date and format "dd-MM-yyyy"

So the part of your code where you set the subject should look something like this:

...
Dim nextSunday as Date
nextSunday = DateAdd("d", -Weekday(Now)   8, Now)

emailItem.Subject = "Training Report - " & Format(nextSunday, "dd-MM-yyyy")