Results 1 to 2 of 2

Thread: Send Lotus Notes Email Using VBA

  1. #1
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0

    Send Lotus Notes Email Using VBA

    Dear Experts,

    I have an excel sheet in cell "c9" i have mentioned one email adderss. When i click the click button the attahcment should be send to the Recipient mentioned in cell c9

    I am using lotus notes for mails. Request you to assist.

    Excel sheet is attached for your reference.


    Thanks in Advance,

    R.Ramakrishnan
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Ramakrishnan,

    Welcome to ExcelFox !!

    I got this code from here


    Code:
    Option Explicit
    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 = ""
         
        vaRecipient = ThisWorkbook.Worksheets(1).Range("c9") '<<=== adjust to suit
         
         '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
         
         'Activate Excel for the user.
        AppActivate "Microsoft Excel"
        MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  2. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  3. How To Send Outlook Email Using VBA
    By mfaisalrazzak in forum Excel Help
    Replies: 7
    Last Post: 03-03-2013, 03:09 AM
  4. how to send each row by email
    By BARIS in forum Excel Help
    Replies: 22
    Last Post: 09-19-2012, 09:42 PM
  5. Send Outlook Email With Word Document
    By Murali K in forum Excel Help
    Replies: 2
    Last Post: 06-27-2012, 08:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •