Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel Macro to Send Email via Lotus Notes (Excel 2002)

    Hi,
    I found the following code at http://www.fabalou.com/VBandVBA/lotusnotesmail.asp. I've tweaked it a bit, and would like to have the ".SendTo =" portion of the code to read from Sheet1.Range("A3"), which is in Excel of course. As the code is currently, I have to hard code an email address into this area. Any ideas would be appreciated as always!
    Thanks!
    Lana

    Sub TestingLotusNotesEmail()

    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

    'Retrieve the path and filename of the active workbook.
    stAttachment = ActiveWorkbook.FullName

    'Initiate 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 = "jane.doe@corp.com"
    .Subject = "Phantom Stock Reports"
    .Body = "Hi there Lana. This is fun!"
    .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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro to Send Email via Lotus Notes (Excel 2002)

    Change the line

    .SendTo = "jane.doe@corp.com"

    to

    .SendTo = Worksheets("Sheet1").Range("A3")

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel Macro to Send Email via Lotus Notes (Excel 2002)

    Hmmm... I must be doing something wrong... it accepts the code, however now it says that there is no name to send to and highlights the ".Send 0, vaRecipient" portion of the code. Any ideas? I've attached the worksheet with the macro in it.
    Thanks!!!
    Lana
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel Macro to Send Email via Lotus Notes (Excel 2002)

    I got it to work...
    I had to define the vaRecipient as follows:
    vaRecipient = Worksheets("lana").Range("A3")
    Also, I replaced the SendTo: part of the code with .SendTo = vaRecipient
    Yahoo...
    Thanks for the help Hans!
    Lana

Posting Permissions

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