topic Re: Sending Emails using Macro in QlikView

Sending Emails using Macro

Wed, 13 Mar 2013 07:54:44 GMT

Hi guys

I want to send the mail from my company's EmailID to the someone's else company 's Email id using macro .

Right now i can send the mail to all the Employee's of my company using macro, but how to send the mail to someone who is not in my company.

Re: Sending Emails using Macro

vikasgupta — Wed, 13 Mar 2013 09:01:58 GMT

Do you want to send individual mail to any mail id ? please explain more !!

Thanks

Re: Sending Emails using Macro

Wed, 13 Mar 2013 09:09:12 GMT

Yes ....

Re: Sending Emails using Macro

vikasgupta — Wed, 13 Mar 2013 09:15:01 GMT

Try this script !!!

--------------------------------------------------------------------------

SendGMail

ActiveDocument.Save

set objExcel=nothing

set strDate=nothing

set filepath=nothing

End Sub

Sub SendGMail()

MsgBox "Report Exported Successfully"

strDate = Year(Date()) &"-"& month(date()) &"-"& Day(Date())

filepath="D:\Excel_"&strDate&".xls"

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") = 25

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "500vikas@gmail.com"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "**********" 'Type your acccount Password

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

msgConf.Fields.Update

'End With

objMsg.To = "Sender Mail ID"

objMsg.From = "Xyzz@gmail.com"

objMsg.Subject = "Test mail"

objMsg.HTMLBody = "QlikView Test Mail"

'objMsg.AddAttachment "D:\Image.jpg"

objMsg.AddAttachment filepath ' Attachement

objMsg.Sender = "Mr. Name"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

Msgbox("Email Sent Successfully")

' Clear

Set objMsg = nothing

Set msgConf = nothing

End Sub

-----------------------------------------------------------------------------

Just edit your maild and password and reciver mail id .

Thanks

Vikas

Re: Sending Emails using Macro

Wed, 13 Mar 2013 09:56:08 GMT

hi vikas

it is not working ......

Re: Sending Emails using Macro

Wed, 13 Mar 2013 10:02:08 GMT

error is coming on

objMsg.Send

line

Re: Sending Emails using Macro

vikasgupta — Wed, 13 Mar 2013 10:09:36 GMT

Use this working sample first edit the macro i commented where changes required . This applictaion can convert any object in excel and send it by mail .If any error will come please let me know .

Thanks

Vikas

Re: Sending Emails using Macro

Wed, 13 Mar 2013 10:19:02 GMT

hi vikas

the code is working when i am sending to the person within my company.

but when i am sending outside my company (say xyz@othercomapnyname.com) then the module is getting opened and no error message is coming

But when i debuged the macro i came to know that the error is at objMsg.Send

Re: Sending Emails using Macro

Gethyn — Wed, 13 Mar 2013 10:22:46 GMT

Hi Manish,

I use these two macros assigned to a button. It pulls the send to e-mail address for the relevent record selected befor pressing the button.

Sub ExportTable()

Dim strvDBA

Dim strvDate

Set vDBA = ActiveDocument.Fields("Debtor Billing Agency").GetPossibleValues

strvDBA = vDBA.item(i).Text

Set vDate = ActiveDocument.Fields("Transaction Date").GetPossibleValues

strvDate = vDate.item(i).Text

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.DisplayAlerts = False

set chart = ActiveDocument.GetSheetObject ("CH104")

chart.SendToExcel

Set WB = objExcel.ActiveWorkbook

WB.SaveAs "\\belvan\Belvan_Systems\Monthly_Processing\"&strvDBA&" - NCA Backing Data for "&strvDate&".xls"

WB.Close

objExcel.Quit

chart.Minimize

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

End Sub

SUB openMail

Dim objEmail

Dim strMailTo

Dim appname

Dim strvDBA

Dim strvDate

Set vDBA = ActiveDocument.Fields("Debtor Billing Agency").GetPossibleValues

strvDBA = vDBA.item(i).Text

Set vDate = ActiveDocument.Fields("Transaction Date").GetPossibleValues

strvDate = vDate.item(i).Text

Set objSelected = ActiveDocument.Fields("Email contact").GetPossibleValues

Set vOlApp = CreateObject("Outlook.Application")

Set vMessage = vOlApp.CreateItem(olMailItem)

Set myAttachments = vMessage.Attachments

myAttachments.Add "\\belvan\Belvan_Systems\Monthly_Processing\"&strvDBA&" - NCA Backing Data for "&strvDate&".xls"

For i = 0 to objSelected.Count-1

strMailTo = objSelected.item(i).Text

vMessage.Subject = strvDBA&" - Billing Backing Data for "&strvDate

vMessage.To = strMailTo

vMessage.BCC = "xyz@nhs.net"

vMessage.Display

END SUB

Hope this helps,

Gethyn.

Re: Sending Emails using Macro

vikasgupta — Wed, 13 Mar 2013 10:34:30 GMT

Did you configure smtp server port ? sample is working on gmail smtp server configuration so you need to configure your compnay smtp server configuration .

Thanks

vikas

Re: Sending Emails using Macro

vikasmahajan — Sat, 01 Mar 2014 04:53:45 GMT

PFA

Vikas

Re: Sending Emails using Macro

Thu, 22 May 2014 06:04:43 GMT

Hi Manish,

Could u plz share your mail macro code for sending mails to same company id's.

Re: Sending Emails using Macro

Thu, 28 Aug 2014 05:34:29 GMT

Hi ,

I am getting error "The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available " on code line objMsg.Send

Please help me

thanks,

Kapil

Sub SendGMail1()

' 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") = "kapil_dadheech@gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "kapil1"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

msgConf.Fields.Update

' Email
objMsg.To = "kapil_dadheech@gmail.com"
objMsg.From = "kapil_dadheech@gmail.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 Emails using Macro

Thu, 20 Oct 2016 13:37:39 GMT

Hi Vikas,

We used that sending mail but after objMsg.Send no response.

I given mail server settings also.

I need to send a excel sheet attachment in the mail from saved path.

Regards

Naren

Re: Sending Emails using Macro

Anonymous — Wed, 11 Jan 2017 13:37:02 GMT

Hi guys

I'm trying to send email but happens the error "The transport failed to connect to the server."

Anyone could help me?

Re: Sending Emails using Macro

poojask123 — Tue, 31 Jul 2018 07:53:28 GMT

Hi Vikas,

I used the code you shared, but the execution stops at ObjMsg.Send Can you please help?

Thanks