topic Re: Sending Email in QlikView

Sending Email

Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I Have a problem in Sending Email through Macro.

earlier it was working fine but recently it is showing error.

When I run the macro it stoping at

objEmail.Send

Error Message :

The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available

Please help me on this issue.

Thanks,

Re: Sending Email

bindu_apte — Wed, 22 Jun 2016 06:47:38 GMT

Hi,

Can you please share the macro code which you are executing

Re: Sending Email

Wed, 22 Jun 2016 07:12:48 GMT

Hi Bindu,

Please check below code

sub Export

'dim excelFile

'Set the path where the excel will be saved

path= "C:\Users\Desktop\Report"

filePath = path &".xls"

'Create the Excel spreadsheet

set oXL=CreateObject("Excel.Application")

oXL.visible=False

Set curWorkBook= oXL.Workbooks.Add

aSheetObj=Array("CH52")

usedRows=0

oXL.Sheets.Add

Set oSH = oXL.ActiveSheet

oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(0))

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

set obj=Nothing

oSH.Rows("1:1").Select

oXL.Selection.Font.Bold = True

oSH.Cells(1,1).Select

oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select

oSH.Name=left(sCaption,30)

set oSH=Nothing

oXL.Visible = False

oXL.DisplayAlerts = False

'Save the file and quit excel

curWorkBook.SaveAs filePath

curWorkBook.Close

oXL.Quit

'set oXL=Nothing

Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP

Const cdoBasicAuth = 1 ' Clear-text authentication

Const cdoTimeout = 60 ' Timeout for SMTP in seconds

mailServer = "Server IP"

SMTPport =25

mailusername = "Mail ID"

'mailpassword = ""

mailto = "To Mail ID list"

mailSubject = "Test mail to test macro"

body = "<html><body> Dear All, <br> <br>Pl find attached<br> </body></html>"

Set objEmail = CreateObject("CDO.Message")

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth

'.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername

.Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = mailusername

'.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword

'.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

.Update

End With

objEmail.To = mailto

objEmail.From = mailusername

'objEmail.CC =

objEmail.Subject = " Report."

objEmail.HTMLBody = body

objEmail.AddAttachment filePath

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

Set curWorkBook = nothing

Set oXL= nothing

ActiveDocument.Save

ActiveDocument.GetApplication.Quit

End Sub

Re: Sending Email

marcus_malinow — Wed, 22 Jun 2016 09:27:25 GMT

Surender,

a couple of things to check:

Check whether the SMTP server and service are actually running. Has the service been moved?

Check if permissions have been changed on it. Was it previously set to allow anonymous access? Does the QlikView service account have permissions on it? (Assuming that you are running this from your QlikView server)

Marcus

Re: Sending Email

Wed, 22 Jun 2016 09:38:52 GMT

Thanks Marcus,

I Have checked SMTP Server and Services it working fine. Access also we are given anonymous access.

Yes Qlikview service have permission.

Through Qlikview Publisher mails are generating fine but through macro there is a problem.

Regards

Surender

Re: Sending Email

syukyo_zhu — Wed, 22 Jun 2016 09:58:48 GMT

Hi,

Did you check your recepient is always avaiable et not be deleted?

hope helpful

Re: Sending Email

sampada0810 — Wed, 22 Jun 2016 10:22:22 GMT

This can be even achieved by using NPrinting

Re: Sending Email

Thu, 23 Jun 2016 03:47:22 GMT

yes Have checked with one mail ID also.

Re: Sending Email

Thu, 23 Jun 2016 03:48:26 GMT

My Company is not ready to buy Nprinting now.

through Macro also we can send the Excel files.

Re: Sending Email

marcus_malinow — Thu, 23 Jun 2016 09:19:01 GMT

Hi Surender,

have you seen this - smtp - Getting Error Transport error code was 0x80040217 while Sending Email in Asp.Net - Stack Overflow

One possible cause might be that your email account has been locked.

Marcus