Suggestion regarding macro

Fri, 05 Feb 2016 11:09:56 GMT

Sub SelectNext

ActiveDocument.Fields("Dealer").Clear

ActiveDocument.Fields("Area Manager").Clear

ActiveDocument.Fields("Zonal Head").Clear

Set myMail=CreateObject("CDO.Message")

Set filesys = CreateObject("Scripting.FileSystemObject")

set valD=ActiveDocument.Fields("Dealer").GetPossibleValues()

for i=0 to valD.Count-1

ValueToSelect = valD.Item(i).Text

ActiveDocument.Fields("Dealer").Select ValueToSelect

msgbox(i+1)

'myMail.AddAttachment "E:\Akhilesh\TestImg" & i & ".jpg"

'myMail.AddAttachment "E:\Akhilesh\TestImg.jpg"

ActiveDocument.GetApplication.WaitForIdle

'Set filesys = CreateObject("Scripting.FileSystemObject")

'filesys.DeleteFile "E:\Akhilesh\TestImg.jpg"

set ValM=ActiveDocument.Fields("Area Manager").GetPossibleValues()

for k=0 to ValM.Count-1

ValueToSelect1 = ValM.Item(k).Text

ActiveDocument.Fields("Area Manager").Select ValueToSelect1

set ValH=ActiveDocument.Fields("Zonal Head").GetPossibleValues()

for m=0 to ValH.Count-1

ValueToSelect2 = ValH.Item(m).Text

ActiveDocument.Fields("Zonal Head").Select ValueToSelect2

Set obj = ActiveDocument.ActiveSheet

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile "E:\Akhilesh\TestImg" & i & ".jpg"

'obj.ExportBitmapToFile "E:\Akhilesh\TestImg.jpg"

ActiveDocument.GetApplication.WaitForIdle

myMail.To=ValueToSelect

myMail.From = "123@Gmail.com" 'Email Sender

myMail.Cc =ValueToSelect1'&";"&ValueToSelect2 'CC person

myMail.Subject = "Automated Mail Sending Using Macro"

myMail.AddAttachment "E:\Akhilesh\TestImg" & i & ".jpg"

ServerIP="2.1.2.4"

ServerPort="2"

'Assigning Name or IP of remote SMTP server

myMail.Configuration.Fields.Item _

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

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "2"

'Assigning Server port

myMail.Configuration.Fields.Item _

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

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = false

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = "60"

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "2"

' Set objimg = myMail.AddRelatedBodyPart( vImagePath & "TestImg.jpg", "TestImg.jpg", CdoReferenceTypeName)

' objimg.Fields.Item("urn:schemas:mailheader:Content-ID") = "<TestImg.jpg>"

' myMail.HTMLBody = HTML

'Updating Fields

myMail.Configuration.Fields.Update

'Sending Mail

myMail.Send

'filesys.DeleteFile "E:\Akhilesh\TestImg" & i & ".jpg"

ActiveDocument.GetApplication.WaitForIdle

set myMail=Nothing

set ServerIP=Nothing

Set ServerPort= Nothing

end Sub

it's is working fine but first dealer is getting only its own data but second dealer is getting data of both (of first dealer and own) and so on

so it means last dealer is getting data of all the dealer. But my requirement is that every dealer should get their own data only.

Re: Suggestion regarding macro

marcus_sommer — Sat, 06 Feb 2016 14:43:02 GMT

It's not quite clear but I think you should consider to change the selections-order from Dealer to Area Manager to Zonal Head to another and/or to put clear-statements before the end your loops. If you comment out the whole export- and mail-statements and looked on the gui what will be selected (you will need additional sleep-statements for this otherwise it will be too fast) or a msgbox like:

msg = "first loop: " & i & " - selected value: " & ValueToSelect & chr(10)

msg = msg & "second loop: " & k & " - selected value: " & ValueToSelect1 & chr(10)

msg = msg & "third loop: " & m & " - selected value: " & ValueToSelect2

msgbox msg

- Marcus

topic Re: Suggestion regarding macro in QlikView

Suggestion regarding macro

Re: Suggestion regarding macro