Adding Excel Chart objects to an Outlook mail body

Adding Excel Chart objects to an Outlook mail body


Hi friends,


I hope you can help me here… I tried to find solutions for my need but didn’t succeed to implement any of them…


The attached VBA code sends an automated mail through Outlook to few people in a weekly basis, and contains text in the body and attached Excel file. This code was written in ‘ThisOutlookSession’ in Outlook VBA (based on recurrent task created in Outlook) and works perfect!


I would like to add to the code the following action:
Enter the Excel file: C:\folder\file.xlsm (the file that was attached to the mail by VBA)
Go to Sheet A and copy Chart X (chart object name X)
Go to Sheet B and copy Chart Y (chart object name Y)
Go to Sheet C and copy Chart Z (chart object name Z)
Paste these 3 copied charts in the same mail body, one by another, after the text lines “Bla Bla…”.
Above every chart add its relevant header text (Chart X / Y / Z)
At the end of the process, Excel file should be closed with no changes in it.


Please try to help me editing the current code with the needed additions.
I’ll be happy to have your support.


Thanks in advance 😊

:think: Can someone help me please? :think:

Thanks in advance! :yes

Quote:

---

Originally Posted by lico13

Adding Excel Chart objects to an Outlook mail body


Hi friends,


I hope you can help me here… I tried to find solutions for my need but didn’t succeed to implement any of them…


The attached VBA code sends an automated mail through Outlook to few people in a weekly basis, and contains text in the body and attached Excel file. This code was written in ‘ThisOutlookSession’ in Outlook VBA (based on recurrent task created in Outlook) and works perfect!


I would like to add to the code the following action:
Enter the Excel file: C:\folder\file.xlsm (the file that was attached to the mail by VBA)
Go to Sheet A and copy Chart X (chart object name X)
Go to Sheet B and copy Chart Y (chart object name Y)
Go to Sheet C and copy Chart Z (chart object name Z)
Paste these 3 copied charts in the same mail body, one by another, after the text lines “Bla Bla…”.
Above every chart add its relevant header text (Chart X / Y / Z)
At the end of the process, Excel file should be closed with no changes in it.


Please try to help me editing the current code with the needed additions.
I’ll be happy to have your support.


Thanks in advance 😊

---

Cross-posted at: https://www.excelforum.com/excel-pro...mail-body.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

Hi,

I don't understand... I didn't have any intention to act against Forum's policy, but after posting a thread here which is not being answered nor responded for 11 days, what do you expect me to do?

I really needs forum's help for my question...

So I published my thread in other forum with a special notification that it is already posted here with no answer...

Now I'm getting the same answer of cross-posting in the other forum, and can not get answers from both...

This is frustrating... how should I act in such case?

I will appreciate your answer.

Thanks in advance.

Please read the rules concerned. There is no 'expiry date' for compliance.

I don't visit this forum very often. Normally, I integrate Excel with Outlook and Word. Your code could be involved so a quick response can't be expected.

For something like that, rather than htmlBody, I would use Outlook's WordEditor method with Word's Paste feature. I don't have time to fully flesh out a specific example. See the end for a WordEditor code example. It would be easier to help if you had attached a simple example XLSM file with the objects.

Normally, we like to see code pasted between Code tags. Click the # icon in the reply toolbar to insert the tags. e.g.

Code:

---

Private Sub Application_Reminder(ByVal Item As Object)
Dim objPeriodicalMail As MailItem
    If Item.Class = olTask Then
      If InStr(LCase(Item.Subject), "send an email periodically") Then
          Set objPeriodicalMail = Outlook.Application.CreateItem(olMailItem)
          With objPeriodicalMail
              .Subject = "Bla Bla"
              .To = "mail@mail.com"
              .CC = " mail@mail.com "
              .HTMLBody = "<HTML><BODY>" _
                & "Bla Bla" & "<br>" & "<br>" _
                & "Bla Bla"  & "<br>" & "<br>" _
                & "Bla Bla</HTML></BODY>"
              .Attachments.Add ("c:\folder\file.xlsm")
              .Importance = olImportanceHigh
              .ReadReceiptRequested = True
              .Send
          End With
      End If
    End If
End Sub

---

Pasting can be a bit tricky depending on the objects that are copied and pasted to WordEditor. Here is an Excel VBA example that shows the concept:

Code:

---

'https://www.mrexcel.com/forum/excel-questions/1029977-how-can-i-attach-object-file-attached-worksheet-email.htmlSub Main()
  Dim S$, T$, sig$, ole$
  'Tools > References > Microsoft Outlook xx.0 Object Library > OK
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  'Tools > References > Microsoft Word xx.0 Object Library > OK
  Dim Word As Document, wr As Word.Range, rTo As Recipient, wos As Word.Selection
 
  'INPUTS to change if needed...........................................................
  S = "Hello World Example" 'Subject
  T = "ken@gmail.com"      'To
  ole = "oleFile1"          'OLE object's name.
  sig = ThisWorkbook.Path & "\sig.rtf" 'contents to copy for signature.
  'End INPUTS...........................................................................
 
  'Get Outlook application
  Set olApp = New Outlook.Application
     
  'Make email, send/display.
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail 'olApp.CreateItem(olMailItem)
    .Subject = S
    .Importance = olImportanceNormal
   
    'Set the recipient(s) for To field and resolve.
    Set rTo = .Recipients.Add(T)
    rTo.Resolve
    rTo.Type = olTo 'olTo, olcc, olbcc
    If rTo.Resolved = False Then
      Debug.Print T & "email address: Resolved=False"
      '.To = T  'Using rTo above for .To instead.
      GoTo TheEnd
    End If
   
    'Setup WordEditor parts:
    .GetInspector.Display
    Set Word = .GetInspector.WordEditor
    Set wr = Word.Range
    wr.Font.Name = "Arial"
    wr.Font.Size = 18
         
    'Body, introductory text:
    'wr.typetext "..."
    wr.Text = "Dear VBA Enthusiast, " & vbCrLf & vbCrLf & _
          "I hope that you find this example of copied Excel Range " _
          & "and embedded OLEObject using WordEditor in Outlook " _
          & "useful." & String(2, vbCrLf)
         
    'Body, range A1, copy/paste:
    Set wos = Word.Windows(1).Selection
    Sheet1.Range("A1").CopyPicture xlScreen, xlPicture
    wr.Collapse Direction:=wdCollapseEnd
    'Word.Range(End:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
    wr.Paste
   
    wr.Collapse Direction:=wdCollapseEnd
    Word.Range.InsertAfter String(2, vbCrLf)
    'Word.Selection.EndKey Unit:=wdStory
    'Selection.InsertBreak Type:=wdPageBreak
    'word.Selection.Paste
   
    'Body, copy/paste OLEObject
    Word.Range.InsertAfter " " & String(2, vbCrLf)
    wr.Collapse Direction:=wdCollapseEnd
    'Word.Range(Start:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
    Sheet1.OLEObjects(ole).Copy
    olMail.GetInspector.Display
    Sheet1.[A1].Select
    .GetInspector.Display
    wr.PasteSpecial Placement:=wdInLine, DataType:=wdPasteOLEObject


    'Body, copy/paste contents of sig.rtf, signature...
    'Adjust String()'s vbCrLf number to be below the oldObject due to its size.
    wr.InsertParagraphAfter
    wr.InsertBreak Type:=wdSectionBreakNextPage
    wr.InsertAfter String(22, vbCrLf)
    wr.Collapse Direction:=wdCollapseEnd
    'https://msdn.microsoft.com/en-us/vba/word-vba/articles/selection-insertfile-method-word
    wr.InsertFile sig, , , False, False
 
    'Word.Range(Start:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
    'GetObject(sig).Range.Copy
    'wr.PasteSpecial Placement:=wdInLine, DataType:=wdPasteRTF
    'wr.Paste
   
    '.Attachments.Add e 'e is the full path to a file.
    .Display
    '.Send
  End With
 
TheEnd:
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

---