Create Email but don't Send - Lotus Notes

[Sean Stevens]

I have the following code which creates an email and sends it. Works fine.

However, I was wondering if it was possible for the email to be created and not sent. This will give the user the oppertunity to enter some comments into the body of the email. I know I can do this through the use of an inputbox, but I really want them to enter the comments via their email client.

Sub EmailFile ()
' Declare Variables for file and macro setup

Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object

' 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

' Create New Mail and Address Title Handlers

Set MailDoc = Maildb.CreateDocument

MailDoc.Form = "Memo"
MailDoc.SendTo = "email@web.com"


MailDoc.Subject = "BEN New Project"
MailDoc.Body = _
"Attached is a new BEN Project Request. Please let me know when it has been setup."

' Select Workbook to Attach to E-Mail

MailDoc.SaveMessageOnSend = False
attachment1 = "C:\Temp\New BEN Project.xls" ' Required File Name

If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\Temp\New BEN Project.xls", "") 'Required File Name
On Error Resume Next
End If

MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.Send 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing

errorhandler1:

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

Thanks, Sean.

 

[NateO]

Hello Sean,

However, I was wondering if it was possible for the email to be created and not sent. This will give the user the oppertunity to enter some comments into the body of the email. I know I can do this through the use of an inputbox, but I really want them to enter the comments via their email client.

Change:

MailDoc.Send 0, Recipient

to

Call MailDoc.Save(True, True)

Note ( ), you could just as easily add comments in a worksheet cell!

 

[Sean Stevens]

Nate - Thanks for replying, but that code actually just saves a copy of the email in the sent folder.

I need to have an email created ready to be sent, but allow the user to enter some comments into the body of the email. The user will then send the email manually.

Thanks, Sean.

 

[Von Pookie]

Perhaps you could edit the code for some input boxes or something for them to enter what they need?

Just a thought

 

[NateO]

Whoops, should have mentioned, untested! I don't have LN handy at the moment. Oh, pull the following as well:

MailDoc.PostedDate = Now()

As in delete it. Should go to Draft folder.

 

[Sean Stevens]

Yeah - I've played around with that, but I'm not sure how to create an input box, which enables a user to enter formatted text inside. For example, the input box to be quite large and when the user presses enter it creates a new line and doesn't 'OK' the input box.

Any ideas??

 

[NateO]

The least annoying thing to do in my opinion to create input cells and append them to your message body...

 

[Smitty]

For example, the input box to be quite large and when the user presses enter it creates a new line and doesn't 'OK' the input box.

I have a similar situation and created a User Form (called frmE_Mail) that contains a Text Box (tb1). In my Case, Message = tb1.Text.

    '   Declare a Message Subject
    Subject = InputBox("Please Enter the Message Subject", "Message Subject")
        MailDoc.Subject = Subject
            If Subject = "" Then MailDoc.Subject = "Volunteer Update"
        frmE_Mail.Show
    MailDoc.Body = Message

Hope that helps,

Smitty

 

[Sean Stevens]

For example, the input box to be quite large and when the user presses enter it creates a new line and doesn't 'OK' the input box.

I have a similar situation and created a User Form (called frmE_Mail) that contains a Text Box (tb1). In my Case, Message = tb1.Text.

    '   Declare a Message Subject
    Subject = InputBox("Please Enter the Message Subject", "Message Subject")
        MailDoc.Subject = Subject
            If Subject = "" Then MailDoc.Subject = "Volunteer Update"
        frmE_Mail.Show
    MailDoc.Body = Message

Hope that helps,

Smitty

Smitty - Thanks for pointing me in the right direction - works exactly how I want it to.

 

[Miss Pocahontas]

NateO:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for the original code below which attaches and sends the active workbook through Lotus Notes.<o></o>
<o></o>
I removed the:<o></o>
<o></o>
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.Send 0, Recipient<o></o>
<o></o>
...and replaced it with:<o></o>
<o></o>
Set workspace = CreateObject("Notes.NotesUIWorkspace")
Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")<o></o>
(I found this in http://www.ozgrid.com/forum/showthread.php?t=18259)<o></o>
<o></o>
...which keeps the email message open instead of sending.<o></o>
<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>
<o></o>
The only thing I'm having trouble with is:<o></o>
<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>
<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 VBA. I just want the code to run and open the email message without that dialog box.<o></o>
<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>
<o></o>
NateO, below is your code, which I modified per my comments above:<o></o>
<o></o>
Sub LotusNotsSendActiveWorkbook()
'Send an e-mail & attachment using Lotus Not(s)
'Original Code by Nate Oliver (NateO)<o></o>
'Declare Variables for file and macro setup<o></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></o>
With Application
.ScreenUpdating = False
.DisplayAlerts = False<o></o>
<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>
<o></o>
' Create New Mail and Address Title Handlers
Set MailDoc = Maildb.CreateDocument<o></o>
MailDoc.Form = "Memo"<o></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></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>
<o></o>
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True
MsgBox ActiveWorkbook.FullName
attachment1 = ActiveWorkbook.FullName<o></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>
<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>
<o></o>
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing<o></o>
<o></o>
.ScreenUpdating = True
.DisplayAlerts = True
End With<o></o>
<o></o>
errorhandler1:<o></o>
<o></o>
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing<o></o>
<o></o>
End Sub