Sending Outlook Attachments with VBA

I am using the code below to select a recipient and attach the corresponding attachment. I am getting an error saying that the oject does not allow this and the code seems to stop at the line directly following. What do I need to change?

OlMail.Attachments.Add attacher

Sub CreateEmail_AR()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Range
Dim ToRecipientSTR As String
Dim CcRecipient As Variant
Dim attstr As String


'Dim subjectField As String
'subjectField = Application.InputBox(Prompt:="Please type the subject", Type:=2)
Dim attacher As Workbook
Set ToRecipient = Application.InputBox(Prompt:= _
                "Please select a range with the recipient.", Type:=8)
     ToRecipientSTR = ToRecipient.Value
attstr = ToRecipient.Offset(0, 2).Value
Set attacher = Workbooks.Open(attstr)

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)


'For Each ToRecipient In Array("User 1", "User 2", "User 3")
OlMail.Recipients.Add ToRecipientSTR
'Next ToRecipient

'For Each CcRecipient In Array("User 4", "User 5", "User 6")
'With OlMail.Recipients.Add(CcRecipient)
'.Type = olCC
'End With
'Next CcRecipient

'fill in Subject field
OlMail.Subject = "Report as of " & Month(Now()) & "/" & Day(Now()) - 2 & "/" & Year(Now())

'Add the active workbook as an attachment
OlMail.Attachments.Add attacher

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Sub

Hi,

Untested but probably because you've Dimmed 'attacher' as a Workbook object. The .Add instruction is expecting to see a string reference which should be the complete drive\path\folder\filename.

That may in fact be your existing attstr variable but I can't tell whether this includes the full drive\path\folder string.

HTH

I doubt you can attach an open workbook using this method (sending open files generally warrants SendMail which is limited in terms of other functionality like body etc...)

edit: probably wrong though...