: MM: formato SS dalla cella di Excel al corpo di Outlook Email

Ok Quindi sono bloccato su questo codice Excel VBA. Lo ho automaticamente generando emials in outlook con le informazioni dal foglio già inputed. MA Quando provo a spostare una cella che è formattata come [h]: mm: ss mette una versione decimale del numero nell'email di Outlook.

Sub Mail_test() 'Set variables/objects for code. Dim OutApp As Object Dim OutMail As Object Dim eto As String Dim ecc As String Dim esub As String Dim ebody As String Dim ebody1 As String Dim ebody2 As String Dim ebody3 As String Dim ebody4 As String Dim intbody As String Dim wkDay As String Dim otOff As String Dim intbody2 As String Dim intbody3 As String Dim ebody6 As String Dim intbody4 As String Dim intbody5 As String Dim ebody7 As String Dim Ebody10 As String Dim ebody11 As String Dim Ebody12 As String Dim intbody12 As String Dim intbody13 As String Dim Ebody13 As String Dim ebody15 As String Dim ebody20 As String Dim Ebody21 As String Dim Ebody22 As String Dim Tempefile As String 'Sets application to update with code executions. With Application .ScreenUpdating = True .EnableEvents = False End With 'Conditional to determine if the code should continue. mydate = E3 'Sets default body of the email. Ebody12 = "Hello " intbody12 = Sheets("Emails").Range("b3") ebody15 = "," & vbNewLine & vbNewLine Ebody13 = "We are doing our Bi-Weekly Aux 2 Audit. " & vbNewLine & vbNewLine ebody11 = "This Escalation is for Agent: " Ebody10 = Sheets("Emails").Range("AgentName").Value & vbNewLine & vbNewLine ebody1 = "Your agent was over the allotted time for aux 2 for the two week period :" ebody2 = "Agents are allotted 1.33% of their staffed time. " & vbNewLine & vbNewLine ebody3 = "Your agents staffed time was : " intbody = Sheets("Emails").Range("stafftime") & vbNewLine & vbNewLine ebody4 = "Your Agents Aux 2 percentage for the last two weeks was : " intbody2 = Sheets("Emails").Range("auxper").Value ebody5 = "????" intbody3 = Sheets("Emails").Range("F3").Value ebody6 = " Can we please have this coached? " & vbNewLine & vbNewLine intbody4 = Sheets("Emails").Range("g3").Value & vbNewLine & vbNewLine ebody7 = "Your agents Aux 2 time was :" intbody5 = Sheets("Emails").Range("Auxhours").Value & vbNewLine & vbNewLine intbody13 = Sheets("Emails").Range("I3").Value & vbNewLine & vbNewLine ebody20 = "Thank you," Ebody21 = " - " Ebody22 = "%" & vbNewLine & vbNewLine ' Begins loop for adherence entry, loops until no further records exist to enter. 'Resets variables for body of the email. intbody = "" ebody = "" 'Conditional, if agent doesn't have overtime for tomorrow, skips to next agent.. 'Continues to reset variables for email communication eto = Sheets("Emails").Range("Supname") & ";" & Sheets("Emails").Range("managerName") 'sets the value in email data tab for To field ecc = "[email protected]" 'sets the value in email data tab for CC field esub = "Aux 2 Escalation" 'sets the value in email data tab for Subject field intbody = Sheets("Emails").Range("d3").Value & vbNewLine & vbNewLine ebody = Ebody12 + intbody12 + ebody15 + ebody11 + Ebody10 + Ebody13 + ebody + ebody1 + intbody3 + inbody + Ebody21 + intbody4 + ebody2 + ebody3 + intbody + ebody7 + intbody5 + ebody4 + intbody2 + Ebody22 + ebody6 + ebody20 'Starts outlook application to send email. Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) 'Sets variables for email. On Error Resume Next With OutMail .To = eto .CC = ecc .BCC = ebcc .Subject = esub .Body = ebody .Importnce = 2 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With ' Unlocks outlook Set OutMail = Nothing Set OutApp = Nothing ' Removes the row and loops back to the beginning to enter a new record. Application.Wait Now + TimeValue("00:00:02") Rows(6).EntireRow.Delete End Sub 

Questi due sono quelli che voglio mostrare [H]: MM: formato SS

 intbody5 = Sheets("Emails").Range("Auxhours").Value & vbNewLine & vbNewLine intbody = Sheets("Emails").Range("stafftime") & vbNewLine & vbNewLine 

ho provato

 tempfile = Sheets("Emails").Range("stafftime") & Format (Time, "[H]:MM:SS") 

e usando il tempfile anziché l'intestazione nella parte del corpo della codifica, ma solo retunrs gli stessi numbers con 00:00:00 alla fine.

Qualsiasi Aiuto potrebbe essere appreicated

Utilizza .text invece di .value

.text lo mette mentre lo visualizzi nella cella

 intbody5 = Sheets("Emails").Range("Auxhours").text & vbNewLine & vbNewLine