VBA producing unexpected results

Hello,

I am writing VBA coded to execute a macro that attaches a file to an email. Within the code, I use the .Attachments.Add command along with the directory path of where the attachment resides. All works fine and the macro does its job by attaching the chosen file to the email as expected, except when one of the subdirectories is named with two words separated by a space.

For example, when the directory path is c:\users\tom\documents\test.pdf, the macro attaches the file successfully to the email. However, when the directory path is c:\users\tom\test files\test.pdf and the subdirectory "test files" name contains a space between the two words, the macro is unable, or won't, attach the file to the email.

Please comment if you know of a reason why the macro does not perform as expected. Does there need to be a character to be placed between the two words when I type the .Attachments.Add command and add the directory path where the file I want to attach is located? I'm stuck.

Good morning trundle

Try adding an extra set of quotes around the path, so :

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

HTH

DominicB

Thanks Dominic,

Thank you for your reply and suggestion. The line of code is actually written as follows: .Attachments.Add "c:\users\Randle\TWR Documents\HMT Group Results Report Test.pdf". Where the subject space occurs in the name of the subdirectory "TWR Documents". So quotes are actually in use around the path. So I rewrote the line to add additional quotes as you suggest as follows: .Attachments.Add ""c:\users\Randle\TWR Documents\HMT Group Results Report Test.pdf"" and received the following error: Compile Error: Syntax Error.

Where would I add the text you propose above? Would it be included as part of the above line or somewhere else in the set of code and would I need to include the DirPath = Chr (34) text as well?

Hi trundle

Do as xlnitwit suggested and post more info, as he said, the code should work.

Before you do just try :

Please Login or Register  to view this content.

HTH

DominicB

Dominic,

I tried adding the DirPath line as you demonstrated above and immediately received the following error: "Compile Error: Expected end of statement:" I guess the editor did not like the double quotes together. Then, I removed one set of quotes from the path statement. The error went away and I ran the macro using the new DirPath text. All I got was an email shell set up with the addressees I wanted along with the body of the email, but no attachment. So I'm back to square one.

Since Dominic's suggestion did not work, I am following xlnitwit's advice and have pasted my macro code below. Also, I have verified that the path is correct. Apologies, but I know I'm supposed to copy and past code a certain way (using some sort of tags I think?) in order for it to be properly read. I did not do that here, so if someone will tell me how I need to paste the code, I will do so and resubmit my response.

Sub ResultsReporteMail()

Dim Answer As String
Dim Question As String

'opens message box and asks user to confirm they want to send email
Question = "Do you want to send the HMT Group Weekly Results Report to everyone?"

Answer = MsgBox(Question, vbQuestion + vbYesNo, "eMail Registration to Regulars")

If Answer = vbNo Then
Exit Sub
End If

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

'eMail settings have been setup to reference cells in the Configuration worksheet
With OutMail
.To = Worksheets("Configuration").Range("E28")
.CC = Worksheets("Configuration").Range("E30")
.BCC = Worksheets("Configuration").Range("E31")
.Subject = Worksheets("Configuration").Range("C32")
.Body = Worksheets("Configuration").Range("C33")
DirPath = "c:\users\Randle\TWR Documents\HMT Group Results Report Test.pdf"
.Attachments.Add DirPath
.Display
'.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

Dominic,

I tried adding the DirPath line as you demonstrated above and immediately received the following error: "Compile Error: Expected end of statement:" I guess the editor did not like the double quotes together. Then, I removed one set of quotes from the path statement. The error went away and I ran the macro using the new DirPath text. All I got was an email shell set up with the addressees I wanted along with the body of the email, but no attachment. So I'm back to square one.

Since Dominic's suggestion did not work, I am following xlnitwit's advice and have pasted my macro code below. Also, I have verified that the path is correct. Apologies, but I know I'm supposed to copy and past code a certain way (using some sort of tags I think?) in order for it to be properly read. I did not do that here, so if someone will tell me how I need to paste the code, I will do so and resubmit my response.

Please Login or Register  to view this content.

I removed the "On Error Resume Next" code as suggested and reran the macro. I received the following error message: "Run-Time error '-2147024893 (80070003)': Path does not exist. Verify the path is correct.". Next I went to where the attachment file is stored and right-clicked on Properties and found the location listed was: c:\Users\Randle\Documents. I revised the macro code to now read "c:\users\Randle\Documents\HMT Group Results Report Test.pdf" and reran the macro. This time it worked perfectly and not only created the email but also attached the HMT Group Results Report Test.pdf file!

So, you were correct in that I had the path listed incorrectly. However, in the file explorer, the name of the folder where the attachment file is located is "TWR Documents" and not "Documents". It appears the issue has been resolved, yet I have a couple of questions. Why does the name of the folder appear to not match the path listed for the file under the Properties view? Second, I take it the "On Error Resume Next" code is useless and unnecessary?

Many thanks to xlnitwit and to Dominicb for your assistance in solving my issue. You really helped me out of a bind and the ultimate solution worked perfectly!