Hi,
I use Lotus Notes as email and have set up a macro in Excel using the
information from XL-Dennis on VSTO /.Net & Excel to create my email macro and
have finally got the macro to work without getting an error message. The
problem that I have is that when I click the button to email the form, I
receive a message stating that the email has been sent, but I have not
designated an email address for the email to go to. I would like to choose
the email address each time before I email the form.

Can someone please provide me with the exact language to include in my macro
that would allow me to do so. Thanks.

pdaws wrote...
....
Can someone please provide me with the exact language to include in my macro
that would allow me to do so. *Thanks.

No, not without seeing the code you're using or knowing the Lotus
Notes object model, which I'd guess most Excel users don't know.

A guess: you need to add code to your macro that checks whether the e-
mail recipient field/property is empty, and if it is, the macro either
prompts you to enter the recipient's e-mail address which the macro
then incorporates into the e-mail and sends it or aborts with a
message telling you you need to provide the recipient's e-mail
address.

Harlan,

Below is the code that I am using. I am not sure how to proceed from here.
Hopefully this helps and yes you are right, I do not know what Notes object
model I am using. Thanks.

Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "c:\Temp"

Const stSubject As String = "American Express Request Form Approval Required"

Const vaMsg As Variant = "Please OPEN this AMEX Request." & vbCrLf & _
"And click your APPROVAL link on the bottom of the
form, and click SAVE," & vbCrLf & _
"Then email this AMEX Request to the next Approver"

Dim stFileName As String
Dim vaRecipients As Variant

Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String

'Variables for E-mailaddress.
Dim vaMailTo As Variant
Dim vaCopyTo As Variant
Dim vaBCC As Variant

'Copy the active sheet to a new temporarily workbook.
With ActiveSheet
.Copy
'Populate the variant variables with mailaddress.
vaMailTo = .Range("A2:A4").Value
vaCopyTo = .Range("B2:B4").Value
vaBCC = .Range("C2:C4").Value
stFileName = .Name
End With

'Create the full path and name of the workbook.
stAttachment = stPath & "\" & stFileName & ".xls"

'Save and close the temporarily workbook.
With ActiveWorkbook
.SaveAs stAttachment
.Close
End With

'Create the list of recipients.
vaRecipients = ")

'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 noAttachment = noDocument.CreateRichTextItem("stAttachment")
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "",
stAttachment)

'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipients
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaRecipients
End With

'Delete the temporarily workbook.
Kill stAttachment

'Release objects from memory.
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

MsgBox "This AMEX Request Form e-mail has successfully been created and
distributed", vbInformation

End Sub

"pdaws" wrote:

Hi,
I use Lotus Notes as email and have set up a macro in Excel using the
information from XL-Dennis on VSTO /.Net & Excel to create my email macro and
have finally got the macro to work without getting an error message. The
problem that I have is that when I click the button to email the form, I
receive a message stating that the email has been sent, but I have not
designated an email address for the email to go to. I would like to choose
the email address each time before I email the form.

Can someone please provide me with the exact language to include in my macro
that would allow me to do so. Thanks.