I have the following from a post. Can it be revised to send only the active worksheet rather than the whole workbook?
Originally Posted by Miss Pocahontas
NateO:<o
></o
<o
Thanks for the original code below which attaches and sends the active workbook through Lotus Notes.<o
<o
I removed the:<o
<o
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.Send 0, Recipient<o
<o
...and replaced it with:<o
<o
Set workspace = CreateObject("Notes.NotesUIWorkspace")
Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")<o
(I found this in http://www.ozgrid.com/forum/showthread.php?t=18259)<o
<o
...which keeps the email message open instead of sending.<o
<o
Also, I changed the Recipient to ccRecipient, so the user can type whichever email address he needs to address to from his custom drop-down list within Lotus Notes, and type his message in the body of the email. This is the setup that is most useful for my purposes.<o
<o
The only thing I'm having trouble with is:<o
<o
1. After the user revises the workbook, but forgets to click save, if the user runs the code, it will send the workbook WITHOUT the revisions. How can I integrate coding within this same module to save the workbook before attaching it to the email?<o
<o
2. How can I disable the dialog box that pops up, which the user needs to click OK? It's the dialog box that contains the FilePath of the workbook. I tried setting DisplayAlerts to False, but that didn't work. I'm new to <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>. I just want the code to run and open the email message without that dialog box.<o
<o
3. When the code is run, the Lotus Notes email message window remains in the background behind all the other open windows. How can I get it to pop up in front of all the other windows automatically?<o
<o
NateO, below is your code, which I modified per my comments above:<o
<o
Sub LotusNotsSendActiveWorkbook()
'Send an e-mail & attachment using Lotus Not(s)
'Original Code by Nate Oliver (NateO)<o
'Declare Variables for file and macro setup<o
Dim UserName As String, MailDbName As String, ccRecipient As String, attachment1 As String
Dim Maildb As Object, MailDoc As Object, AttachME As Object, Session As Object
Dim EmbedObj1 As Object<o
With Application
.ScreenUpdating = False
.DisplayAlerts = False<o
<o
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = _
Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If<o
<o
' Create New Mail and Address Title Handlers
Set MailDoc = Maildb.CreateDocument<o
MailDoc.Form = "Memo"<o
' Select range of e-mail addresses
ccRecipient = Sheets("EmailSheet").Range("B2").Value
' Or send to a signle address
' Recipient = "yourname@isp.com"
MailDoc.CopyTo = ccRecipient<o
' Subject & Body stored in a**worksheet
MailDoc.Subject = Sheets("EmailSheet").Range("C2").Value
' MailDoc.Body = Sheets("EmailSheet").Range("ENTER CELL OF BODY").Value
' These can be entered here manually instead
' MailDoc.Subject = "Check this out!"
' MailDoc.Body = "Made you look!"<o
<o
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True
MsgBox ActiveWorkbook.FullName
attachment1 = ActiveWorkbook.FullName<o
If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.EmbedObject(1454, "attachment1", ActiveWorkbook.FullName, "")
On Error Resume Next
End If<o
<o
'Displays email message without sending; user needs to click Send
Set workspace = CreateObject("Notes.NotesUIWorkspace")
Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")<o
<o
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing<o
<o
.ScreenUpdating = True
.DisplayAlerts = True
End With<o
<o
errorhandler1:<o
<o
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing<o
<o
End Sub
