Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change attach workbook code to worksheet
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 _ '& 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change attach workbook code to worksheet
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" wrote in message oups.com... 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 _ '& 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change attach workbook code to worksheet
But if i wanted to change it (from send the workbook to the
worksheet), would i just name the worksheet or is there any preceding code changes needed? Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attach code | Excel Worksheet Functions | |||
NEED VB CODE TO REMOVE INITIALS FROM A NAME AND ATTACH IT TO THE END OF THE NAME | Excel Programming | |||
code to attach files | Excel Programming | |||
Code Conflicts With Worksheet Change Code | Excel Programming | |||
How do I properly attach vba code to a workbook | New Users to Excel |