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

    Need VBA code to attach ACTIVE PDF to Lotus Notes e-mail


    I have been using the VBA code below to attach the ACTIVE Excel file to the Lotus Notes email. I have recently created another macro that does a "file save as" function to save the ACTIVE Excel file as a PDF file. The macro below will not attach the ACTIVE PDF file... it gives me an error and stops at:

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

    I'm guessing I need to change the wording from "ActiveWorkbook.FullName" to something else because the active PDF is what I'd like to attach.

    Any help is very much appreciated!


    Sub SendEmail()

    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)

    vaRecipient = Range("A3")

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Melbourne, Victoria, Australia
    Thanked 295 Times in 267 Posts
    You will need to pass in the path where the PDF is saved.

    What code do you have that creates the PDF? What filename is given to the file in that piece of the code? You need to define that PDF file path as a variable and pass it to the SendEmail sub you showed us.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 465 Times in 382 Posts
    Assuming the PDF has already been created and has the same name as the workbook (except for the extension), you should be able to use:
    stAttachment = Split(ActiveWorkbook.FullName, ".")(0) & ".pdf"
    Note that this won't work if there are periods in the filename (not a good practice, anyway)

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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