Excel sheet to Notes r5

[robhargreaves]

I have been trying to get sendmail from excel to work with lotus notes

I have thought about trying to use a mailto: but cant think how to do it. I can get it to work in Outlook no problem but we use Lotus Notes r5 at work.

My button in excel to outlook allows me to click, create a seperate excel file with that specific worksheet only placed in it. I use

.Cells.Copy
        .Cells.PasteSpecial Paste:=xlPasteValues

to only get values not formula.

The new file is called by worksheetname.xls

The Outlook message is addressed and headed in the code also the new file is attached to the email.

This is exactly what I wish to do with notes - is it possible?

By the way the user will be logged in to notes and it will be open.

Thanks for your help.

Rob

 

[Smitty]

Here's NateO's code for using Lotus Not(s) with Excel:

Sub LotusNots()
  ' 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

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

      ' 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"
        '   Select range of e-mail addresses
        Recipient = Sheets("E-Mail Addresses").Range("A2").Value
        MailDoc.SendTo = Recipient
        
        ans = MsgBox("Would you like to Copy (cc) anyone on this message?" _
            , vbQuestion & vbYesNo, "Send Copy")
        
            If ans = vbYes Then
                ccRecipient = InputBox("Please enter the additional recipient's e-mail address" _
                    , "Input e-mail address")
                MailDoc.CopyTo = ccRecipient
            End If
                
        MailDoc.Subject = "Pending Report"
            MailDoc.Body = _
        "Attached is a Pending Report.  Please acknowledge receipt."
    
    '   Select Workbook to Attach to E-Mail
    
        MailDoc.SaveMessageOnSend = True
            attachment1 = "C:\YourFile.xls" '    Required File Name
    
        If attachment1 <> "" Then
            On Error Resume Next
                Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
                Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\YourFile.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
    
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

Hope that helps,

Smitty