Forum Discussion

R-o-x-o's avatar
R-o-x-o
Copper Contributor
Apr 24, 2019
Solved

VBA Coding - inserting dates into body of email

Hi,   I'm currently creating a VBA Macro whereby I can draft multiple emails at once, in order to remind people of their respective deadlines. The deadlines are driven from an excel worksheet. The ...
excel
Formulas and Functions
Macros and VBA
  • Celia_Alves's avatar
    Celia_Alves
    Apr 25, 2019

    R-o-x-o 

    hi, there!

    Please try replacing the .HTML part of the code with:

     

    Dim DueDate as String

    DueDate=Format(Thisworkbook.Sheets("Sheet4").Range("D3").Value,"YYYY-MM-dd")

     

     .HTMLBody = "Hello XX,<p> I'm looking to confirm the dates for delivery of your Project information. Per my schedule we require the information to be delivered by" & DueDate & ". Please can you confirm with me that this is feasible?<p> Many thanks,<p> "

     

    I am assuming the range D3 in sheet Sheet4 has a date value. You can replace YYYY-MM-dd with another date format of your preference.

     

    Please let me know how it goes. Good luck!

     

     

     

Celia_Alves's avatar
Celia_Alves
MVP
Apr 25, 2019

R-o-x-o 

hi, there!

Please try replacing the .HTML part of the code with:

 

Dim DueDate as String

DueDate=Format(Thisworkbook.Sheets("Sheet4").Range("D3").Value,"YYYY-MM-dd")

 

 .HTMLBody = "Hello XX,<p> I'm looking to confirm the dates for delivery of your Project information. Per my schedule we require the information to be delivered by" & DueDate & ". Please can you confirm with me that this is feasible?<p> Many thanks,<p> "

 

I am assuming the range D3 in sheet Sheet4 has a date value. You can replace YYYY-MM-dd with another date format of your preference.

 

Please let me know how it goes. Good luck!

 

 

 

  • R-o-x-o's avatar
    R-o-x-o
    Copper Contributor
    Apr 25, 2019

    Celia_Alves Hey Celia, thanks for your help with this :)

     

    I'm now getting Runtime error "9": Subscript out of range. I've now got the following code.

     

    Public Sub CreateNewMessage()

    Dim outlookApp As Object
    Dim outlookMail As Object
    Dim DueDate As String
    DueDate = Format(ThisWorkbook.Sheets("Sheet4").Range("D3").Value, "YYYY-MM-dd")

    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

     With outlookMail
      .To = "X.X@ABC.com"
      .Subject = "This is the subject"
      .BodyFormat = 2
      .HTMLBody = "Hello XX,<p> I'm looking to confirm the dates for delivery of your Project information. Per my schedule we require the information to be delivered by" & DueDate & ". Please can you confirm with me that this is feasible?<p> Many thanks,<p> "
      .Display
    End With

    Set outlookMail = Nothing
    Set outlookApp = Nothing

    End Sub

     

    Have I made an error somewhere? I'm using Excel16.0 Object library if that makes any difference to the referencing used?

Resources