If the value to use for the file name is now in Column A not C change
Cells(irow, 3)
to
Cells(irow, 1)
The second part is the columns argument and column A is number 1 on the sheet.
Cells(row,column)
Overall with the code see the following:
1) Option Explicit at the top to make sure all variables are declared, that the typing is consistent and your spelling is consistent.
2) Put those declarations in. Note that a reference to the Outlook Object Library must be added if you are going to use early binding. Early binding will be quicker but will hit problems if someone else using your program has a different version of Outlook.
3) Set the scope of the procedure e.g. I can't see the signature for your sub so I will assume it is called as public i.e. can be called from other modules. So for my test example I am putting:
Public Sub test()
4) iRow needs somewhere to start and it can't be 0 , which is what it is instantiated as. There is no 0 row in the sheet. Long type variables and Integers (I couldn't see a declaration so I went with Long). For the why, see the discussion here. It is certainly a safer bet as with Integer you run the risk of overflow. You would set this to the start row for where you want to commence looping. In your case row 24.
5) That loop..... you have a number of options:
You could also consider:
Do Until IsEmpty(ActiveSheet.Cells(irow, 1))
This will stop at the first empty cell. Notice that I have explicitly stated which sheet is being worked with via ActiveSheet.Cells rather than implicitly calling by Cells. Better still would be to use the actual sheet name.
Or, if you know that all the cells will be populated up to an including row N in column A you could put:
Do Until irow = N + 1
I am less of a fan of the second. There are lots of other ways to construct your loop but essentially you want to look for the earliest viable exit.
6) Use vbNullString rather than the empty string literal ""
i.e. pfile <> vbNullString rather than pfile <> "".
It is faster to assign, more efficient and generally what you want to use.
7) If you really did want easy access to the start row and column you could have them as public constants. In which case you would declare them at the top of the module after the Option Explicit like so:
Public Const startRow As Long = 24
Public Const columnToLoop As Long = 1
You would then refer to them in the code as
Cells(iRow, columnToLoop) rather than Cells(irow, 1)
and
irow = startRow rather than irow = 24
So in summary you could have something like:
Option Explicit
Public Const startRow As Long = 24
Public Const columnToLoop As Long = 1
Public Sub test()
Dim ObMail As MailItem
Dim irow As Long
Dim dPath As String
Dim pfile As String
dPath = vbNullString 'what are your values
pfile = vbNullString 'what are you values
Set ObMail = Outlook.CreateItem(OlMailItem)
irow = startRow
With ObMail
.To = "email@comapny.com"
.Subject = "O/S Blanace"
.BodyFormat = olFormatPlain
.Body = "Please see attached files"
Do Until IsEmpty(ActiveSheet.Cells(irow, columnToLoop))
'picking up file name from column A
pfile = Dir(dPath & "\*" & Cells(irow, columnToLoop) & "*")
'checking for file exist in a folder and if its a pdf file
If pfile <> vbNullString And Right(pfile, 3) = "pdf" Then
.Attachments.Add (dPath & "\" & pfile)
End If
'go to next file listed on the A column
irow = irow + 1
Loop
.Send '.Display 'for testing purposes
End With
End Sub
