Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
louwriet
Creator
Creator

Sending mail with gmail fail at objMsg.Send in macro

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

11 Replies
Not applicable

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

Not applicable

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?