Excel Macro - concatenate filename with text in a cell

[Alexk13]

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.

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

 

[rondeondo]

Hi Alex.
I see two things.
1 your loop will be performed once with the empty row as your loop end condition is set at the start but doesn't cause the while to end until it has completing the loop one more time.
2 you are declaring the variable inside the loop.
3 missing .value in the file name

I've made some changes

Sub Email()

Dim BlankFound As Boolean Dim x As Long
Dim Mail_Object, Mail_Single As Variant
BlankFound = false
X=1

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

Do While BlankFound = False

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")[B].value[/B] & ".pdf")

.Display
' .send

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

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

Loop
End Sub

 

[Alexk13]

This works perfect!
Thank you very much for your help.

 

[mole999]

@Alexk13

Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

 

[Alexk13]

Excel Macro - concatenate filename with text in a cell

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

stackoverflow.com