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 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, 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? Many thanks, "
.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

excel

Formulas and Functions

Macros and VBA

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, 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? Many thanks, "

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!

7 Replies

Celia_Alves
MVP
Apr 25, 2019
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.
- 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
    MVP
    Apr 26, 2019
    Hi!
    - what is the name of the sheet tab where your date is? Is it "Sheet4"?
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, 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? Many thanks, "

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
 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, 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? Many thanks, "
 .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?

Forum Discussion

VBA Coding - inserting dates into body of email

7 Replies

Resources