Consulting

Results 1 to 7 of 7

Thread: Adding Excel Chart objects to an Outlook mail body

  1. #1
    VBAX Regular
    Joined
    Dec 2016
    Posts
    10
    Location

    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 😊
    Attached Files Attached Files

  2. #2
    VBAX Regular
    Joined
    Dec 2016
    Posts
    10
    Location
    Can someone help me please?

    Thanks in advance!

  3. #3
    VBAX Regular
    Joined
    Dec 2016
    Posts
    10
    Location

    Hi, Can anybody explain why my thread (Sep 4th) is not answered?I need your help ASAP

    Quote Originally Posted by lico13 View Post
    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 😊

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Dec 2016
    Posts
    10
    Location

    I don't understand...

    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.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Please read the rules concerned. There is no 'expiry date' for compliance.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.
    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:
    '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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •