CODEWITHSUNDEEP
excelvbaimageemailoutlook
Francisco J Pérez
Francisco J Pérez

Reputation: 33

Range to IMG is empty when sent

I use this https://stackoverflow.com/a/48897439/14866652 to a range as an image into an Outlook email.

I don't want the screen to display so I changed the .Display to .Send. The mail is sent empty:

Public Sub enviarmail()
    Dim rng As Range
    Dim olApp As Object
    Dim Email As Object
    Dim Sht As Excel.Worksheet
    Dim wdDoc As Word.Document
    
    Set Sht = ActiveWorkbook.Sheets("Cierre de ventas")
    Set rng = Sht.Range("B23:N35")
    rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set Email = olApp.CreateItem(0)
    Set wdDoc = Email.GetInspector.WordEditor
    
    With Email
        .BodyFormat = 2
        .To = Range("D13").value
        .CC = Range("D14").value
        .Subject = Range("D15").value
           
        wdDoc.Range.PasteAndFormat Type:=wdChartPicture
        wdDoc.Range.InsertAfter vbLf & vbLf & "Firmado: " & Range("K13").value
 
        With wdDoc
            .InlineShapes(1).Height = 260
        End With
  
        .Send
            
    End With
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Set Email = Nothing
    Set olApp = Nothing
   
End Sub

Update: after several tests, @urdearboy's Potential Workaround #2 works, no need to use a buffer too, I've tried before without the buffer and I did not got it to work.

Using .Display and .Send at the same time works.

Upvotes: 2

Views: 193

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Potential Workaround #1

I've ran into a similar issue that only occurs when adding objects to body outside of text or a direct file upload. The only way I have been able to solve is to add a time buffer between the file being added and the email being sent.

I know the answer here is not satisfying, but worth a try:

Application.Wait Now + #12:00:01 AM#
.Send

Here is the question I posted with issue. I bountied this and still got no solution so settled with the time delay eventually

Potential Workaround #2

You can also try to first display and then send email. Same as above, this seems like a workaround to the actual problem but may be worth a try. If you are just sending one email then this could be a acceptable route to go. If many, then it may slow down the process and become less ideal.

With Email

    'Email Attributes
    .Display
    .Send

End With

Upvotes: 2

Related Questions