Excel Macro - concatenate filename with text in a cell

Alexk13

New Member
Joined
Sep 18, 2020
Messages
6
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top