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

S

surkos

New member
Joined
Oct 19, 2012
Messages
9
Reaction score
0
Points
0
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.
View attachment PrintToPDF_MultiSheet_Early.txt
 
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?
 
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
View attachment test save and send 2 lotus.xlsm
 
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.
 

Attachments

  • test save and send 2 lotus.xlsm
    29.3 KB · Views: 48
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?
 
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
 
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,
 
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.
 
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.

View attachment test save and send 3 lotus.xlsm
 
Last edited:
Ahhh.... sorry missed that requirement.

Okay, so what you're trying to set up is a distribution grid then? Something like this?

10-25-2012 7-49-01 AM.png

Now some questions then...
  • PDF Creation
    • Should each worksheet be printed into a separate PDF file, or
    • Should each user get a file that has all the sheets they are entitled to in one file?
  • If the PDF's are all being created in separate files how to handle the email?
    • Have one email created to those four people with the single attachment, or
    • Have four individual emails created with all attachments that user is entitled to. (I.e. if user 2 gets sheets 2, 3, 5, should they get one email direct to them with all those files attached?)
  • What path should the PDF's be saved to? Same as the workbook with this code?
  • Do you want the PDF's deleted from the hard drive after they've been emailed?
  • Will the subject and body of the email be the same for all users or customized?

The largest amount of overhead here is in the creation of the PDF files, so ultimately you'd want to minimize that. We do something similar to this and create a customized set of financial statements at every month end for each user in a list, and it takes a while to run. But in that case, I can't send 40 files to each user as that would be overwhelming for printing. Having a customized PDF to that user is much more friendly.

A lot of questions, but I need you to really think about the rules of how you want this handled. If you can work through that, I can help you make the coding adjustments needed.
 
    • PDF Creation
      • Should each worksheet be printed into a separate PDF file? Yes, then sent to a specific recipient whose details are located within the file.
      • Should each user get a file that has all the sheets they are entitled to in one file? No this is not required one pdf per email.
    • If the PDF's are all being created in separate files how to handle the email?
      • Have one email created to those four people with the single attachment No
      • Have four individual emails created with all attachments that user is entitled to. (I.e. if user 2 gets sheets 2, 3, 5, should they get one email direct to them with all those files attached?) Each recipient will only get one pdf. The recipient’s details are located within the worksheet as well as on a summary page at the end however they are not to receive the summary page.

    • What path should the PDF's be saved to? Same as the workbook with this code? Yes what is happening now is fine. Each worksheet is being created into a separate pdf. The pdf is being named by the worksheet name and the pdf's are being saved in the same file where the worksheet is. Because they have unique names and unique recipients this works out well.

    • Do you want the PDF's deleted from the hard drive after they've been emailed? No, this is not required as we file them for backup.

    • Will the subject and body of the email be the same for all users or customized? All users can receive the same subject and body as long as I can adjust this content month by month.

    Summary:
    Currently with your original code I was able to create each worksheet into a separate pdfs that were titled by the worksheet name (there are 178 of them it takes about 5 minutes) and stored in the same location as the original workbook.
    With the link to lotus notes the email was being sent but only one worksheet was being sent out and it was being sent out to everyone.

    The goal is to send out monthly statements for billing to the 178 departments.
 
I won't have time to take a look at this until the weekend, but I will.

Thanks for the observations Ken.
 
Let's give this one a shot.

I've recoded a few parts of this, but again, without the ability to test I wasn't able to check for errors. (I also had a 9 year old walk in in the middle of it and ask me questions!) I think I've got it put together correctly, but if there are errors, I'll need to know what the errors are and what lines they are on. I've also renamed all the procedures to avoid conflicts so you can just drag and drop to your file.

Again, you'll need to add the PDFCreator and Lotus references back.

So here's what you should need to do...
  • Open the file you want to have the code in
  • Open the attached file
  • Copy modPDF_Email into your file
  • Copy the ControlPanel worksheet into your file

That should be all of the pieces you need. The control panel has a button to fire the code.

Verify that every worksheet has the email address of your recipient in cell A1. If not, you'll need to go into modPDF_Email, find SendLotusMail, and update this piece to show the correct range:
Code: 
'Record recipient
sRecipient = ws.Range("C1")

Beyond that, if I've done everything right, it should work.

During the recoding, I tried to optimize it for speed as well. Specifically I:
  • Set it up to create all PDF's first, then send all the emails second. This means only one call to create invoke the PDF application, and only one call to invoke Lotus.
  • I shut off screen updates, as re-drawing can take overhead. So if your screen goes grey in the process, don't worry about it.
  • So that you can see that things are still working, I did add a progress indicator that will show up in the statusbar (bottom left) to tell you how far through it is.

Give it a go and let me know how it fares.
 

Attachments

  • test save and send 4 lotus.xlsm
    30.6 KB · Views: 18
Thank you so much it worked. Well it worked on your test sheet. I got an error the first time I ran it but I ended up disabling the following line and then it worked great.
Code: 
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
[COLOR=#ff0000]' PrintToPDF_MultiSheet_Early = sPDFPath & sPDFName
[/COLOR]Exit Function

I tried to transfer the controlpanel sheet by move create copy into my document however I got an error that the source book contains fewer rows. When I run the macro after copying and pasting the document the code gets stuck at the following line.

Code: 
'Record base path to PDF
Set wb = ActiveWorkbook
sPDFPath = wb.Path & Application.PathSeparator
[COLOR=#ff0000]sSubject = Worksheets("ControlPanel").Range("rngSubject[/COLOR]")
sBody = Worksheets("ControlPanel").Range("rngSubject")

Any advise? I do not have lotus at home so I will have to continue this on tuesday when I return to the office.
Thanks again for all your help.
s
 
Ah, my bad on the first one. Yes, delete the PrintToPDF line should be fine. That's left over from when I converted it from a function.

Re the next part, I actually see a mistake in there too. The last line is referring to rngSubject, and it should be rngBody. That part should read:
Code: 
sBody = Worksheets("ControlPanel").Range("rngBody")

Both rngSubject and rngBody are named ranges. To set them up in your workbook:
  • Choose a range where you want to enter the email subject and select it
  • The next step differs depending on your version
    • In Excel 97-2003 go to Insert-->Name-->Define
    • In Excel 2007+ go to Formulas-->Define Name
  • Define the name as rngSubject
  • Click OK

Do the exact same thing again for whatever range you want the email body to be in, only this time call it rngBody.

Apologies that the sheet didnt' copy in. That's actually part of the reason I tried to get you to do it that way as the names would have come over too. I had it saved in the new file format though, and yours is still in Excel 2003 format. I've attached an xls version if you'd rather just import that sheet.
 

Attachments

  • test save and send 4 lotus.xls
    54 KB · Views: 27
Doing my little happy dance. It works like a charm......:clap2: Thank you so much for all your help.

Sarah
 
You must log in or register to reply here.
Back
Top