Sending mail with gmail fail at objMsg.Send in macro

[louwriet]

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

[wandapec]

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?

[louwriet]

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

[louwriet]

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


[#5]

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!

[Miguel_Angel_Baeyens]

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

[#7]

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

[Miguel_Angel_Baeyens]

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

[#9]

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

whether i have to use any proixy settings

Regards

Pramod Kumar

[Miguel_Angel_Baeyens]

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