Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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
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!
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.
BI Consultant
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
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.
BI Consultant
I dont know , why its not working and stoppiong at .send.
whether i have to use any proixy settings
Regards
Pramod Kumar
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