Referencing file from One Drive

[jaihawk8]

I have written this script, which works perfectly. Unfortunately, when I change the path in B2 for the file path to try to reference my OneDrive path, it blows it up. This is actually going to be run on another person's computer, so ideally, I would like it to find the file, no matter who runs the report. Here's the scripting:

Sub Send_email_fromexcel()
Dim Edress As String
Dim Subject As String
Dim Message As String
Dim Filename As String
Dim outlookapp As Object
Dim myAttachments As Object
Dim path As String
Dim lastrow As Integer
Dim Attachment As String
Dim x As Integer


x = 6
y = 2
Z = 3


Do While Sheets("Austin").Cells(x, 1) <> ""


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments


path = Sheets("Austin").Range("B2")
Edress = Sheets("Austin").Cells(x, 1)
Subject = Sheets("Austin").Cells(x, 2)
Filename = Sheets("Austin").Cells(x, 3)
Attachment = path + Filename


outlookmailitem.To = Edress
outlookmailitem.cc = ""
outlookmailitem.bcc = ""
outlookmailitem.Subject = Subject
outlookmailitem.body = Sheets("Austin").Cells(Z, 2)


myAttachments.Add (Attachment)
outlookmailitem.display
outlookmailitem.send


lastrow = lastrow + 1
Edress = ""


x = x + 1


Loop


Set outlookapp = Nothing
Set outlookmailitem = Nothing




End Sub

 

[cerfani]

it blows it up

on what line? did you step through the code to debug?

 

[jaihawk8]

When I change B2 (Path) to: C:\Users\jasonb\NEOPOST\Evans, Pamela - Central District Payroll\2018 Payroll Files\May 18 Earnings Statements\AU

It fails on:

myAttachments.Add (Attachment)

Run-time error '-2147024894(80070002)':
Cannot find this file. Verify the path and file name are correct.

 

[cerfani]

when you debug... what is the exact value this operation creates?

[COLOR=#333333]path + Filename[/COLOR]

that value appears to be invalid... you probably are missing a '\' if i had to guess

so your path in B2 doesnt have a trailing '\' and im guessing your filename does not have a leading '\' so that would create an invalid path

 

[jaihawk8]

when you debug... what is the exact value this operation creates?

[COLOR=#333333]path + Filename[/COLOR]

that value appears to be invalid... you probably are missing a '\' if i had to guess

so your path in B2 doesnt have a trailing '\' and im guessing your filename does not have a leading '\' so that would create an invalid path

I added a \ at the end of the path, but I still get the same error message when debugging.

 

[cerfani]

what was the value that got created and saved in the Attachment variable?

have a messagebox show it to you or breakpoint on the myAttachments.Add (Attachment) line and hover over attachment and what is the value?

i assume the directory the file is located is what you are calling 'path'

 

[jaihawk8]

what was the value that got created and saved in the Attachment variable?

have a messagebox show it to you or breakpoint on the myAttachments.Add (Attachment) line and hover over attachment and what is the value?

i assume the directory the file is located is what you are calling 'path'

I am not sure how to have a messagebox show it to me. I did hover over it and it says:

(Attachment) = "C:\Users\jasonb\NEOPOST\Evans,Pamela - Central District Pay... and then cuts off.

 

[cerfani]

replace this line...

[COLOR=#333333]Attachment = path + Filename[/COLOR]

with these lines...

MsgBox "path is... " & vbCrLf & vbCrLf & path
MsgBox "Filename is... " & vbCrLf & vbCrLf & Filename
[COLOR=#333333]Attachment = path & Filename
[/COLOR]MsgBox "Attachment after concatenation is... " & vbCrLf & vbCrLf & Attachment

so each message box will tell you what values you are working with if they are wrong then your code wont work... MsgBox lines can be deleted later once you figure out the bug

i just noticed the error... in vba you use & to concatenate... c languages use +

 

[cerfani]

i think your error is actually you need to use ampersand to concatenate... path & Filename

 

[jaihawk8]

i think your error is actually you need to use ampersand to concatenate... path & Filename

Here is what I get in the message boxes:

path is...

C:\Users\jasonb\NEOPOST\Evans, Pamela - Central District Payroll\2018 Payroll Files\May 18 Earnings Statements\AU\

Filename is...

Brandi Andel Commission Payout 05.04.18xlsx

Attachment after concatenation is...

C:\Users\jasonb\NEOPOST\Evans, Pamela - Central District Payroll\2018 Payroll Files\May 18 Earnings Statements\AU\Brandi Andel Commission Payout 05.04.18xlsx

Everything looks good there...