Hi, I have a VBA which takes the current worksheet and emails it out (code shown below). However in the new file it creates and attaches to the email, the colours are distorted and not the same as the original worksheet. Does anybody know how to prevent this from happening.
Many thanks
Many thanks
Code:
Sub Email_Button()
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String
'Turn off screen updating
Application.ScreenUpdating = False
'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook
'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs Filename:=LFileName
'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
.Display
End With
Signature = oMail.body
With oMail
.To = ""
.Subject = "Measurement Report - " & Format(Date, "dd/mm/yyyy")
.Attachments.Add LWorkbook.FullName
.body = "Hi All," & vbNewLine & vbNewLine & _
"Please see attached measurement report for " & Format(Date, "dd/mm/yyyy.") & vbNewLine & vbNewLine & _
"Kind Regards," & Signature
.Display
End With
'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False
'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
