Macro for attaching PDF to Lotus Notes email. PDF name is variable.

I have written a macro that when I am pricing for a client, I click one button on the page and the following happens.

1. It saves the internal prices from the sheet called "internal" as a PDF, naming that PDF according to the data in cell "BD2".
2. It saves a PDF of the client prices from a sheet called "Client Quote" as a PDF, naming that PDF according to the data in cell "AY8"
3. It opens up a new mail in Notes, inserting TO: email and CC:emails. It then inserts a subject title, also taken from cell "AY8" and body text and signature from various cells within the workbook.

I have all of this bit working now.

I now need it to attach the client price PDF to the email also, but for the life of me I cant work out how to do that. I have tried recording macros and copying that code into this macro but I am completely lost.

Any help you can give me would be very much appreciated.

Sub Save_and_email_PDF()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim WordApp As Object
    Dim subject As String
    Dim EmailAddress As String
    Dim s(1 To 21) As String
     
    subject = Worksheets("Client Quote").Range("AY8")
    EmailAddress = Worksheets("Client Quote").Range("ay10")
     'Debug.Print subject
     
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GETDATABASE("", "")
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
     
    Set NDoc = NDatabase.CREATEDOCUMENT
     
    With NDoc
        .SendTo = EmailAddress
        .CopyTo = "test@test.com, " & "test1@test.com, " & "test2@test.com"
                .subject = subject
        s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
        s(2) = ""
        s(3) = "Many Thanks for your enquiry"
        s(4) = ""
        s(5) = "Please find attached your quotation for your request" & " " & " : - " & "'" & Worksheets("internal").Range("j14") & "'"
        s(6) = ""
        s(7) = "If you would like to go ahead with this order, please let  me know and I will send you a template, artwork guidelines and  procedures for processing your order."
        s(8) = ""
        s(9) = "Kind Regards"
        s(10) = ""
        s(11) = Worksheets("sheet3").Range("a58")
        s(12) = Worksheets("sheet3").Range("a59")
        s(13) = ""
        s(14) = "Z-CARD® PocketMedia® Solutions"
        s(16) = ""
        s(17) = Worksheets("sheet3").Range("a61")
        s(18) = Worksheets("sheet3").Range("a62")
        s(19) = ""
        s(20) = "7-11 St. Johns Hill, London, SW11 1TN"
        s(21) = "The PocketMedia® Agency | www.zcard.co.uk"
           
              
        .body = Join(s, vbCrLf) & " "
        
        .Save True, False
    End With
     
    NUIWorkSpace.EDITDOCUMENT True, NDoc
     
    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    
        Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf", _
        OpenAfterPublish:=True
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Internal Prices\" & _
        ActiveSheet.Range("BD2").Value & ".pdf", _
        OpenAfterPublish:=False
        
        End Sub

Open in new window

h3rm1t9536Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I have not used Lotus Notes for quite some time, but I believe to add an attachment the Visual Basic for Applications code is something like this:

  Dim objAttach                                         As Object
  Dim objEmbed                                          As Object

  Set objAttach = NDoc.CreateRichTextItem("attachment1")
  
  Set objEmbed = objAttach.EmbedObject(1454&, _
                                       "attachment1", _
                                       "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
                                       Worksheets("client quote").Range("AY8").Value & ".pdf", _
                                       "")

Open in new window


Where NDoc is as you defined in your code: Set NDoc = NDatabase.CREATEDOCUMENT

The 'Dim' statements can go at the top of your subroutine (with the other statements of this type).

The two other lines (Set obj...) should go before this statement in your existing code:

NUIWorkSpace.EDITDOCUMENT True, NDoc


However, I see in your code listing that you create the ".pdf" file after the e-mail has been created.

You would need to create the required attachment prior to the e-mail being created (& before the code I suggested is executed).

It would also be 'tidy' to set the two new objects to Nothing, prior to the conclusion of the routine (like you have already for three other objects):

  Set objAttach = Nothing
  Set objEmbed = Nothing
  Set NDoc = Nothing
  Set WordApp = Nothing
  Set NSession = Nothing

Open in new window


I hope that made sense! :)
 
Like I said, I have not used Lotus Notes recently, & do not have the provision to do so now, so the parameters to the EmbedObject() method may not be exactly what is required (as I cannot test locally).

Please do let me know what the outcome of the proposal is though.

Thank you.

BFN,

fp.
0
h3rm1t9536Author Commented:
Hi fp

I have put the code in as follows and its still not attaching the PDF.

Did I put the code correctly?

Its all very new to me using excel in this way.

Sub Save_and_email_PDF()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim WordApp As Object
    Dim subject As String
    Dim EmailAddress As String
    Dim s(1 To 21) As String
    Dim objAttach As Object
    Dim objEmbed As Object
    
    Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\2. Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf", _
        OpenAfterPublish:=False
        
          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\3. Internal Prices\" & _
        ActiveSheet.Range("BD2").Value & ".pdf", _
        OpenAfterPublish:=False
     
    subject = Worksheets("Client Quote").Range("AY8")
    EmailAddress = Worksheets("Client Quote").Range("f3") & " " & "<" & Worksheets("Client Quote").Range("ay10") & "> ,"
         
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GETDATABASE("", "")
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
        
    Set NDoc = NDatabase.CREATEDOCUMENT
     
    With NDoc
        .SendTo = EmailAddress
        .CopyTo = "digitalprint@zcard.com, "
                .subject = subject
        s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
        s(2) = ""
        s(3) = "Many Thanks for your enquiry"
        s(4) = ""
        s(5) = "Please find attached your quotation for your request" & " " & " : - " & "'" & Worksheets("internal").Range("j14") & "'"
        s(6) = ""
        s(7) = "If you would like to go ahead with this order, please let  me know and I will send you a template, artwork guidelines and  procedures for processing your order."
        s(8) = ""
        s(9) = "Kind Regards"
        s(10) = ""
        s(11) = Worksheets("sheet3").Range("a58")
        s(12) = Worksheets("sheet3").Range("a59")
        s(13) = ""
        s(14) = "Z-CARD® PocketMedia® Solutions"
        s(16) = ""
        s(17) = Worksheets("sheet3").Range("a61")
        s(18) = Worksheets("sheet3").Range("a62")
        s(19) = ""
        s(20) = "7-11 St. Johns Hill, London, SW11 1TN"
        s(21) = "The PocketMedia® Agency | www.zcard.co.uk"
           
              
        .body = Join(s, vbCrLf) & " "
        
        .Save True, False
    End With
    
    Set objAttach = NDoc.CreateRichTextItem("attachment1")
    Set objEmbed = objAttach.EmbedObject(1454&, _
    "attachment1", _
                                       "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
                                       Worksheets("client quote").Range("AY8").Value & ".pdf", _
                                       "")
     
    NUIWorkSpace.EDITDOCUMENT True, NDoc
     
    Set objAttach = Nothing
    Set objEmbed = Nothing
    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    
            
  End Sub

Open in new window

0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

All looks OK, about from, perhaps, the order of the saving of the e-mail & the attaching of the file.

I would perhaps try this (snippet of the full code) re-ordering...

    With NDoc
        .SendTo = EmailAddress
        .CopyTo = "digitalprint@zcard.com, "
                .subject = subject
        s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
        s(2) = ""
        s(3) = "Many Thanks for your enquiry"
        s(4) = ""
        s(5) = "Please find attached your quotation for your request" & " " & " : - " & "'" & Worksheets("internal").Range("j14") & "'"
        s(6) = ""
        s(7) = "If you would like to go ahead with this order, please let  me know and I will send you a template, artwork guidelines and  procedures for processing your order."
        s(8) = ""
        s(9) = "Kind Regards"
        s(10) = ""
        s(11) = Worksheets("sheet3").Range("a58")
        s(12) = Worksheets("sheet3").Range("a59")
        s(13) = ""
        s(14) = "Z-CARD® PocketMedia® Solutions"
        s(16) = ""
        s(17) = Worksheets("sheet3").Range("a61")
        s(18) = Worksheets("sheet3").Range("a62")
        s(19) = ""
        s(20) = "7-11 St. Johns Hill, London, SW11 1TN"
        s(21) = "The PocketMedia® Agency | www.zcard.co.uk"
           
              
        .body = Join(s, vbCrLf) & " "
        
    Set objAttach = NDoc.CreateRichTextItem("attachment1")
    Set objEmbed = objAttach.EmbedObject(1454&, _
                                       "attachment1", _
                                       "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
                                       Worksheets("client quote").Range("AY8").Value & ".pdf", _
                                       "")

        .Save True, False
    End With

Open in new window

   

I have simply moved the .SaveEnd With statements beneath the two Set statements for the new objects.

Like I said, I am probably far from the best candidate to assist here (given my lack of access to the same environment), so if we are not successful I can post a comment to some of the (more) frequent "Experts" within the Microsoft Excel Topic Area to see if any have (more recent) experience than I do, &/or can assist further.

Fingers-crossed for you here, though :)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

h3rm1t9536Author Commented:
Hi fp

Its still not working, but I have noticed something that may be having a bearing on this function.

When I am saving the variable named PDF in step 1, it is saving to the destination folder and naming as it should.

However, when I then drag and drop that file into our company MIS program, its dropping the file in, but only picking up the name "Quotation No" and not the variable elements.

Could this be the problem in as much as the macro is looking for a file it cant find?
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

Yes, that could be relevant.  Although, sorry, I'm not sure where "Quotation No" is within your process.  Is that the value in cell [BD2], or [AY8], or another cell?

Would you mind changing your code to save the file in a folder that does not include any space characters?

Presently you have "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & Worksheets("client quote").Range("AY8").Value & ".pdf".

Could you please try saving to a fixed (explicit) filename of, say:

"C:\Users\joeellis\Desktop\Test.pdf"

Then use that filename as the attachment to the e-mail?

Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
h3rm1t9536Author Commented:
Hi fp

Yes thats worked, so it is a naming problem, however I do need each quote to be named as per the cell reference I have stated.

How do I make that happen?
0
h3rm1t9536Author Commented:
I should have that the cell the data is being picked up from has the following formula to create that name:

="Quotation No."&H8&" - "&F4&" - "&H7&" - "&G16

Although in windows explorer the full file name is showing as I want it to, when I drop the file into our MIS system in work, its only picking up the "Quotation No." aspect of the name.

I hope I am making sense to you here. as I said, when running it as a fixed name, it works, but I need the naming of the file to be variable.
0
h3rm1t9536Author Commented:
Hi fp

I have managed to work this out, What I have done is placed the naming location and variable as a string, then referred to that in the naming process and attaching functions and it all works.

Many thanks for your help as you really did point me in the right direction and help me in some way understand what was going on here.

I have attached the code so you can see what I did.

Sub Save_and_email_PDF()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim WordApp As Object
    Dim subject As String
    Dim EmailAddress As String
    Dim s(1 To 21) As String
    Dim objAttach As Object
    Dim objEmbed As Object
    Dim Quotename As String
    Dim Qoutename2 As String
       
    
    subject = Worksheets("Client Quote").Range("AY8")
    EmailAddress = Worksheets("Client Quote").Range("f3") & " " & "<" & Worksheets("Client Quote").Range("ay10") & "> ,"
    Quotename = "C:\Users\joeellis\Desktop\Digital Quotes\2. Client Prices\" & "Quotation No." & Range("j7").Value & " - " & Range("j11").Value & " - " & Range("j14").Value & " - " & Range("j15").Value
    Quotename2 = "C:\Users\joeellis\Desktop\Digital Quotes\3. Internal Prices\" & "Internal No." & Range("j7").Value & " - " & Range("j11").Value & " - " & Range("j14").Value & " - " & Range("j15").Value
    
    Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=Quotename, _
        OpenAfterPublish:=True
           
        
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Quotename2, _
    OpenAfterPublish:=False
     
    
         
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GETDATABASE("", "")
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
        
    Set NDoc = NDatabase.CREATEDOCUMENT
     
   With NDoc
        .SendTo = EmailAddress
        .CopyTo = "digitalprint@zcard.com, "
                .subject = subject
        s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
        s(2) = ""
        s(3) = "Many Thanks for your enquiry"
        s(4) = ""
        s(5) = "Please find attached your quotation for your request" & " " & " : - " & "'" & Worksheets("internal").Range("j14") & "'"
        s(6) = ""
        s(7) = "If you would like to go ahead with this order, please let  me know and I will send you a template, artwork guidelines and  procedures for processing your order."
        s(8) = ""
        s(9) = "Kind Regards"
        s(10) = ""
        s(11) = Worksheets("sheet3").Range("a58")
        s(12) = Worksheets("sheet3").Range("a59")
        s(13) = ""
        s(14) = "Z-CARD® PocketMedia® Solutions"
        s(16) = ""
        s(17) = Worksheets("sheet3").Range("a61")
        s(18) = Worksheets("sheet3").Range("a62")
        s(19) = ""
        s(20) = "7-11 St. Johns Hill, London, SW11 1TN"
        s(21) = "The PocketMedia® Agency | www.zcard.co.uk"
           
              
        .body = Join(s, vbCrLf) & " "
        
    Set objAttach = NDoc.CreateRichTextItem("attachment1")
    Set objEmbed = objAttach.EmbedObject(1454&, "attachment1", Quotename & ".pdf")
                                       


        .Save True, False
    End With
     
    NUIWorkSpace.EDITDOCUMENT True, NDoc
     
    Set objAttach = Nothing
    Set objEmbed = Nothing
    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    
End Sub

Open in new window

0
h3rm1t9536Author Commented:
I eventually worked this one out, but without help I would have not known where to start.
0
[ fanpages ]IT Services ConsultantCommented:
Great news!

Thanks for closing the question in my favour :)

We (jointly) got there in the end.  Sorry I didn't respond as quickly as you were working today.

It looks like the use of range/cell objects in the parameters to the Lotus Notes function "EmbedObject" were causing a problem, & using a string variable resolved the issue.

Thanks for also posting the resultant code you are using & this may help others will similar issues in the future.

Good luck with the rest of your project.

BFN,

fp.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.