0

I am getting a paste error when trying to copy paste Excel chart as picture into the email body.

The code executes, taking data from previous day's file updating it and saving it in today's report. The error comes when it tries to copy paste Excel chart in the email body.

Function copy_to_template(template_path As String, temp_file_name As String, _
          sheet_name As String, start_range As String, end_range As String, _
          destin_start_range As String)
    
    'Dim rows_diff As Long
    'Dim end_col As String
    'Dim destin_end_range As String
    
    'rows_diff = Mid(end_range, 2, Len(end_range) - 1) - Mid(start_range, 2, Len(start_range) - 1)
    'end_col = Left(end_range, 1)
    'destin_end_range = end_col & Mid(destin_start_range, 2, Len(destin_start_range) - 1) + rows_diff
    
    xlApp.Workbooks.Open template_path
    Windows(temp_file_name).Activate
    
    Sheets("Sheet1").Select
    

    'If A101ImageCheck(Range(destin_start_range)) Then
        'MsgBox "Image exists!"
    'End If
    
    'Range(destin_start_range & ":" & destin_end_range).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.ClearContents
    Range(destin_start_range).Select
    
    Workbooks.Open new_file, ReadOnly:=True
    Windows(new_file_name).Activate
    Sheets(sheet_name).Select
    
    Range(start_range & ":" & end_range).Copy
    
    Windows(temp_file_name).Activate
    Sheets("sheet1").Select
    Range(destin_start_range).Select
    ActiveSheet.Pictures.Paste.Select
    Application.CutCopyMode = False
    'ActiveWorkbook.Close savechanges:=True
        
    Windows(new_file_name).Activate
    ActiveWorkbook.Close savechanges:=False
        
End Function
Community
  • 1
  • 1
Mudit Arora
  • 1
  • 1
    Side note: this `Function` really should be a `Sub`. – BigBen Aug 18 '21 at 18:20
  • 1
    Remove the `.Select` from `ActiveSheet.Pictures.Paste.Select`. – BigBen Aug 18 '21 at 18:20
  • Not seeing anything email-related here? – Tim Williams Aug 18 '21 at 18:45
  • @BigBen - I tried removing .select from activesheet.pictures.select , didnt make any difference. – Mudit Arora Aug 18 '21 at 19:04
  • @TimWilliams - I have only posted the part where it copy pastes the excel chart. any suggestions? – Mudit Arora Aug 18 '21 at 19:05
  • You said “ the error comes only when it tries to copy paste excel chart in the email body.” I don’t see that? – Tim Williams Aug 18 '21 at 21:02
  • @TimWilliams - my bad , there is another excel template to which the excel chart copy pastes and that's the time where it's giving error. When i try to debug it , it highlights the line " ActiveSheet.Pictures.Paste.Select". I tried removing the .select in the end but no change. Also when I debug it, I am able to run it after I click the play button manually but the copy paste should happen on its own without me doing anything manually. – Mudit Arora Aug 18 '21 at 21:32
  • See this previous post: https://stackoverflow.com/questions/67172010/vba-insert-an-image-multiple-times-without-copy-paste/67173303#67173303 I posted a response with a Sub which you can try out: pasting pictures seems to be a bit buggy in recent Excel versions, and just retrying a couple of times usually gets around the problem. – Tim Williams Aug 18 '21 at 23:12
  • Also try using `Range.CopyPicture` instead of `Range.Copy` – Tim Williams Aug 18 '21 at 23:17
  • @TimWilliams - It worked when i changed it to Range.CopyPicture. Thanks!! – Mudit Arora Aug 19 '21 at 03:24

0 Answers0