View original (French)

Copy a table in Outlook as an image.

Solved
fabkiller78 Posted messages 11 Status Member -  
fabkiller78 Posted messages 11 Status Member -
Bonjour,

After several searches and trials, I am resigned to asking for your help.

I created a macro that updates a daily sales table, which I then copy as an image into the body of an email that also includes text.

I would like to include an image of an Excel table in the body of the email in the macro that creates the email.
Here is my code that works perfectly, but I would like to include an image of an Excel table in the body of the email, but so far without success. 

Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(0)

corps_mail = "Hello, " & vbNewLine & vbNewLine & "Please find below the daily sales for " & Format(JourJ, "dd/mm/yyyy") & "." & vbNewLine & vbNewLine & vbNewLine & "Best regards

On Error Resume Next
With OutMail
 .To = "ventesjour"
 .CC = ""
 .BCC = ""
 .Subject = "Daily sales for " & Format(JourJ, "dd/mm/yyyy")
 .Body = corps_mail
 .Display

End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

As you can see in the code, I create the email with all the info for an automatic send except for the Excel table, which I have to manually paste as an image.

The table image to include is located in the "Daily sales" tab, range ("B2:X53").
The file is necessarily open, and the "Daily sales" tab is already activated.

If anyone has a solution, I am open to it.

Thank you

Configuration: Windows 7 / Chrome 37.0.2062.120

6 answers

  1.
    Normad Posted messages 112 Registration date   Status Member Last intervention   40
     
    Hello,
    A partial solution, add the image copy in the code 

    Range("B2:X53").CopyPicture xlScreen, xlBitmap

    Then use Ctrl V at the desired location in the body of the Outlook email.
    1
  2. fabkiller78 Posted messages 11 Status Member
     
    Hello,

    I would like everything to be automated.
    I tried your solution by integrating a "selection.paste" in the body of the email, but the macro crashes on this line.
    After several searches and attempts, I wonder if it wouldn't be simpler to go through Word or PowerPoint in the macro?
    That is to say, make an image copy to Word and retrieve this image from Word to include it in the body of the email.
    0
  3. Normad Posted messages 112 Registration date   Status Member Last intervention   40
     
    In this case, I think it is necessary to format the message in HTML where we can insert an image by code.
    Great day of kindness, after some trials, here is the adapted code: 


     Dim Img As String, Plage As Range, PathTmp As String
     PathTmp = Environ$("temp") & ""
     Img = "Image.jpg"
     Set Plage = Range("B2:X53")
     
     'Creating an image file in the temporary directory
     Plage.CopyPicture
     With ActiveSheet.ChartObjects.Add(0, 0, Plage.Width, Plage.Height)
     .Activate
     .Chart.Paste
     .Chart.Export PathTmp & Img, "JPG"
     End With
     
     ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Delete

    Set OutApp = CreateObject("outlook.application")
     Set OutMail = OutApp.CreateItem(olMailItem)
     
     With OutMail
     .TO = "ventesjour"
     '.CC = ""
     '.BCC = ""
     .Subject = "Sales day on " & Format(JourJ, "dd/mm/yyyy")
     .Attachments.Add PathTmp & Img, olByValue, 0
     .HTMLBody = "<span LANG=FR><p class=style2>" _
     & "<font FACE=Calibri SIZE=3>Hello,<br><br>" _
     & "Please find below the sales day on " _
     & Format(JourJ, "dd/mm/yyyy") & "<br><br>" _
     & "Best regards<br><br>" _
     & "<img src='cid:" & Img & "'</font></span>"
     
     .Display
     End With
     
     On Error GoTo 0
     Set OutMail = Nothing
     Set OutApp = Nothing
     Kill PathTmp & Img
    0
  4. fabkiller78 Posted messages 11 Status Member
     
    Hello Normad,

    Thank you for this solution, I think we are getting closer to the goal.
    He puts a blank image in the body of the email with the following message:

    "Cannot display the linked image. The file may have been moved, renamed, or deleted. Please check that the link points to the correct file and location."

    I’m trying to modify the code but so far without success.
    0
  6. Normad Posted messages 112 Registration date   Status Member Last intervention   40
     
    Hello,
    At my place, it works perfectly.

    What have you tried to modify?

    Given the error message, I would start by removing the
    last line: 
    Kill PathTmp & Img
    (This line deletes the image created in your temporary folder).

    If it works after the removal, you'll need to delete the file that may already exist at the beginning of the procedure, right after the lines: 

    Dim Img As String, Plage As Range, PathTmp As String
    PathTmp = Environ$("temp") & ""
    Img = "Image.jpg"

    add this line: 
    If Dir(PathTmp & Img) <> "" Then Kill PathTmp & Img

    Otherwise, please provide the complete code of the procedure.
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hello,

      @Normad: The problem is related to a known bug on CCM...
      Your line of code: PathTmp = Environ$("temp") & "\" has been transformed due to the code tags, by the site commentçamarche.net, into PathTmp = Environ$("temp") & ""
      Therefore, it cannot work.

      @fabkiller78: so replace 
      PathTmp = Environ$("temp") & ""

      with 
      PathTmp = Environ$("temp") & "\"
      0
  7. fabkiller78 Posted messages 11 Status Member
     
    A big thank you to both of you
    It was indeed enough to modify the PatchTmp line for it to work
    My problem is solved
    0