CODEWITHSUNDEEP
htmlexcelvbaoutlook
Van Nguyen
Van Nguyen

Reputation: 23

Insert current date inside HTML text for emails

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

Upvotes: 2

Views: 2782

Answers (3)

norie
norie

Reputation: 9867

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.

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

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

Upvotes: 2

Nick Abbot
Nick Abbot

Reputation: 501

Two Changes:

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

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

Upvotes: 0

Related Questions