Excel Macro - concatenate filename with text in a cell

Alexk13

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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

VBA Code:
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

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
This works perfect!
Thank you very much for your help.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,295
Messages
5,577,237
Members
412,777
Latest member
MrGray
Top