Use VBA to attach document from referenced cell to email through Outlook

[VBANEWBIE1229]

Hi guys, I am trying to send an email automatically from excel 2013 using VBA, the email is successful, however, I am having trouble getting it to attach a document from a referenced cell. I would like it to attach the document that is linked into cell B9. The following code is what I have. Thanks in advance for your help!!!

Private Sub CommandButton21_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Set Rng = Range("B9")
On Error Resume Next
With OutMail
.To = Range("D9")
.CC = Range("E9")
.BCC = ""
.Subject = "Subject"
.Body = "Body"
.Attachments.Add ActiveSheet.Range("b9").Select
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Truiz]

Untested but you could try replacing

[B][U].Attachments.Add ActiveSheet.Range("b9").Select[/U][/B]

With

[COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Attachments[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Add [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]ActiveSheet.Range("B9").Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR]

This Should work as long as B9 contains the full path of the file

 

[VBANEWBIE1229]

Thank you!!! It worked perfectly.