Apr 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
Apr 24 2019 08:48 PM - edited Apr 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!
Apr 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?
Apr 25 2019 03:14 AM
Apr 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.
Apr 25 2019 07:01 PM
Apr 26 2019 03:14 AM
@Celia_Alves Thanks that's sorted it! You're a hero :)
Apr 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!
Apr 24 2019 08:48 PM - edited Apr 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!