Monday, May 12, 2014

Send multiple mails using MS Excel

Option explicit
Dim oex, owb, ows, u, p, i, noms, x
'go to excel software
        Set oex=createobject("Excel.Application")
        oex.Visible=true
'go to excel file
        Set owb=oex.Workbooks.Open("F:\Book1.xls")
'goto work sheet
        Set ows=owb.Worksheets("Sheet1")
'count no mails to be send
        noms=ows.usedrange.rows.count
'Associate functions file
        executefile("F:\QTP\yahoo.vbs")
'launch browser
        launch
'login
        u="XXXXXXXX"
        p="XXXXXXXX"
        login u,p
'continue loop to all the mail ids in the excel list
        For i=2 to noms  step 1
                popup 'function to handle unexpected popups
                'click compose
                         With browser("title:=.*")
                With .page("title:=.*")
        .webbutton("name:=Compose","index:=0").click
                 End With
                        End With
                       'fill fields to send mail
          popup 'function to handle unexpected popups
        With browser("title:=.*")
                        With .page("title:=.*")
                         .webedit("name:=to").set ows.cells(i,1)   'fill to address
                         .webedit("name:=cc").Set ows.cells(i,2) 'fill cc address field
                         .webedit("name:=bcc").Set ows.cells(i,3)     'fill bcc address field
                         .webedit("name:=Content").Set ows.cells(i,4)  'fill body of mail
                                .webbutton("name:=Send").Click
                         End With
                    End With
'check correctness of mail sending
With browser("title:=.*")
With .page("title:=.*")
x=.webelement("class:=emails").GetROProperty ("innertext")
If trim(x)="Your email was successfully sent." Then
ows.cells(i,6)="Test Passed"
else
ows.cells(i,6)="Test Failed"
End If
.link("name:=Back to Inbox").Click
End With
End With
Next
   logout 'function to signout
'save Excel file
owb.Save
oex.Quit
'Destroy objects
Set ows=nothing
Set owb=nothing
Set oex=nothing

No comments:

Post a Comment