Forum Discussion
Tamati_W
Jan 27, 2023Copper Contributor
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 i want it to display as per the formatted cells in my workbook, B2, C2 & E2 are all formatted as DayName, DayNum, MonthName, YearNum. and cell F2 is just MonthName. is there a special line of code that I need to use in order to make the email show the exact same formatted data from workbook?
.body code is below S = A2 in Workbook
.Body = "Hi All" & vbNewLine & vbNewLine _
& "End of month will be " & S.Offset(0, 2) & ", Please have all requests for contract pricing submitted and approved via Jotform before " _
& S.Offset(0, 3) & " " & S.Offset(0, 2) & ", any request received between then and the " & S.Offset(0, 4) & " will be actioned within the first 3 business days of " _
& S.Offset(0, 5) & " 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 implemented before " & S.Offset(0, 3) & " " & S.Offset(0, 2) _
& vbNewLine & vbNewLine & "Regards" & vbNewLine & "The Pricing Team"
.Display
the script works however it displays email as follows
Hi All
End of month will be 27/01/2023, Please have all requests for contract pricing submitted and approved via Jotform before 1PM 27/01/2023, any request received between then and the 1/02/2023 will be actioned within the first 3 business days of 1/02/2023 with any cost increase applied.
Please also review the attached workbook making sure to view both FIXED CONTRACTS and NATIONAL tabs, both show each contracts expiry date
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 implemented before 1PM 27/01/2023
Regards
The Pricing Team
1 Reply
Sort By
- Tamati_WCopper ContributorAfter 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