SOLVED

VBA Coding - inserting dates into body of email

Copper Contributor

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

7 Replies
best response confirmed by R-o-x-o (Copper Contributor)
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!

 

 

 

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

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.

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 1Debug Part 2Debug Part 2Debug Part 3Debug Part 3Debug Part 4Debug Part 4Debug Part 5Debug Part 5Debug Part 6 - ErrorDebug Part 6 - Error

 

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

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

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

 

1 best response

Accepted Solutions
best response confirmed by R-o-x-o (Copper Contributor)
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!

 

 

 

View solution in original post