Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Qlikview file and I am writting macro to Automate the report generation. I had done the Automation in the form of PowerPoint Presentation. Now I am looking to paste (embed) multiple QV charts in the Outlook email format. I also want to be able to change the position and size of Images.
I am already able to paste one Chart with some text, but now I am looking for pasting multiple charts and formated Text.
sub CreateReport
Set vOlApp = CreateObject("Outlook.Application")
Set vMessage = vOlApp.CreateItem(olMailItem)
ActiveDocument.Sheets("Density").Activate
set image = ActiveDocument.GetSheetObject("CH01")
ActiveDocument.GetApplication.WaitForIdle
image.CopyBitmapToClipboard
vMessage.GetInspector.WordEditor.Range.Paste
vMessage.Subject = "[MTBF] Report"
vMessage.To = "Ppp@yahoo.com"
vMessage.Display
end sub
I did it!
Sub SendMail_2()
Dim oOutlook 'As Outlook.Application
Dim oEmailItem 'As MailItem
On Error Resume Next
Set oOutlook = GetObject(, "Outlook.Application")
If oOutlook Is Nothing Then Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
.Attachments.Add "C:\Temp\Daily report from Qlikview.pdf"
.To = "email@server.com"
'.BCC = "email@server.com"
.From = "email@server.com"
.Subject = "Email from Qlikview"
.HTMLBody = "The report is updated and is attached."
.send
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
end sub
How would this macro look without adding the signature to the email?
Try using myDoc.Range.Delete to delete the added signature.
Masha,
I was able to paste text and chart, but how do i paste another chart at the end?
Sub Email()
Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.BodyFormat = 2 'Outlook.OlBodyFormat.olFormatRichText
myMessage.Subject = "test email"
myMessage.To = "test@email.com"
Set myInspector = myMessage.GetInspector 'this inserts signature to e-mail
Set myDoc = myInspector.WordEditor
ActiveDocument.GetSheetObject("TX02").CopyTextToClipboard
myText = ActiveDocument.GetSheetObject("TX02").GetText 'this is needed to calculate the lenght of the iserted text to put the next object after it
myDoc.Range(0,0).Paste 'This pastes at the begining of the message body
'chr(13)
ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard true
myDoc.Range(len(myText),len(myText)).Paste 'Pastes after the previous text
'myDoc.Range(myDoc.Characters.Count-1, myDoc.Characters.Count).Paste 'Pastes at the end of the message body //this doesnt work
' myMessage.GetInspector.WordEditor.Range.Paste 'Replaces the entire message body with clipboard content
'myInspector.WordEditor.Content.InsertAfter chr(13)
'THE ABOVE SCRIPT WORKS
'NEXT CHARTS //THIS DOES NOT WORK. WHOLE SCRIPT FAILS WHEN THE BELOW IS ADDED
ActiveDocument.GetSheetObject("CH08").CopyTableToClipboard true
myDoc.Range(myDoc.Characters.Count-1, myDoc.Characters.Count).Paste 'Pastes at the end of the message body
myMessage.Display
Set myMessage = Nothing
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing
End sub
Did you replace CH08 with your own chart id?