Re: how to send objects to email using macros - Qlik Community

pgrs4219 · ‎2016-04-20

Hi

Am Been struggling with macros to send objects to emails.the script that i used is here.am using outlook.i don't know what the changes i need to do here please guide me.

Sub ExportEmail

//Export the qlikview sheet as image to predestined location
Set obj = ActiveDocument.ActiveSheet
obj.ExportBitmapToFile "C:\\TestImageJPG.jpg"
MsgBox "Exported"
//Object creation
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")

// Server Configuration
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "senders email address"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password of your account"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
msgConf.Fields.Update
' Email
objMsg.Subject = "Export & Import"
objMsg.To = "receivers email address"
objMsg.From = "senders email address"
objMsg.HTMLBody = "<html><body>Embedded Image:<br><img src=""E:\\TestImageJPG.jpg""></body></html>"
Set objMsg.Configuration = msgConf
' Send
objMsg.Send
Msgbox("Email Sent")
' Clear
Set objMsg = nothing
Set msgConf = nothing
End Sub

Thanks in advance

sujeetsingh · ‎2016-04-21

just check the options available here

QlikView Macros – Useful collection | Lucian Cotea

marcus_sommer · ‎2016-04-21

Have a look here: How to insert an image in outlook box mail body.

- Marcus

Kushal_Chawda · ‎2016-04-21

see this if can help

Welcome to LearnExcelMacro.comMail Chart as Image from Outlook - VBA Code

pgrs4219 · ‎2016-04-21

Hi Marcus,

Thanks for your reply,Here where should i give "object Id" to send that object to email

b mSendMail

Dim objOutlk 'Outlook

Dim objMail 'Email item

Dim strMsg

dim strMailTo

dim sString

dim oldFormat

Dim Rng

Const olMailItem = 0

Const CdoReferenceTypeName = 1

Set objOutlk = CreateObject("Outlook.Application")

Set objMail = objOutlk.createitem(olMailItem)

Set Rng = Nothing

objMail.To = "amit.kumar@teamcomputers.com"

objMail.subject = "OMS DASHBOARD"

ActiveDocument.ActiveSheet.CopyBitmapToClipboard true

ActiveDocument.Sheets("Dashboard-email").ExportBitmapToFile "\\172.16.11.211\Documents\OMS DASHBOARD.jpeg"

objMail.attachments.add("\\172.16.11.211\Documents\OMS DASHBOARD.jpeg")

strMsg = "This is an automatic generated email from QlikView for OMS DASHBOARD."

objMail.body = strMsg

objMail.HTMLBODY = "<HTML><BODY><img align=baseline border=0 hspace=0 src='cid:\\172.16.11.211\Documents\OMS DASHBOARD.jpeg'</BODY></HTML>"

objMail.Send

Set objMail = Nothing

Set objOutlk = Nothing

Msgbox " Mail delivered"

end sub

pgrs4219 · ‎2016-04-21

Hi Marcus,

Am getting Error like this. and i have a doubt can run this in my local personalized version,is it possible?? if not am currently doing it in server side still getting errors. am wondering do i need to put this in access point and check whether its working or not. please reply me am new to Macros.

pgrs4219 · ‎2016-04-21

Hi Kushal,

Thanks for your reply am getting error like this

marcus_sommer · ‎2016-04-22

You need to combine your previous routine with this outlook example and a loop-logic through your fields - here you will find a comprehensive loop-example: VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Valu....

- Marcus

marcus_sommer · ‎2016-04-22

This error-message meant that you need to enable system-access within the modul-security (are the two drop-down fields within the left panel just beside your marekd line). Further must ActiveX be enabled within windows (not blocked from security rules) and of course Outlook must be installed on this machine.

- Marcus

marcus_sommer · ‎2016-04-22

You couldn't simply use a vba-macro within the vbs-environment from qlikview - both languages are very close but not identical. To look for vba-routines for such things is very useful (there are lot more available as for vbs regarding to excel-exports and mailing with outlook) to get ideas - but you need then to adapt and to translate them.

- Marcus