Excel Macro - concatenate filename with text in a cell

Alexk

New member
Joined
Sep 18, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2010
I have a sheet where in column A I have email addresses and in column B the client IDs. I would like to use the client IDs to match the file name,
so the right file can be grabbed from a folder and then be attached to an email, using the email address in column A. here is a sample data:

Column A | Column B
client1@yahoo.com | 12345
client2@gmail.com | 67890

The files in the c:\PDF Files\Invoice folder: Invoice12345.pdf Invoice67890.pdf

I am trying to use the following macro to attach Invoice12345.pdf to an email using outlook and send it to client1@yahoo.com and repeat it until
there are no more emails in column A. for some reason, sometimes it works but only creates one email and crashes when it tries to get to the second record.
here is the error message: "Run-time error'-2147024894 (80070002)': Cannot find this file. Verify the path and file name are correct."

Thank you in advance for your help.

Code:
Sub Email()

Dim BlankFound As Boolean Dim x As Long

Do While BlankFound = False

  x = x + 1
  If Cells(x, "A").Value = "" Then
  BlankFound = True
  End If

Dim Mail_Object, Mail_Single As Variant

    Email_Subject = "Booking Invoice"
    nameList = Sheets("Sheet1").Cells(x, "A").Value
    Email_Send_To = nameList

    Email_Cc = ""
    Email_Bcc = ""
    Email_Body = "Here's your Invoice"

    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(o)

    With Mail_Single
        .Subject = Email_Subject
        .To = Email_Send_To
        .CC = Email_Cc
        .BCC = Email_Bcc
        .Body = Email_Body
        .Attachments.Add ("c:\PDF Files\Invoice\Invoice" & Cells(x, "B") & ".pdf")
   
        .Display
       ' .send

End With ' MsgBox "E-mail successfully sent" ' Application.DisplayAlerts = False

Loop
End Sub
 
Last edited by a moderator:
You are missing the \ between the path and the file name
 
You are missing the \ between the path and the file name

Thanks for your reply.
the path is c:\PDF Files\Invoice\
and the file name is Invoice12345.pdf

so where am I missing a \ ?
 
between the path and the file name
should be like this

Code:
 .Attachments.Add ("c:\PDF Files\Invoice\Invoice" [COLOR=#ff0000]& "\"[/COLOR] & Cells(x, "B") & ".pdf")
 
.Attachments.Add ("c:\PDF Files\Invoice\Invoice" & Cells(x, "B") & ".pdf")

The section I underlined is the file name. it's not part of the the path. I am trying to change the file name every time it loops through. for example the first time, the path is
c:\PDF Files\Invoice\ and the file name is "Invoice12345.pdf". the second time (which seems to causing the issue) the path is the same (c:\PDF Files\Invoice\) but the file name is "Invoice67890.pdf"
 
maybe leading spaces in the cell ?
try
Code:
.Attachments.Add ("c:\PDF Files\Invoice\Invoice"& trim(Cells(x, "B")) & ".pdf")
 
maybe leading spaces in the cell ?
try
Code:
.Attachments.Add ("c:\PDF Files\Invoice\Invoice"& trim(Cells(x, "B")) & ".pdf")

Thank you very much for your help.
The following is working now. The only problem is that it loops one extra time! foe example if I only have to rows with email addresses, it still goes through the code 3 times which causes the error!

Code:
Sub Email()

Dim BlankFound As Boolean
Dim x As Integer

Do While BlankFound = False

      x = x + 1
      If Cells(x, "A").Value = "" Then
      BlankFound = True
      End If
 
Dim Mail_Object, Mail_Single As Variant

        Email_Subject = "Booking Invoice"
 
      nameList = Sheets("Sheet1").Cells(x, "A").Value
         
        Email_Send_To = nameList
 
        Email_Cc = ""
        Email_Bcc = ""
        Email_Body = "Here's your Invoice"
 
        Set Mail_Object = CreateObject("Outlook.Application")
        Set Mail_Single = Mail_Object.CreateItem(o)
 
        With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
 
            .CC = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .Attachments.Add ("c:\PDF Files\Invoice1\Invoice" & Cells(x, "B") & ".pdf")

            .Display
           ' .send
 
End With
 '       MsgBox "E-mail successfully sent"
 '       Application.DisplayAlerts = False
   
   Loop
        
 End Sub
 
Last edited by a moderator:
What if you do away with BlankFound and just use
Code:
If Cells(x, "A").Value = "" Then Exit Do
 
Hi and welcome
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question

Do not post any further responses in this thread until a link has been provided to these cross posts.
 
Back
Top