VBA Coding - inserting dates into body of email

[R-o-x-o]

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 code I have currently is below.

Public Sub CreateNewMessage()

Dim outlookApp As Object
Dim outlookMail As Object

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 =Sheet4.Range"D3". 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

Its the =Sheet4.Range"D3" I think that is causing the error. Effectively I want this command to pull a date into the body of the email based on a specific cell. I'm currently getting an error saying Compile Error: Expected end of statement.

Would anyone be able to help with this?

Many thanks,

Roxo

[#2 · Accepted solution]

@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!

[#3]

@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?

[#4]

Hi, Roxo.
Could you please do the following:
When you get the error, click Debug and send a print screen of the text that shows highlighted in yellow.
Thanks.

[#5]

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

[#6]

Hi!
- what is the name of the sheet tab where your date is? Is it "Sheet4"?

[#7]

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

[#8]

@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!