Attach File to Excel generated Outlook E-mail

[DWRgt2885]

Hey Everyone!

Can't seem to figure this one out for the life of me. I click submit, an e-mail is generated, the macro takes value's from text boxes in user form to create the "To", "Subject" & "Body" fields, but I also need it to attach the file listed in the PO text box (txtsspo.value). I've tried attachment.add and .attachment = "bla bla bla".

Not working, but the code doesn't error out either. Any suggestions?

Dim oOLook As Object
Dim oEMail As Object




Set oOLook = CreateObject("Outlook.Application")
oOLook.Session.Logon
Set oEMail = oOLook.CreateItem(0)
oEMail.Display
On Error Resume Next
With oEMail
.Importance = olImportanceHigh
.To = txtVendEML.Value
.CC = ""
.BCC = ""
.Attachment = "C:\Users\User\OneDrive - Company name llc\PO's\" & txtSSPO.Value '<----- NEED HELP HERE
.Subject = " PO# " & txtSSPO.Value
.Body = "Hi," & vbNewLine & "Attached is PO# " & txtSSPO.Value & "." & vbNewLine & _
"Please confirm estimated ship date, and your order # when you have a moment. Thank you and have a great day!" & vbNewLine & "Thanks," & vbNewLine & drpUserSingle.Value
End With

 

[Norie]

Try this.

.Attachment = "C:\Users\User\OneDrive - Company name llc\PO's\" & txtSSPO.Value

 

[DWRgt2885]

Thanks But that's what i have already isn't it?

 

[Logit]

.
Perhaps he meant :

.Attachments.Add [COLOR=#333333]= "C:\Users\User\OneDrive - Company name llc\PO's\" & txtSSPO.Value[/COLOR]

 

[DWRgt2885]

Thank you both for the quick replies, i tried both of those originally and neither worked.

 

[Norie]

'neither worked' in what way?

 

[DWRgt2885]

I tried both below lines of code prior to posting this, and again when these nice users suggested them. Neither worked in the sense that nothing got attached to the e-mail.. The files exist, in the path described, but nothing is being attached?

.Attachments.Add [COLOR=#333333]= "C:\Users\User\OneDrive - Company name llc\PO's\" & txtSSPO.Value
[/COLOR]
.Attachment = "C:\Users\User\OneDrive - Company name llc\PO's\" & txtSSPO.Value

</pre>

 

[Logit]

.
Often in these cases it comes down to spelling, spacing, etc.

Re-check everything in the path description. Sometimes what appears correct is off by one letter .. one space (too much / too little) or some other tiny error.

 

[DWRgt2885]

hmmm....I copied the Path directly from the File location properties.

When i submit my form data; the text box containing "txtsspo.value" adds the PO number to my sheet. Then it hyperlinks that cell to the document in the path "C:\Users\User\OneDrive - Company name llc\PO's"

So when i hit "Submit" on my user form i'm asking Excel to generate an e-mail, and then attach "C:\Users\User\OneDrive - Company name llc\PO's" & txtsspo.value.....


Should i instead be asking a different way since the value on the sheet is merely a hyperlink?

 

[Logit]

.
Try change :

[COLOR=#333333]txtSSPO.[B]Value[/B][/COLOR]

To :

[COLOR=#333333]txtSSPO.[B]Text[/B][/COLOR]