Forum Discussion
Display Dates in email as formatted in excel workbook via VBA code
Hi I have written some vba code to allow me to send a default email with reference to dates in a formulated workbook. it seems to work fine except the email displays the dates as dd/mm/yyyy where as...
After a search through various other videos and posts I managed to find the solution which was to just insert a Format function with each date expression in my vba script, ie the body now looks like this :
.Body = "Hi All" & vbNewLine & vbNewLine _
& "End of month will be " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") & ", Please have all requests for contract pricing submitted and approved via Jotform before " _
& S.Offset(0, 3) & " " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") & ", any request received between then and " & Format(S.Offset(0, 4), "dddd, dd MMMM, yyyy") & " will be actioned within the first 3 business days of " _
& Format(S.Offset(0, 5), "MMMM") & " with any cost increase applied." & vbNewLine & vbNewLine _
& "Please also review the attached workbook making sure to view both FIXED CONTRACTS and NATIONAL tabs, both show each contracts expiry date" _
& vbNewLine & vbNewLine & "Please note the Comment column and if any changes are required, you will need to discuss with your State/Area Manager for them to Approve " _
& "and submit to Pricing with notes regarding changes to be implemented before " & S.Offset(0, 3) & " " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") _
& vbNewLine & vbNewLine & "Regards" & vbNewLine & "The Pricing Team"
.Display
.Body = "Hi All" & vbNewLine & vbNewLine _
& "End of month will be " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") & ", Please have all requests for contract pricing submitted and approved via Jotform before " _
& S.Offset(0, 3) & " " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") & ", any request received between then and " & Format(S.Offset(0, 4), "dddd, dd MMMM, yyyy") & " will be actioned within the first 3 business days of " _
& Format(S.Offset(0, 5), "MMMM") & " with any cost increase applied." & vbNewLine & vbNewLine _
& "Please also review the attached workbook making sure to view both FIXED CONTRACTS and NATIONAL tabs, both show each contracts expiry date" _
& vbNewLine & vbNewLine & "Please note the Comment column and if any changes are required, you will need to discuss with your State/Area Manager for them to Approve " _
& "and submit to Pricing with notes regarding changes to be implemented before " & S.Offset(0, 3) & " " & Format(S.Offset(0, 2), "dddd, dd MMMM, yyyy") _
& vbNewLine & vbNewLine & "Regards" & vbNewLine & "The Pricing Team"
.Display