04-24-2019 07:48 AM
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
04-24-2019 08:48 PM - edited 04-24-2019 08:52 PM
Solutionhi, 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!
04-25-2019 01:47 AM
@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?
04-25-2019 03:14 AM
04-25-2019 05:28 AM
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 1
Debug Part 2
Debug Part 3
Debug Part 4
Debug Part 5
Debug Part 6 - Error
04-25-2019 07:01 PM
04-26-2019 03:14 AM
@Celia_Alves Thanks that's sorted it! You're a hero :)
04-26-2019 06:31 AM
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!