title: How can I keep the original format of a cell in an Excel macro?
body: <p>I'm new in Excel VBA and I have managed to create a simple macro to automate some e-mails that carry a customized report for people at my work. In this case I'm a using a worksheet that changes depending on the information added to the file in general. The macro uses some of this info and creates a customized e-mail. Some of this info are time and percentage values, and in the worksheet the cells keep that formats. For example, if the person has worked 3 hours in a specific task it will show the cell as "3:00". But when creating the variables and using them to create the e-mail, the values are shown in their numeric counterpart. </p>
  
  <blockquote>
    <p>EXAMPLE EMAIL</p>
    
    <p>This is the 0.666666666666667 report! The idea of this automated
    e-mail is to help you fulfill your daily target expectations. Feel
    free to contact us if you think something can be done better! </p>
    
    <p>Currently you are sitting in:  •  00:00 of Rating Time •  00:00 of
    Quality Time •    2.79282407407407E-02 of Not Rating Time Since now it is
    0.666666666666667, you should have 0.3125, so you are missing 0.284571759259259. Also, your daily completion target is now at 0.133333333333333. PD: you have 00:00 so you are perfect!.</p>
  </blockquote>
  
  <p>I would like to show this values in the specific format that I need, which is the original format of the cells also. I leave you the code, sorry if there are rookie mistakes, I'm new to this. Thanks in advance.</p>
  
  <pre><code>Sub SendEmail(what_address As String, subject_line As String, email_body As String)
  
  Dim olApp As Outlook.Application
  Set olApp = CreateObject("Outlook.Application")
  
      Dim olMail As Outlook.MailItem
      Set olMail = olApp.CreateItem(olMailItem)
  
      olMail.To = what_address
      olMail.Subject = subject_line
      olMail.BodyFormat = olFormatHTML
      olMail.HTMLBody = email_body
      olMail.Send
  
  End Sub
  Sub SendMassEmail()
  
  row_number = 1
  
  Do
  DoEvents
  
      'Adding 1 to the counter
  
      row_number = row_number + 1
  
      'Creating the variables
  
      Dim mail_body_message As String
      Dim full_name As String
      Dim rating As String
      Dim quality_review As String
      Dim not_rating_time As String
      Dim inactive As String
      Dim absolute_target As String
      Dim missing As String
      Dim inactive_comment As String
      Dim expected_time As String
      Dim hour As String
      Dim recipient As String
  
      'Define the non-moving elements
  
      mail_body_message = Sheet10.Range("J2")
      hour = Sheet10.Range("J8")
      expected_time = Sheet10.Range("J11")
      recipient = Sheet10.Range("B" &amp; row_number) &amp; "@accenture.com"
  
      'Define the moving elements
  
      full_name = Sheet10.Range("A" &amp; row_number)
      rating = Sheet10.Range("C" &amp; row_number)
      quality_review = Sheet10.Range("D" &amp; row_number)
      not_rating_time = Sheet10.Range("E" &amp; row_number)
      inactive = Sheet10.Range("F" &amp; row_number)
      absolute_target = Sheet10.Range("G" &amp; row_number)
      missing = Sheet10.Range("H" &amp; row_number)
      inactive_comment = Sheet10.Range("I" &amp; row_number)
  
      'Replacing elements in the email body
  
      mail_body_message = Replace(mail_body_message, "replace_name", full_name)
      mail_body_message = Replace(mail_body_message, "replace_hour", hour)
      mail_body_message = Replace(mail_body_message, "replace_rating", rating)
      mail_body_message = Replace(mail_body_message, "replace_quality", quality_review)
      mail_body_message = Replace(mail_body_message, "replace_nrt", not_rating_time)
      mail_body_message = Replace(mail_body_message, "replace_expected", expected_time)
      mail_body_message = Replace(mail_body_message, "replace_missing", missing)
      mail_body_message = Replace(mail_body_message, "replace_target", absolute_target)
      mail_body_message = Replace(mail_body_message, "replace_inactive", inactive)
      mail_body_message = Replace(mail_body_message, "replace_inacomment", inactive_comment)
  
  
      Call SendEmail(recipient, "DO NOT RESPOND - TEST EMAIL", mail_body_message)
  
  Loop Until row_number = Sheet10.Range("J5")
  
  MsgBox "The e-mails have been sent!"
  
  End Sub
  </code></pre>