Need help in VB

srinivasanyadhav
StarLounger
Posts: 81
Joined: 21 Apr 2014, 10:45
Location: Chennai, India

Need help in VB

Post by srinivasanyadhav »

Dear All,

I am not able to run this code.
Can you please help me out to find the problem.

Code: Select all

Sub SendPayadvice()
     Const CompanyCol = "A"
     Const MailCol = "B"
     Const FirstRow = 2
     Const FileFolder = "C:\Users\pur2\Desktop\New folder"
         
    Dim LastRow As Long
     Dim StartRow As Long
     Dim CurRow As Long
     Dim Company As String
     Dim Email As String
     Dim Filename As String
     Dim StartedOL As Boolean
     Dim olApp As Object
     Dim olMsg As Object

     On Error Resume Next
     Set olApp = GetObject(Class:="Outlook.Application")
     If olApp Is Nothing Then
         Set olApp = CreateObject(Class:="Outlook.Application")
         If olApp Is Nothing Then
             MsgBox "Cannot start Outlook!", vbExclamation
             Exit Sub
         End If
         StartedOL = True
     End If
     On Error GoTo ErrHandler
     olApp.Session.Logon

     LastRow = Range(CompanyCol & Rows.Count).End(xlUp).Row
     ' Process in batches of 25
     For StartRow = FirstRow To LastRow Step 25
         For CurRow = StartRow To Application.Min(StartRow + 24, LastRow)
             Company = Range(CompanyCol & CurRow).Value
             Filename = FileFolder & Company & ".pdf"
             ' Test whether the file to be attached exists
             If Dir(Filename) <> "" Then
                 Email = Range(MailCol & CurRow).Value
                 Set olMsg = olApp.CreateItem(0) ' olMailItem
                 olMsg.Subject = "PAYMENT ADVICE for " & Company
                 olMsg.Body = "Dear Sir " & vbNewLine & _
"            " & vbNewLine & _
"Please find the attached PAYMENT ADVICE." & vbNewLine & _
"          " & vbNewLine & _
"Regards," & vbNewLine & _
"Premsankar M" & vbNewLine & _
"Sr.Officer - Finance" & vbNewLine & _
"ILJIN Automotive pvt Ltd" & vbNewLine & _
"B1&B2, SIPCOT INDUSTRIAL PARK" & vbNewLine & _
"Irungattukottai, Sriperumbudur" & vbNewLine & _
"Tamilnadu - 602 105" & vbNewLine & _
"Tel : +91-44-4710 2116"

                 olMsg.Recipients.Add Email
                                
                 ' olCC
                olMsg.Attachments.Add Filename
                 
                 ' To test, open the message:
                 olMsg.Display
                 ' For the final version, send it immediately:
                 'olMsg.Send
        olMsg.Importance = 2
                 
             End If
         Next CurRow
     Next StartRow

ExitHandler:
     On Error Resume Next
     If StartedOL And Not olApp Is Nothing Then
         olApp.Quit
     End If
     Exit Sub
      With OutMail
.ReadReceiptRequested = True
End With

ErrHandler:
     MsgBox Err.Description, vbExclamation
     Resume ExitHandler
 End Sub






Regards,
Srinivasan

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Need help in VB

Post by Rudi »

What seems to be the problem?
Is there an error message you get? If so, what?
Where does the code debug?
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need help in VB

Post by HansV »

The part

Code: Select all

      With OutMail
.ReadReceiptRequested = True
End With
makes no sense. It refers to a variable OutMail that hasn't been defined, and it is in the ExitHandler section so even if it was correct it would run after the message had been sent.
The line

Code: Select all

                olMsg.Importance = 2
is also placed incorrectly: after the message has been sent.

Here is a hopefully correct version. I also corrected the inconsistent indentation of the modified parts of the code.

Code: Select all

Sub SendPayadvice()
     Const CompanyCol = "A"
     Const MailCol = "B"
     Const FirstRow = 2
     Const FileFolder = "C:\Users\pur2\Desktop\New folder"

     Dim LastRow As Long
     Dim StartRow As Long
     Dim CurRow As Long
     Dim Company As String
     Dim Email As String
     Dim Filename As String
     Dim StartedOL As Boolean
     Dim olApp As Object
     Dim olMsg As Object

     On Error Resume Next
     Set olApp = GetObject(Class:="Outlook.Application")
     If olApp Is Nothing Then
         Set olApp = CreateObject(Class:="Outlook.Application")
         If olApp Is Nothing Then
             MsgBox "Cannot start Outlook!", vbExclamation
             Exit Sub
         End If
         StartedOL = True
     End If
     On Error GoTo ErrHandler
     olApp.Session.Logon

     LastRow = Range(CompanyCol & Rows.Count).End(xlUp).Row
     ' Process in batches of 25
     For StartRow = FirstRow To LastRow Step 25
         For CurRow = StartRow To Application.Min(StartRow + 24, LastRow)
             Company = Range(CompanyCol & CurRow).Value
             Filename = FileFolder & Company & ".pdf"
             ' Test whether the file to be attached exists
             If Dir(Filename) <> "" Then
                 Email = Range(MailCol & CurRow).Value
                 Set olMsg = olApp.CreateItem(0) ' olMailItem
                 olMsg.Subject = "PAYMENT ADVICE for " & Company
                 olMsg.Body = "Dear Sir " & vbNewLine & _
                    "            " & vbNewLine & _
                    "Please find the attached PAYMENT ADVICE." & vbNewLine & _
                    "          " & vbNewLine & _
                    "Regards," & vbNewLine & _
                    "Premsankar M" & vbNewLine & _
                    "Sr.Officer - Finance" & vbNewLine & _
                    "ILJIN Automotive pvt Ltd" & vbNewLine & _
                    "B1&B2, SIPCOT INDUSTRIAL PARK" & vbNewLine & _
                    "Irungattukottai, Sriperumbudur" & vbNewLine & _
                    "Tamilnadu - 602 105" & vbNewLine & _
                    "Tel : +91-44-4710 2116"
                olMsg.Recipients.Add Email
                olMsg.Attachments.Add Filename
                olMsg.Importance = 2
                olMsg.ReadReceiptRequested = True
                ' To test, open the message:
                olMsg.Display
                 ' For the final version, send it immediately:
                 'olMsg.Send
             End If
         Next CurRow
     Next StartRow

ExitHandler:
     On Error Resume Next
     If StartedOL And Not olApp Is Nothing Then
         olApp.Quit
     End If
     Exit Sub

ErrHandler:
     MsgBox Err.Description, vbExclamation
     Resume ExitHandler
End Sub
Best wishes,
Hans

srinivasanyadhav
StarLounger
Posts: 81
Joined: 21 Apr 2014, 10:45
Location: Chennai, India

Re: Need help in VB

Post by srinivasanyadhav »

Thank you HansV.
I have used the code, but not able to run.
Please see the attachment and help.
You do not have the required permissions to view the files attached to this post.
Regards,
Srinivasan

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need help in VB

Post by HansV »

You have placed the macro in the worksheet module of the MATERIAL PAYMENT worksheet. You should create a standard module by selecting Insert > Module in the Visual Basic Editor, then move the macro to this new module. You will then be able to run it.
Best wishes,
Hans

srinivasanyadhav
StarLounger
Posts: 81
Joined: 21 Apr 2014, 10:45
Location: Chennai, India

Re: Need help in VB

Post by srinivasanyadhav »

Thank you HansV.
Please look into the sheet.
I have created a module and assigned a button to run the code.
But not working. Help please.
You do not have the required permissions to view the files attached to this post.
Regards,
Srinivasan

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Need help in VB

Post by Rudi »

Replace the line:

Code: Select all

     Const FileFolder = "C:\Users\pur2\Desktop\New folder"
With this line:

Code: Select all

     Const FileFolder = "C:\Users\pur2\Desktop\New folder\"
Its just a matter of including a backslash after New folder so the path and file concatenation is correctly validated further down the macro.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

srinivasanyadhav
StarLounger
Posts: 81
Joined: 21 Apr 2014, 10:45
Location: Chennai, India

Re: Need help in VB

Post by srinivasanyadhav »

Thank you Rudi & HansV
Works fine...
Regards,
Srinivasan