Hello,

I am trying to incorporate a piece of code that will attach an Excel file to a Lotus Notes email. I have found a code that works beautifully except for one thing: I do NOT want the email to send out automatically. I'd like the code to activate Lotus Notes and then let the user write a full message, change recipients, decide if they'd like to send it or not, etc. Surprisingly, it is not as easy as deleting the last few lines which sends the email (that seems to break the code altogether).

Could someone please help me troubleshoot this issue?

Thanks,

Evan


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 = Application.InputBox( _
            Prompt:="Please add name of the recipient such as:" & vbCrLf _
            & "will@yahoo.co.uk 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 = Application.InputBox( _
            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 = Application.InputBox( _
            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 = ActiveWorkbook.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

   AppActivate "Lotus Notes"
   
   MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub