Attaching PDF to Lotus Notes email. PDF name is variable.

Hi , as I have mentioned on previous posts, I am very new to all this and need some help if you can possibly spare 5 or 10 minutes.

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

Anyone have any ideas on this?

Maybe?

Sub joee74yyy()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim Attachment1 As String
    Dim AttachME As Object
    Dim EmbedObj1 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
     
        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
        
Attachment1 = "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf"

If Attachment1 <> "" Then
On Error Resume Next
Set AttachME = NDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf", "")
On Error Resume Next
End If

    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    Set NSession = Nothing
    Set AttachME = Nothing
    Set EmbedObj1 = Nothing
         
End Sub

Hi John

thanks for this, I have run this macro and its getting hung up on the create PDF element I had place in for creating the client price PDF.

The following error message is being displayed.

Run-Time error '1004:

Document not saved, the document may be open, or error may have been encountered when saving.

Its creating the email but still not attaching the PDF.

Maybe:

Sub joee74yyy()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim Attachment1 As String
    Dim AttachME As Object
    Dim EmbedObj1 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
     
        Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf", _
        OpenAfterPublish:=True

        activeworkbook.save
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Internal Prices\" & _
        ActiveSheet.Range("BD2").Value & ".pdf", _
        OpenAfterPublish:=False
        
        activeworkbook.save
Attachment1 = "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf"

If Attachment1 <> "" Then
On Error Resume Next
Set AttachME = NDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
        Worksheets("client quote").Range("AY8").Value & ".pdf", "")
On Error Resume Next
End If

    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    Set NSession = Nothing
    Set AttachME = Nothing
    Set EmbedObj1 = Nothing
         
End Sub

Hi John, still getting it hanging up as previously, with same error

Can you send me a sample workbook with the sheetnames as mentioned above?

Hi John

I have found the issue, its to do with the naming of the file. although the file is being named as I require as a variable name. excel is not picking up the variable part of the name when searching for the file to attach, its only seeing the fixed field " Quotation No."

I have changed this code to run a test with a fixed file name being created and looked for and it works fine and does everything I want it to.

However I do need the filename to be a variable, so Im a little lost as to what to do here now.

Code I have working is as follows:

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\Test.pdf", _
    OpenAfterPublish:=True
           
        
    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) & " "
        
    Set objAttach = NDoc.CreateRichTextItem("attachment1")
    Set objEmbed = objAttach.EmbedObject(1454&, "attachment1", "C:\Users\joeellis\Desktop\Test.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

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.

Maybe:

Sub joee74xx()
Dim x As String

x = "C:\Users\joeellis\Desktop\" & "Quotation No." & Range("H8").Value & " - " & Range("F4").Value & " - " & Range("H7").Value & " - " & Range("G16").Value
Set objEmbed = objAttach.EmbedObject(1454&, "attachment1", x & ".pdf")


End Sub

John My friend, you are a genius. all working now. I just did an Irish jig in the office.

thanks for your patience.

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.