Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Create Multi PDF's from Excel Workbook w/ Multi Worksheets and send via Lotus Notes?

  1. #1

    Create Multi PDF's from Excel Workbook w/ Multi Worksheets and send via Lotus Notes?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am extremely new at programming. I have used the following code and it is working well for my purposes of creating one pdf for each worksheet within my workbook. Is there a way to adapt or add to this so that I can send the pdf's to the email contained in a cell within each worksheet (different people)? The tricky thing is that I only have lotus notes to work with. Any help would be greatly appreciated.
    PrintToPDF_MultiSheet_Early.txt

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I haven't ever used (and don't have access to) Lotus Notes, but... there is an article here that shows how to send mail.

    So basically, so send your email, you're going to want a routine that does think, I think:

    1) Create the PDF
    2) Create the email body & attach the file
    3) Loop through a range of addresses adding them to the email
    4) Send the email

    As a suggestion, I would also do this:
    -Set up a named range that holds the email addresses you want to send to
    -Set up a named range for the email subject
    -Set up a named range for the email body

    That will make it easier to set up the components of the email without having to readjust the code all the time.

    Can you share the actual code you are using, as well as the range names?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    thank you-

    Thank you so much for your help. I am using your Print to pdf and it works great. I have attached a test file. You are indeed correct that I need to :
    1) Create the PDF (from excel worksheets within one workbook)
    2) Create the email body (text can be provided) & attach the file (email address located in cell within the file)
    3) Send the email (i can even do this part manually if the rest is done for me)

    I have found the following bits of information however I have no idea how to string them together to make it work.

    //windowssecrets.com/forums/showthread.php/147911-Need-VBA-code-to-attach-ACTIVE-PDF-to-Lotus-Notes-e-mail

    Thank you again for your help. Much appreciated
    test save and send 2 lotus.xlsm

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I've tried to knock up a quick one here, but again, I don't have Lotus so can't test. I'll need to work with you to try and figure out what works/doesn't here. Most likely the issue (if any) will be about how the array of addresses is passed to Lotus.

    You will need to add the PDFCreator and the Lotus references back in Tools->References, as they were giving me errors on what testing I could do.

    Add those back and give this a go. Let me know what happens.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I works all the way up until session initialization.
    - When I shut down lotus and then run the macro the lotus program opens up however still prompts me for a password then i get the error
    - If I run it with lotus open then I just get the error at that point.

    - If I disable the password section then I get a run time error 7060 where it looks like it is trying to find someone else nsf file instead of mine. I deleted all the nsf files on my computer to see if that would help but it is not. I have sent a note to someone on our IT to see if there might be a reason for that.
    Any ideas?

  6. #6
    Status Update.

    I was able to figure out the password and user name issue. I have disabled password and run it with lotus open. I am now getting an error at
    MailDoc.CREATERICHTEXTITEM ("Attachment")
    error 7368 rich text attachment already exists

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Let me see if I can call in someone who has actually used Lotus Notes before...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    I haven't use Not(s) in a few years (thankfully), but here's the core code that I used to use:

    Code:
    Sub LotusNotsCoreCode()
        '     Send an e-mail & attachment using Lotus Not(s)
        '     Original Code by Nate Oliver (NateO)
        '     Declare Variables for file and macro setup
    
        Dim UserName As String
        Dim MailDbName As String
        Dim Recipient As String
        Dim ccRecipient As String
        Dim ans As String
        Dim Attachment1 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 = ActiveWorkbook.Name
        
            If Attachment1 <> "" Then
                On Error Resume Next
                    Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
                    Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", ActiveWorkbook.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
        
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
        
    errorhandler1:
        
            Set Maildb = Nothing
                Set MailDoc = Nothing
                    Set AttachME = Nothing
                Set Session = Nothing
            Set EmbedObj1 = Nothing
        
    End Sub
    It used to work fine for single attachments, but I never experimented with multiple. I suppose that you could create an attachment list and loop through it. Most likely you'd need to create a string from that list, then remove the final separator that will be left over at the end. E.G.

    Code:
    For i = 1 to 10
      strAttachment = strAttachment & "; " & Cells(i,"A").Value
    Next i
    
    strAttachment = Left(strAttachment, Len(strAttachment = strAttachment) - 1)
    I believe that Lotus Notes still has a developer forum, where this code was originally posted, so you might want to check there.

    HTH,

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hmm...

    Okay, so based on the Nate's code that Smitty posted (pretty similar to what I located earlier), I think there is a duplicate line in this section:
    Code:
        If Attachment <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
    Since the CreateRichTextItme is already done in the Set statement, I think the second instance (right before End If) is not required. It certainly doesn't appear in Nate's code.

    Try putting an apostrophe (single quote) in front of that line of code so that it looks like this:
    Code:
        If Attachment <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment")
    '        MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
    Then give it a run. (For reference, that comments the line so it won't be executed at run time.)

    Let us know if that fixes it up.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10

    we are creating and sending

    Thanks, this is great. We are creating and sending
    1. The macro is creating all the pdfs
    2. It is sending email

    The issue is now that it is sending worksheet "test 3" to all receipients at in one email instead of sending worksheet test 2 - specific receipiant
    worksheet test 3 - specific receipiant


    Thanks again for all your help on this.

    test save and send 3 lotus.xlsm
    Last edited by surkos; 2012-10-25 at 02:41 PM. Reason: Add attachment

Page 1 of 2 1 2 LastLast

Posting Permissions

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