Find hard drive path versus OneDrive

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Find hard drive path versus OneDrive

Post by gailb »

All of my documents are stored on the OneDrive, so when I run a macro in Excel, it's not recognizing the path.

I have an Excel file with the path in one column to some pdf documents I'm attaching to an email. It's all working on my computer that is not connected to the OneDrive, but of course as soon as I try it on the computer where all files are saved to the OneDrive, path is not found. How can I pick-up my hard drive part versus the OneDrive path? I've looked for macros on-line already composed, but they are just not helping me out here.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find hard drive path versus OneDrive

Post by HansV »

How do you use the path?
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Find hard drive path versus OneDrive

Post by gailb »

Hi Hans,

The path is typed into one of the cells in the workbook. So, the path looks like

C:\Users\gailb\OneDrive\Desktop\Email Attachments

where the hard drive path is

C:\Users\gailb\Desktop\Email Attachments

Code: Select all

Sub Mail_Attachment_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim LastRow As Long: LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    
    On Error Resume Next
        For i = 2 To LastRow
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = Sheet1.Range("B" & i).Value2
                .CC = ""
                .BCC = ""
                .Subject = "Course Certificate"
                .Body = "Hello " & Sheet1.Range("A" & i).Value2 & ","
                .Attachments.Add Sheet1.Range("C" & i).Value2
                .Send
            End With
        Next i
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Since the code does not see the right path, the .Attachments does not pickup anything.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find hard drive path versus OneDrive

Post by HansV »

What does column C contain?
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Find hard drive path versus OneDrive

Post by gailb »

It contains the path string

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find hard drive path versus OneDrive

Post by HansV »

I don't understand. If column C just contains a path such as C:\Users\gailb\OneDrive\Desktop\Email Attachments, the line

.Attachments.Add Sheet1.Range("C" & i).Value2

will fail since you specify a folder, not a file.
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Find hard drive path versus OneDrive

Post by gailb »

Sorry about that. It actually has the file name in that path. I failed to post it.

So, it would be, C:\Users\gailb\OneDrive\Desktop\Email Attachments\Student1.pdf

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Find hard drive path versus OneDrive

Post by gailb »

Hi Hans,

I was really messing this up. After your comment about missing the file name, I've included all of the files names in the path and all is working well. Thanks for getting me on the right track

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find hard drive path versus OneDrive

Post by HansV »

Glad you hear you were able to solve it.
Best wishes,
Hans