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]

yeah i agree, well if that is indeed a valid file path it should resolve the error about the file missing or path being invalid... it looks like it no longer gets cutoff... vba i know has problems with file paths and spacing... it might also require you surround the file path with single quotes... perhaps that is only an excel thing but worth a try if it isnt accepting the path still. When I say path i also mean the entire filename not just the directory like you describe in the code... i would change the name of 'path' to 'directory' to make things more clear to others who read your code and you can concatenate attachment this way...

directory & "\" & Filename

i would remain consistent with variable capitalization... go with camelCase... makes it much easier for others to read code when your naming is consistent and describes the object.

attachment = directory & "\" & fileName

dim the variables with these names imo is better, local scope variables should be camel case

If you are curious I would lookup the .Net naming convention and use that... it describes how different programming objects should be named. This all doesnt matter but it matters when you ask others to look at your code.