Insert current date inside HTML text for emails

user_15479405 0
Viewed 971

I want to send outlook email from excel.

I need to insert today's date inside the HTML code.

"strDate" in the email body does not work when trying to format the date.

I tried replace function, Format (Date) and other ways.

Sub SharePerformance1()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xOutMsg As String
    Dim strDate As String

    On Error Resume Next

    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    strDate = Format(Date, "dd mmm yyyy")
    xOutMsg = "Good morning!<br />This pay period is from <b><span style=""color:#CE0426"">&strDate&</span style=""color:#CE0426""></b>To help ensure you are paid accurately and timely, please follow the instructions below.<br /><br />" & _
              "<u>Salaried team members</u><br />" & _
              "<span style=font-size:5px>&#9679;</span> Reason e.g. sick, vacation, jury duty<br /><br />" & _
              "Thank you!"
                  
    With xOutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Notice" & Format(Now() + 1, "dddd, mmmm dd") & " for Pay Period " & Format(Now() - 8, "dd") & "-" & Format(Now() + 3, "dd/yyyy")
        .HTMLBody = xOutMsg
        .Display
    End With
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
outlook vba excel html
3 Answers

The variable strDate needs to be outside the quotes. I've fixed that in the code below and also tidied things up a bit. In particular I've broken the creation of the HTML into multiple lines which should hopefully make it easier to maintain, debug and add to.

Sub SharePerformance1()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xOutMsg As String
Dim strDate As String

    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    strDate = Format(Date, "dd mmm yyyy")
    xOutMsg = xOutMsg & "Good morning!<br><br>"
    xOutMsg = xOutMsg & "This pay period is from <b><span style=""color:#CE0426"">" & strDate & "</span></b><br><br>"
    xOutMsg = xOutMsg & "To help ensure you are paid accurately and timely, please follow the instructions below.<br><br>"
    xOutMsg = xOutMsg & "<u>Salaried team members</u><br>"
    xOutMsg = xOutMsg & "<ul><li>Reason e.g. sick, vacation, jury duty</li></ul><br>"
    xOutMsg = xOutMsg & "Thank you!"
                  
    With xOutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Notice " & Format(Now() + 1, "dddd, mmmm dd") & " for Pay Period " & Format(Now() - 8, "dd") & "-" & Format(Now() + 3, "dd/yyyy")
        .HTMLBody = xOutMsg
        .Display
    End With
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    
End Sub

Message generated by above code.

user_28500260

This works fine here: (Note: Edited per @NickAbbot to remove extra ampersands)

xOutMsg = "Good morning!<br />This pay period is from <b><span style=""color:#CE0426"">" & strDate & ". </span style=""color:#CE0426""></b>To help ensure you are paid accurately and timely, please follow the instructions below.<br /><br />" & _
              "<u>Salaried team members</u><br />" & _
              "<span style=font-size:5px>&#9679;</span> Reason e.g. sick, vacation, jury duty<br /><br />" & _
              "Thank you!"

Also, what kind of error are you expecting in routine use? Usually not a good idea to have an unqualified On Error Resume Next unless you know you will only get a single error which can be ignored.

Here is a screenshot of the rendered text:

enter image description here

user_28729220

Two Changes:

  1. You need a space after "Notice" .Subject = "Notice " & Format...

  2. Not this &strDate& This: " & strDate & "

user_30710510
Related