Colour Distortion when Macro Run

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
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

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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Re: Colour Distorion when Macro Run

When you say distorted, do you mean the RGB is different?
 

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
Re: Colour Distorion when Macro Run

Thanks for you reply. Yes I assume so. The green for example turns to grey and red to orange.
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Re: Colour Distorion when Macro Run

Could you check in format cells > fill > more colours > custom and confirm the RGB is different in each sheet?
 

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
Re: Colour Distorion when Macro Run

I've just checked and they have defientey changed. The green for example has gone from R:118,G:147,B:60 to grey- R:123,G:123,B:123.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,578
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top