topic Sending mail with gmail fail at objMsg.Send in macro in QlikView

Sending mail with gmail fail at objMsg.Send in macro

louwriet — Wed, 06 Jul 2011 13:46:43 GMT

Hi ,

I have a macro to send mail through gmail that fails. On my work network through our mail server it work perfectly. I have tried with switching off may firewall it still doesnt work. My module security is set to SYSTEM ACCESS and local security set to ALLOW SYSTEM ACCESS as suggested in other discussions.

Does anyone have an idea what else can cause sending mail through gmail to fail ?? I am so close but not there yet.

See my macro below.

Below is links to threads that explain how to do this automation and i am gratefull for their contribution.

http://community.qlik.com/message/25067#25067

http://community.qlik.com/message/89915#89915

thank you Louw

sub EmailReports

'*********** create string of destination emails as defined in xls

set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Open("C:\dir\AddressMaster1.xls")
set objWorksheet = objExcel.ActiveWorkBook.WorkSheets("Sheet1")

strTo = ""
intRow = 2 ' row 1 = header

Do Until objExcel.Cells(intRow,3).Value = ""
strTo = strTo & objExcel.Cells(intRow, 3).Value & ";"
intRow = intRow + 1
Loop

'Msgbox strTo

objExcel.Quit

' Object creation
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")

' Server Configuration
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "louwrie.terblanche@gmail.com"
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "alfie68791"
objMsg.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
objMsg.Fields.Update

msgConf.Fields.Update

'*********** create email and send
objMsg.To = strTo
objMsg.From = "*****.*******@gmail.com"
objMsg.Subject = "Test send with Gmail account"
objMsg.TextBody = "***! attached find your new report !***"_
& vbCRLF _
& vbCRLF & "8888888888888888888888888888888888:"_
& vbCRLF _
& vbCRLF & " 88888888888888"_
& vbCRLF & " 88888888888888888888888888888888" _
& vbCRLF _
& vbCRLF & "88888888888888888888"_
& vbCRLF _
& vbCRLF & "888888888888888888888"
objMsg.Sender = "8888888"

objMsg.AddAttachment "C:\dir\myreport.pdf"

Set objMsg.Configuration = msgConf

' Send
objMsg.Send

' Clear
Set objMsg = nothing
Set msgConf = nothing

'*********** log action

strLog = "C:\dir\Distribution log.txt"

set fso = CreateObject("Scripting.FileSystemObject")

on error resume next

set logFile = fso.GetFile(strLog)

if err <> 0 then ' file does not exist
on error goto 0
set logFile = fso.CreateTextFile(strLog)
set logFile = fso.GetFile(strLog)
else
on error goto 0
end if

set txsStream = logFile.OpenAsTextStream(8) ' append
txsStream.WriteLine Now & " Service Pricing emailed to " & strTo

txsStream.Close

'*********** tidy up and release memory

''set objMessage = Nothing
set objExcel = Nothing
set FSO = Nothing

End sub

Sending mail with gmail fail at objMsg.Send in macro

wandapec — Mon, 11 Jul 2011 11:23:00 GMT

Hi Louwrie,

Did you get an answer or solution for this? I used to use this script. Now I it is stopping at the objMsg.Send command?

Sending mail with gmail fail at objMsg.Send in macro

louwriet — Mon, 11 Jul 2011 14:44:23 GMT

Hi Wandapec,

No i have not find a solution yet, hopefully somebody has encounter the same problem as us. Plse let me know if you found the solution. I need to demo this to client in a few days, getting a bit nervious at this stage

Regards

Louwrie

Sending mail with gmail fail at objMsg.Send in macro

louwriet — Mon, 11 Jul 2011 15:28:21 GMT

Hi Wandapec,

Got this working. Have a look here http://community.qlik.com/message/25067#25067. Just tested it and its working.

Sub SendGMail() ' 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") = 465 msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "user@gmail.com" msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1 msgConf.Fields.Update ' Email objMsg.To = "user@gmail.com" objMsg.From = "fromuser@domain.com" objMsg.Subject = "Test send with Gmail account" objMsg.HTMLBody = "HTML/Plain text message." objMsg.Sender = "Mr. Name" Set objMsg.Configuration = msgConf ' Send objMsg.Send ' Clear Set objMsg = nothing Set msgConf = nothing End Sub

Re: Sending mail with gmail fail at objMsg.Send in macro

Fri, 05 Aug 2011 10:02:18 GMT

Thanks a lot! louwrie1 . It's works fine, very useful!:D

Please an other information, it's possible for you add some line to show how is possible to send same attachments (like qlikview object)?

Thanks!

Re: Sending mail with gmail fail at objMsg.Send in macro

Miguel_Angel_Baeyens — Fri, 05 Aug 2011 10:23:48 GMT

Hi Igor,

Just add a new line (or as many as you need) under the "Email" section (just below the objMsg.Sender) with the following

objMsg.AddAttachment "C:\Folder\File.qvw"

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Sending mail with gmail fail at objMsg.Send in macro

Fri, 05 Aug 2011 10:54:58 GMT

Thanks Miguel, but it is not what I meant

( sorry for my english, i'm studing it. I know is not so good)

I'm searching for same like this ( send object and not file)

objMsg.AddAttachment ActiveDocument.GetSheetObject("LB03")

but it return me all times an error of wrong type...

Thanks for any help

Re: Sending mail with gmail fail at objMsg.Send in macro

Miguel_Angel_Baeyens — Fri, 05 Aug 2011 11:11:21 GMT

Yes, no QlikView object as such can be sent in an email. Prior to that you need to export the object to a file (xls, csv, jpg). There are a lot of macro examples to do that, but you can check this one, that is very recent.

Once you have exported the chart to a file, you can attach this file to the email and send it.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Sending mail with gmail fail at objMsg.Send in macro

Mon, 23 Jan 2012 09:26:17 GMT

I dont know , why its not working and stoppiong at .send.

whether i have to use any proixy settings

Regards

Pramod Kumar

Re: Sending mail with gmail fail at objMsg.Send in macro

Miguel_Angel_Baeyens — Mon, 23 Jan 2012 10:02:50 GMT

Hi Pramod Kumar,

Can you please elaborate (in a new thread with your specific issue) what code are you using and what are the specifications of your environment (version of QlikView, if mail addresses are local or external, if you are using a smtp server...)

Regards.

Miguel

Re: Sending mail with gmail fail at objMsg.Send in macro

Mon, 23 Jan 2012 10:10:48 GMT

Dear Miguel,

I am using Qlikview 8.5 and tried with your code for sending mail from gmail using my credentials.

Process of mailing is terminated at .send.

I think , Most probably its not working because of Proxy Settings as your code is tested and approved by many user.

Is there any way to declare proxy settings in qlikview

Regards

Pramod Kumar

Re: Sending mail with gmail fail at objMsg.Send in macro

Mon, 12 Mar 2012 03:03:21 GMT

hi Miguel

i follow this code, but i got a wrong AddAttachment with mail ...

send a jpg file, but not same view i got, file size not change, but its empty, was file damage? why?