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!

     

     

     

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

Doesn't come up directly with the debug option on error so I have manually stepped into the code as per the attached screenshots.

Debug Part 1Debug Part 2Debug Part 3Debug Part 4Debug Part 5Debug Part 6 - Error

 

Celia_Alves's avatar
Celia_Alves
MVP
Apr 26, 2019
Hi!
- what is the name of the sheet tab where your date is? Is it "Sheet4"?
  • R-o-x-o's avatar
    R-o-x-o
    Copper Contributor
    Apr 26, 2019

    Celia_Alves Thanks that's sorted it! You're a hero :)

    • Celia_Alves's avatar
      Celia_Alves
      MVP
      Apr 26, 2019

      R-o-x-o 

      Awesome!! Very happy that I could help and that your tool is now working.

      If you can mark my answer as the best answer it would be very helpful.

      thank you and good luck!

       

Resources