Outlook VBA: Create email from Template with dynamic Subject

 

This sub helps open outlook template and automatically update the subject line with the current date and week number.

Using outlook template (.oft) is the easiest way to manage your email content, easily format text, add tables, or update to/cc/bcc addresses directly in the template file without touching the VBA code. 

Open the VBA Editor (Alt+F11), insert a New Module, then paste the code below. Remember to adjust the parameters to fit your data structure:

Sub opentemplate()

    Dim olapp As Object

    Dim olitem As Object

    Dim weeknum As Integer

    Dim reportdate As Date

    Dim subjectdate As String

    ' init outlook app

    Set olapp = CreateObject("outlook.application")

    

    ' 1. get your .oft template path

    Set olitem = olapp.CreateItemFromTemplate("d:\onedrive2\onedrive\desktop\temporately\untitled.oft") 'change your template address here

    ' 2. setup date & week number

    reportdate = Date

    weeknum = DatePart("ww", reportdate, vbMonday)

    subjectdate = Format(reportdate, "mm/dd/yyyy")

    ' 3. update subject line: [date] + [text] + [week]

    olitem.Subject = subjectdate & " daily report w" & weeknum

    ' display for checking

    olitem.Display

    ' clean up

    Set olitem = Nothing

    Set olapp = Nothing

End Sub