I am using a Sub Mail_ActiveSheet_Outlook() code in VB

G

Georgia girl

I am using a VB code in Excel that copies an excel sheet from a workbook,
saves it to another worksheet and then sends it via Outlook. I would like to
add another attachment of an rtf file but have been unsuccessful in doing so.
See code below:


Sub Mail_ActiveSheet_Outlook()
'
' Mail_ActiveSheet_Outlook()
' Macro recorded 3/8/2006 by United Parcel Service
'
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("Daily Carrier Info - Yard").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Yard Status " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Outbound FTC91102"
.Body = "Please see today's attached Full Outbound and Yard
Status reports."
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("\\usagatla01s1002\Sear_K Mrt
Share\Sears-Kmart\Daily Reporting\rptFullOutbound.rtf")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

The section (.Attachments.Add wb.FullName) I would think should be as
follows but it doesn't work.

..Attachments.Add rtf.("\\usagatla01s1002\Sear_K Mrt Share\Sears-Kmart\Daily
Reporting\rptFullOutbound.rtf")

Is this possible to do???

Thanks.
 
R

Ron de Bruin

Hi Georgia girl

No problem to add a file from the network for me

Are you sure the path is correct ???
 
G

Georgia girl

Yes, the path is correct. I copied it from the window bar. I receive a
compile error - expected identifier or bracketed expression. It seems any
way I try to correct it it gives another error. I wonder if it has something
to do with the version?

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top