Date Format in Excel File changes upon email in excel VBA Macro

[anshikam]

Hello,

I am emailing an excel file through a Macro VBA.
Successfully able to do so however, the file when emailed successfully has a different date format than the original.
Can anyone help me with this?

Thanks in advance
Anshika

Would like to mention that this site has been very helpful since I started from scratch.
The mentors and members have shared very valuable information allowing me to better my skills.

Thanks !!!!

 

[Joe4]

Can you provide the following?
1. The VBA code you are using.
2. A sample of what is in the Excel file.
3. What the date looks like on your side.
4. What the date looks like on the other side.

 

[anshikam]

The code to email is listed below.
The variable 'Report' right before 'Mail.Send' stores the name of the file being generated earlier in the macro.

Image is attached of the date format in the excel file and upon opening in gmail.

PS: When the file is downloaded from email and opened the format is the original format.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String

strSubject = "Court Case Dates"
strFrom = "response@hospital.com"
strTo = "jack@hospital.com"
strBody = ""
strCc = "sam@hospital.com"

Set CDO_Mail = CreateObject("CDO.Message")

Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1

Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "response@hospital.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*****"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With

With CDO_Mail
Set .Configuration = CDO_Config
End With

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.CC = strCc
CDO_Mail.TextBody = strBody
CDO_Mail.AddAttachment "f:\Corporate\Anshika\Due Dates\sent\" & Report
CDO_Mail.Send

 

[Joe4]

Image is attached of the date format in the excel file and upon opening in gmail.

PS: When the file is downloaded from email and opened the format is the original format.

So it sounds like the issue is how gmail renders the format when the file opened/viewed in gmail.
I think that sounds like a gmail issue, not an Excel one. They may be some settings in gmail that control this.
I am no expert in gmail, so really cannot provide much guidance on that.

 

[anshikam]

So it sounds like the issue is how gmail renders the format when the file opened/viewed in gmail.
I think that sounds like a gmail issue, not an Excel one. They may be some settings in gmail that control this.
I am no expert in gmail, so really cannot provide much guidance on that.

No worries. I gathered the same. Thanks for your response !!!!!

 

[Joe4]

You are welcome.

 

[anshikam]

Joe,

Just figured this is not a google docs issue.
I had to set the format for the column after file gets created.
Doing this I see all my dates in the right format per specification !!!!!

Worksheets("CourtCaseDates").range("c:c").NumberFormat = "dd-mm-yyyy"

 

[Joe4]

Just figured this is not a google docs issue.

Google docs or Gmail?
I thought this was using Gmail with Excel.
Is it using Google Docs with Gmail?

 

[anshikam]

Google docs or Gmail?
I thought this was using Gmail with Excel.
Is it using Google Docs with Gmail?

Hi there !

This is basically a macro running in excel that sends out an email from a google account. The file is basically an attachment and when opened in view mode / google sheets was giving formatting errors. Th actual file created had no such errors. However only when viewed in Gmail …normal view by clicking on the attachment as well as opening in google sheets had formatting errors.

 

[Joe4]

Yes, I have experienced some weird formatting issues when trying to preview Excel files in other email programs too.
I don't really bother trying to do that anymore. I always download and open it directly from Excel.