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
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
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
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.