A sheet has a Name property but not a FullName property. You probably want
ActiveWorkbook.FullName.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to change the attach workbook code to the worksheet only.
> Getting error on this line....
>
>
> stAttachment = ActiveSheet.FullName
>
>
>
> Sub SendWithLotus()
> Dim noSession As Object, noDatabase As Object, noDocument As
> Object
> Dim obAttachment As Object, EmbedObject As Object
> Dim stSubject As Variant, stAttachment As String
> Dim vaRecipient As Variant, vaMsg As Variant
>
> Const EMBED_ATTACHMENT As Long = 1454
> Const stTitle As String = "Active workbook status"
> Const stMsg As String = "The active workbook must first be saved
> " & vbCrLf _
> & "before it can be sent as an attachment."
> 'Check if the active workbook is saved or not
> 'If the active workbook has not been saved at all.
> If Len(ActiveWorkbook.Path) = 0 Then
> MsgBox stMsg, vbInformation, stTitle
> Exit Sub
> End If
> 'If the changes in the active workbook have been saved or not.
> If ActiveWorkbook.Saved = False Then
> If MsgBox("Do you want to save the changes before sending?", _
> vbYesNo + vbInformation, stTitle) = vbYes Then _
> ActiveWorkbook.Save
> End If
> 'Get the name of the recipient from the user.
> Do
> vaRecipient = "jeremy aldridge"
> 'Prompt:="Please add name of the recipient such as:" & vbCrLf
> _
> '& "(E-Mail Removed) or just the name if internal mail within
> Unity.", _
> 'Title:="Recipient", Type:=2)
> Loop While vaRecipient = ""
> 'If the user has canceled the operation.
> If vaRecipient = False Then Exit Sub
> 'Get the message from the user.
> Do
> vaMsg = "Hi, Please make the following adjustments to the DOR
> for PCP.... Thanx"
> 'Prompt:="Please enter the message such as:" & vbCrLf _
> '& "Enclosed please find the weekly report.", _
> 'Title:="Message", Type:=2)
> Loop While vaMsg = ""
>
> 'If the user has canceled the operation.
> If vaMsg = False Then Exit Sub
> 'Add the subject to the outgoing e-mail
> 'which also can be retrieved from the users
> 'in a similar way as above.
> Do
> stSubject = "PCP Out of Production Report"
> 'Prompt:="Please add a subject such as:" & vbCrLf _
> '& "Weekly Report.", _
> 'Title:="Subject", Type:=2)
> Loop While stSubject = ""
> 'Retrieve the path and filename of the active workbook.
> stAttachment = ActiveSheet.FullName
> 'Instantiate the Lotus Notes COM's Objects.
> Set noSession = CreateObject("Notes.NotesSession")
> Set noDatabase = noSession.GETDATABASE("", "")
> 'If Lotus Notes is not open then open the mail-part of it.
> If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
> 'Create the e-mail and the attachment.
> Set noDocument = noDatabase.CreateDocument
> Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
> Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "",
> stAttachment)
> 'Add values to the created e-mail main properties.
> With noDocument
> .Form = "Memo"
> .SendTo = vaRecipient
> .Subject = stSubject
> .Body = vaMsg
> .SaveMessageOnSend = True
> End With
> 'Send the e-mail.
> With noDocument
> .PostedDate = Now()
> .Send 0, vaRecipient
> End With
>
> 'Release objects from the memory.
> Set EmbedObject = Nothing
> Set obAttachment = Nothing
> Set noDocument = Nothing
> Set noDatabase = Nothing
> Set noSession = Nothing
>
> 'Activate Excel for the user.
> AppActivate "Microsoft Excel"
> MsgBox "The e-mail has successfully been created and
> distributed.", vbInformation
> End Sub
>
Similar Threads
