topic Re: Send Chart in body using CDO.Message in QlikView

Send Chart in body using CDO.Message

Wed, 11 Dec 2013 21:10:10 GMT

Hi All,

I am trying to automate sending a daily email using vbscript that contains one chart in the body of the email. I have it working great locally where I can use Outlook by using:

sub DailyMailLocal
  Set myOutlook = CreateObject ("Outlook.Application")
  Set myMessage = myOutlook.CreateItem(olMailItem) 

  myMessage.BodyFormat = 2 'HTMLFormat
  myMessage.To = "testemailaddress@host.com"
  myMessage.Subject = "Qlikview Email Testing"
  Set myInspector = myMessage.GetInspector
  Set myDoc = myInspector.WordEditor
  myInspector.WordEditor.Content.InsertBefore chr(13)
  ActiveDocument.GetSheetObject("CH154").CopyTableToClipboard TRUE
  myDoc.Range(1,1).Paste
  myInspector.WordEditor.Content.InsertAfter chr(13) 
  myMessage.Display

  Set myMessage = Nothing
  Set myApp = Nothing
  Set myInspector = Nothing
  Set myDoc = Nothing
end sub

How can this be recreated using CDO.Message? I have the below working where the excel file is attached to the message. But what I require is the above result with the chart as HTML body in the email.

Sub DailyMailServer

Set obj = ActiveDocument.GetSheetObject("CH154")

obj.ExportEx "C:\Users\vercillo\Documents\Temp.htm",5

Set myMail = CreateObject("CDO.Message")

myMail.Subject = "QlikView Email Testing"

myMail.From = "testemailaddress@host.com"

myMail.To = "testemailaddress@host.com"

myMail.AddRelatedBodyPart "C:\Users\vercillo\Documents\Temp.xls", "Temp.xls", 0

myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing")= 2

myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0

myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mailhost.test.com"

myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

myMail.Configuration.Fields.Update

myMail.Send

set myMail = nothing

set obj = nothing

End Sub

Any help is greatly appreciated, and I hope these working code snips might help someone!

Re: Send Chart in body using CDO.Message

gainkarthi — Tue, 04 Mar 2014 01:34:07 GMT

You got any improvement in this?

Re: Send Chart in body using CDO.Message

Thu, 01 May 2014 18:12:31 GMT

No, I ended up going with the less than ideal file as an attachment option.

Re: Send Chart in body using CDO.Message

vikasmahajan — Fri, 02 May 2014 07:12:54 GMT

Hii,

set cs = ActiveDocument.GetSheetObject("CH663")

cs.ExportHtml "d:\tmp\sales.html"

objEmail.CreateMHTMLBody "file://d:\tmp\sales.html"

Should work for you.

Vikas

Re: Send Chart in body using CDO.Message

Wed, 17 Sep 2014 15:43:47 GMT

Thank you Vikas, sorry for the delayed response... but this worked beautifully!

Re: Send Chart in body using CDO.Message

vikasmahajan — Thu, 18 Sep 2014 03:56:42 GMT

No Problem ,WelCome

Vikas

Re: Send Chart in body using CDO.Message

nesawant — Tue, 23 May 2017 13:35:11 GMT

Hi Vikas,

How do I do this for multiple objects?

So my requirement is to send multiple charts in email body. I even tried copying sheet with all these charts in mail body but that doesn't work. Any idea?

Best Regards,

Neha

Re: Send Chart in body using CDO.Message

vikasmahajan — Wed, 24 May 2017 04:40:18 GMT

you embedded the charts in message body if 2 or 3 but it is advised that create a pdf and attached with message send it to user.

Vikas