(Not so) Simple VBA Loop

[Richard1982]

Hello,

Really hoping someone can help me because I'm puling out my hair trying to solve this and getting no where fast.
I have a PivotTable and one of the filters is for 'Vendor Name'. I can select a vendor then click a button I made and it fires them off an e-mail full of details which have been extracted from the PivotTable. So far so good... Then I thought to myself that since I pretty much e-mail these people all at the same time (once a month, 30 vendors), I should make a loop function so I can click the button once and it e-mails everyone, by going through the 'Vendor Name' list one by one.

So I came up with this code:

Sub Click()
Dim pT As PivotTable
Dim pF As PivotField
Dim pi As PivotItem
Dim i As Long 'This is the loop counter

Workbook.RefreshAll

Set pT = ActiveSheet.PivotTables(1) 'Select the PivotTable for the loop
Set pF = pT.PivotFields("Vendor Name") 'Select the PivotField we will loop through

For i = 1 To pF.PivotItems.Count
Call EmailGP 'This is the function that makes/sends the e-mail
Next i
End Sub


I thought that would crack it, but it just tells me 'Object Required', what is it I'm missing?

Any help would be greatly appreciated.
Richard

 

[6StringJazzer]

Please show all the code including EmailGP. You are not calling it with any parameters so I have no idea how it knows the data needed to set up and send and email.

it just tells me 'Object Required'

When asking about a VBA error message, always indicate which line of code is causing the error.

Also, when posting code I recommend selecting the code and clicking VBA so as to preserve the spacing in the code.

 

[Richard1982]

Please show all the code including EmailGP. You are not calling it with any parameters so I have no idea how it knows the data needed to set up and send and email.


When asking about a VBA error message, always indicate which line of code is causing the error.

Also, when posting code I recommend selecting the code and clicking VBA so as to preserve the spacing in the code.

Hi Jeff,

Thanks for the reply When the error message pops up it doesn't give me a line number I'm afraid
Here's the EmailGP() code, when run separately it works perfectly.

Sub EmailGP()

    Dim EntityName As String
    Dim VendorName As String
    Dim DisplayEmail As Boolean
    Dim OutlookApp As Object, OutlookMail As Object
    Dim rng As Range
    Dim Copyrange As String
    Dim DataCheck As String
    
    'Specify the start and end rows of the data
Startrow = "8"
Lastrow = ActiveSheet.Range("H8")

'Use these values to specify the range of the data
Let Copyrange = "A" & Startrow & ":" & "B" & Lastrow
    
    Set rng = Sheets("Pivot").Range(Copyrange).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If
    

    DisplayEmail = True 'Preview E-mail before sending
    EntityName = Sheets("Pivot").Range("B2")
    VendorName = Sheets("Pivot").Range("B3")
        
    Set OutlookApp = CreateObject("Outlook.Application")

  
        'Create a new mail message
        Set OutlookMail = OutlookApp.CreateItem(0)
                
        'Display email and specify To, Subject, etc
        With OutlookMail
             EmailName = ""
            .Display
            
            .To = Sheets("Pivot").Range("E5")
            .CC = ""
            .BCC = ""
            
            .Subject = EntityName & " - Vendor; " & VendorName
            .htmlBody = "Hello,<BR><BR> There are GR's without a matching balance of invoices for the vendor; " & VendorName _
            & ". Please can you contact the vendor and ask if they are expecting any further payments for the PO numbers listed below." _
            & " If so, please can you request copies of these missing invoices?<BR>" & RangetoHTML(rng) & .htmlBody
            
            
            .htmlBody = Replace(.htmlBody, "1.111", "GR, no invoice")
            .htmlBody = Replace(.htmlBody, "2.222", "GR higher than invoice")
            
                
            ' Change this to True to automatically send emails without first viewing them
            If DisplayEmail = False Then
            
                .Send
            
            End If
        
        End With

                    
    ' Tidy up
    Set OutlookApp = Nothing
    Set OutlookMail = Nothing
    
End Sub

 

[6StringJazzer]

When the error message pops up you get a choice of Debug, End, and Help. Click Debug. It will jump to the code and the line that caused the error will be highlighted in yellow.

Your Sub EmailGP has all the email data hardcoded so if you call it in a loop, it's going to send the same email to the same person over and over. This Sub must be rewritten to accept parameters giving the details for each email. I'm afraid I can't help with that without knowing what your Pivot sheet looks like.