Send E-mail along with attachment based on Filename

This might help you along the way (excel needs extra column though, the customer name):

Public Sub CreateNewMessage()
Dim objMsg As MailItem
 
Dim xlApp As Object
Dim sourceWB As Object
Dim sourceWS As Object

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.EnableEvents = False
End With


strFile = "C:\users\kimputer\documents\contacts.xlsx"  'Put your file path.

Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWH = sourceWB.Worksheets("sheet1")

sourceWH.UsedRange 'Refresh UsedRange
lastrow = sourceWH.UsedRange.Rows(sourceWH.UsedRange.Rows.Count).Row

For i = 1 To lastrow

Set objMsg = Application.CreateItem(olMailItem)
 
With objMsg
  .To = sourceWH.Cells(i, 1).Value
  .CC = sourceWH.Cells(i, 2).Value
  .Subject = sourceWH.Cells(i, 3).Value
  .Body = sourceWH.Cells(i, 4).Value
   .BodyFormat = olFormatPlain ' send plain text message
   .Attachments.Add ("C:\pdffiles\" & sourceWH.Cells(i, 5).Value & ".pdf")
   .Display
   .Send  
End With

Set objMsg = Nothing


Next

Set sourceWH = Nothing
sourceWB.Close
Set sourceWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Select allOpen in new window

Obviously, if you see a file path, adjust it to an existing one. Test it first without the ".Send" line and a smaller contacts file. After it works, add the .Send and have the full contacts file.

Please try something like this....

Since you have not attached a sample file, please refer to the attached and make the required change on the sheet and in the code as well.

Sub SendEmailWithAttachment()
Dim olApp As Object
Dim olEmail As Object
Dim fso As Object
Dim folderPath As String, fileName As String
Dim rng As Range, cell As Range
Dim lr As Long

Application.ScreenUpdating = False

'Path of the folder containing pdf files
folderPath = Environ("UserProfile") & "\Desktop\Files\"

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)

Set fso = CreateObject("Scripting.FileSystemObject")
Set olApp = CreateObject("Outlook.Application")

'Check if the Folder with pdf file exists
If Not fso.FolderExists(folderPath) Then
   MsgBox "Folder " & folderPath & " doesn't exist.", vbCritical, "Folder Not Found!"
   Exit Sub
End If
For Each cell In rng
   fileName = cell.Value & ".pdf"
   
   'If the pdf file with the customer name exists, send the email
   If fso.FileExists(folderPath & fileName) Then
      With olApp.createitem(0)
         .to = cell.Offset(0, 1).Value
         .cc = cell.Offset(0, 2).Value
         .Subject = cell.Offset(0, 3).Value
         .body = Range("F1").Value
         .attachments.Add folderPath & fileName
         .send
      End With
   End If
Next cell
Application.ScreenUpdating = True
Set olApp = Nothing
Set fso = Nothing
End Sub

Select allOpen in new window

SendEmailWithAttachment.xlsm

Hi Neeraj, that was brilliant. but only one request can it pick the customer name from anywhere in the file for example i will have customer in the file as Jerry 061516 or  061516 Jerry

Don't forthwith to use the bypass of Outlook security or you will have to click send on each message.  I use this script successfully.

https://www.experts-exchange.com/articles/17839/Mailmerge-using-Visual-Basic-MS-Excel-2010-and-MS-Outlook-2010.html

See the last several lines.

    .display
    Application.Wait (Now + TimeValue("0:00:02"))
    Application.SendKeys "%s"

This displays the email then hits the send key.

hi Kimputer getting a debug error in the line

.Attachments.Add ("C:\Users\123456\Desktop\infi" & sourceWH.Cells(i, 5).Value & ".pdf")

Select allOpen in new window

i have added a column with "Customer" in column 5

Hi Neeraj, that was brilliant. but only one request can it pick the customer name from anywhere in the file for example i will have customer in the file as Jerry 061516 or  061516 Jerry

I am completely not getting your requirement.
Do you mean that your code will reside in a different workbook and the information about the customers in a different workbook?

It would be helpful if you can provide a sample workbooks.

BTW did you try the code in the attached? Did it run successfully?

I ran your code and was successful..the only thing is that in excel I will have customer name but the actual file that needs to be attached will have customer name and invoice number example as in your file jerry 6547890

thanks a ton Neeraj. worked brilliant

You're welcome Udai! Glad I could help.
Thanks for the feedback.

Hi Neeraj sorry to bug you on a closed question. if i have multiple customers with same name and i add serial numbers as 1.jerry 2. jerry can you we add that. here is the final code that i have

Dim fso As Object
Dim folderPath As String
Dim Folder As Object
Dim File As Object
Dim fileName As String
Sub SendEmailWithAttachment()
Dim olApp As Object
Dim olEmail As Object
Dim rng As Range, cell As Range
Dim lr As Long

Application.ScreenUpdating = False

'Path of the folder containing pdf files
folderPath = Environ("UserProfile") & "\Desktop\infi\"

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)

Set fso = CreateObject("Scripting.FileSystemObject")
Set olApp = CreateObject("Outlook.Application")

'Check if the Folder with pdf file exists
If Not fso.FolderExists(folderPath) Then
   MsgBox "Folder " & folderPath & " doesn't exist.", vbCritical, "Folder Not Found!"
   Exit Sub
End If
For Each cell In rng
   fileName = pdfFileName(cell.Value)
   If fileName <> "" Then
      With olApp.createitem(0)
         .to = cell.Offset(0, 1).Value
         .cc = cell.Offset(0, 2).Value
         .Subject = cell.Offset(0, 3).Value
         .body = Range("F1").Value
         .attachments.Add folderPath & fileName
         .SentOnBehalfOfName = Range("G2").Value
         '.send
         .display
      End With
   End If
Next cell
Application.ScreenUpdating = True
Set olApp = Nothing
Set fso = Nothing
End Sub

Function pdfFileName(vFileName As String) As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set Folder = fso.getfolder(folderPath)
For Each File In Folder.Files
   If InStr(File.Name, vFileName) > 0 And fso.GetExtensionName(File.Name) = "pdf" Then
      pdfFileName = File.Name
      Exit For
   End If
Next File
End Function

Select allOpen in new window