SOLVED
Home

VBA Coding - inserting dates into body of email

%3CLINGO-SUB%20id%3D%22lingo-sub-482030%22%20slang%3D%22en-US%22%3EVBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482030%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20creating%20a%20VBA%20Macro%20whereby%20I%20can%20draft%20multiple%20emails%20at%20once%2C%20in%20order%20to%20remind%20people%20of%20their%20respective%20deadlines.%20The%20deadlines%20are%20driven%20from%20an%20excel%20worksheet.%20The%20code%20I%20have%20currently%20is%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Sub%20CreateNewMessage()%3C%2FP%3E%3CP%3EDim%20outlookApp%20As%20Object%3CBR%20%2F%3EDim%20outlookMail%20As%20Object%3C%2FP%3E%3CP%3ESet%20outlookApp%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3ESet%20outlookMail%20%3D%20outlookApp.CreateItem(0)%3C%2FP%3E%3CP%3E%26nbsp%3BWith%20outlookMail%3CBR%20%2F%3E%26nbsp%3B%20.To%20%3D%20%22X.X%40ABC.com%22%3CBR%20%2F%3E%26nbsp%3B%20.Subject%20%3D%20%22This%20is%20the%20subject%22%3CBR%20%2F%3E%26nbsp%3B%20.BodyFormat%20%3D%202%3CBR%20%2F%3E%26nbsp%3B%20.HTMLBody%20%3D%20%22Hello%20XX%2C%3C%2FP%3E%3CP%3E%20I'm%20looking%20to%20confirm%20the%20dates%20for%20delivery%20of%20your%20Project%20information.%20Per%20my%20schedule%20we%20require%20the%20information%20to%20be%20delivered%20by%20%3DSheet4.Range%22D3%22.%20Please%20can%20you%20confirm%20with%20me%20that%20this%20is%20feasible%3F%3C%2FP%3E%3CP%3E%20Many%20thanks%2C%3C%2FP%3E%3CP%3E%20%22%3CBR%20%2F%3E%26nbsp%3B%20.Display%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3ESet%20outlookMail%20%3D%20Nothing%3CBR%20%2F%3ESet%20outlookApp%20%3D%20Nothing%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20the%20%3DSheet4.Range%22D3%22%20I%20think%20that%20is%20causing%20the%20error.%20Effectively%20I%20want%20this%20command%26nbsp%3Bto%20pull%20a%20date%20into%20the%20body%20of%20the%20email%20based%20on%20a%20specific%20cell.%20I'm%20currently%20getting%20an%20error%20saying%20Compile%20Error%3A%20Expected%20end%20of%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20anyone%20be%20able%20to%20help%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERoxo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482638%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482638%22%20slang%3D%22en-US%22%3E%3CP%3EDoesn't%20come%20up%20directly%20with%20the%20debug%20option%20on%20error%20so%20I%20have%20manually%20stepped%20into%20the%20code%20as%20per%20the%20attached%20screenshots.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110255iD9A1452FAE902DD4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%201.jpg%22%20title%3D%22Debug%20Part%201.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%201%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110257i67F1407AD29A7A31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%202.jpg%22%20title%3D%22Debug%20Part%202.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%202%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110258iC2675B727B7F56C8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%203.jpg%22%20title%3D%22Debug%20Part%203.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%203%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110256i14056984013CC9F9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%204.jpg%22%20title%3D%22Debug%20Part%204.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%204%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110259iDAA90C10C2D3B2DF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%205.jpg%22%20title%3D%22Debug%20Part%205.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%205%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110260i8E4B46AE1E880E52%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Debug%20Part%206%20-%20error.jpg%22%20title%3D%22Debug%20Part%206%20-%20error.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDebug%20Part%206%20-%20Error%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482549%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482549%22%20slang%3D%22en-US%22%3EHi%2C%20Roxo.%3CBR%20%2F%3ECould%20you%20please%20do%20the%20following%3A%3CBR%20%2F%3EWhen%20you%20get%20the%20error%2C%20click%20Debug%20and%20send%20a%20print%20screen%20of%20the%20text%20that%20shows%20highlighted%20in%20yellow.%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482502%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3BHey%20Celia%2C%20thanks%20for%20your%20help%20with%20this%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20now%20getting%20Runtime%20error%20%229%22%3A%20Subscript%20out%20of%20range.%20I've%20now%20got%20the%20following%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Sub%20CreateNewMessage()%3C%2FP%3E%3CP%3EDim%20outlookApp%20As%20Object%3CBR%20%2F%3EDim%20outlookMail%20As%20Object%3CBR%20%2F%3EDim%20DueDate%20As%20String%3CBR%20%2F%3EDueDate%20%3D%20Format(ThisWorkbook.Sheets(%22Sheet4%22).Range(%22D3%22).Value%2C%20%22YYYY-MM-dd%22)%3C%2FP%3E%3CP%3ESet%20outlookApp%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3ESet%20outlookMail%20%3D%20outlookApp.CreateItem(0)%3C%2FP%3E%3CP%3E%26nbsp%3BWith%20outlookMail%3CBR%20%2F%3E%26nbsp%3B%20.To%20%3D%20%22X.X%40ABC.com%22%3CBR%20%2F%3E%26nbsp%3B%20.Subject%20%3D%20%22This%20is%20the%20subject%22%3CBR%20%2F%3E%26nbsp%3B%20.BodyFormat%20%3D%202%3CBR%20%2F%3E%26nbsp%3B%20.HTMLBody%20%3D%20%22Hello%20XX%2C%3C%2FP%3E%3CP%3E%20I'm%20looking%20to%20confirm%20the%20dates%20for%20delivery%20of%20your%20Project%20information.%20Per%20my%20schedule%20we%20require%20the%20information%20to%20be%20delivered%20by%22%20%26amp%3B%20DueDate%20%26amp%3B%20%22.%20Please%20can%20you%20confirm%20with%20me%20that%20this%20is%20feasible%3F%3C%2FP%3E%3CP%3E%20Many%20thanks%2C%3C%2FP%3E%3CP%3E%20%22%3CBR%20%2F%3E%26nbsp%3B%20.Display%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3ESet%20outlookMail%20%3D%20Nothing%3CBR%20%2F%3ESet%20outlookApp%20%3D%20Nothing%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20I%20made%20an%20error%20somewhere%3F%20I'm%20using%20Excel16.0%20Object%20library%20if%20that%20makes%20any%20difference%20to%20the%20referencing%20used%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482430%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314014%22%20target%3D%22_blank%22%3E%40R-o-x-o%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%2C%20there!%3C%2FP%3E%3CP%3EPlease%20try%20replacing%20the%20.HTML%20part%20of%20the%20code%20with%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20DueDate%20as%20String%3C%2FP%3E%3CP%3EDueDate%3D%3CSPAN%3EFormat(Thisworkbook.Sheets(%22Sheet4%22).Range(%22D3%22).Value%2C%22YYYY-MM-dd%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B.HTMLBody%20%3D%20%22Hello%20XX%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%20I'm%20looking%20to%20confirm%20the%20dates%20for%20delivery%20of%20your%20Project%20information.%20Per%20my%20schedule%20we%20require%20the%20information%20to%20be%20delivered%20by%22%20%26amp%3B%20DueDate%20%26amp%3B%20%22.%20Please%20can%20you%20confirm%20with%20me%20that%20this%20is%20feasible%3F%3C%2FP%3E%3CP%3E%20Many%20thanks%2C%3C%2FP%3E%3CP%3E%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20assuming%20the%20range%20D3%20in%20sheet%20Sheet4%20has%20a%20date%20value.%20You%20can%20replace%26nbsp%3BYYYY-MM-dd%20with%20another%20date%20format%20of%20your%20preference.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20let%20me%20know%20how%20it%20goes.%20Good%20luck!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483741%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483741%22%20slang%3D%22en-US%22%3EHi!%3CBR%20%2F%3E-%20what%20is%20the%20name%20of%20the%20sheet%20tab%20where%20your%20date%20is%3F%20Is%20it%20%22Sheet4%22%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-485104%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3BThanks%20that's%20sorted%20it!%20You're%20a%20hero%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-485901%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Coding%20-%20inserting%20dates%20into%20body%20of%20email%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314014%22%20target%3D%22_blank%22%3E%40R-o-x-o%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAwesome!!%20Very%20happy%20that%20I%20could%20help%20and%20that%20your%20tool%20is%20now%20working.%3C%2FP%3E%3CP%3EIf%20you%20can%20mark%20my%20answer%20as%20the%20best%20answer%20it%20would%20be%20very%20helpful.%3C%2FP%3E%3CP%3Ethank%20you%20and%20good%20luck!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
R-o-x-o
Occasional 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
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?

Highlighted
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 1.jpgDebug Part 1Debug Part 2.jpgDebug Part 2Debug Part 3.jpgDebug Part 3Debug Part 4.jpgDebug Part 4Debug Part 5.jpgDebug Part 5Debug Part 6 - error.jpgDebug 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!

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies