Adding Excel Chart objects to an Outlook mail body

[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 😊

[lico13]

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

Thanks in advance! :yes

[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 😊

[macropod]

Cross-posted at: https://www.excelforum.com/excel-programming-vba-macros/1245613-adding-excel-chart-objects-to-an-outlook-mail-body.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[lico13]

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.

[macropod]

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

[Kenneth Hobs]

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